Sumproduct
 

I am using a sum product equation on a spreadsheet which has 500-1000 entrys
at any given time with aaproximately 200 values per entry.

I used a method to filter results that i have used before but because of the
number of calculations required the sheet itself has become very processor
intensive.

my current equation is of the following format;
=SUMPRODUCT((('DBase Clone'!$L$2:$L$500<=WORKDAY(NOW(),6))-('DBase
Clone'!$L$2:$L$500<=NOW()))*Common Array*('DBase Clone'!$BA$2:$BA$500))

With an array in the middle which is common to every entry, as follows
((('DBase Clone'!$F$2:$F$500<"Order Despatched - Back Orders
Pending")*('DBase Clone'!$F$2:$F$500<"Order Despatched -
Complete"))*((($H$2=TRUE)+((('DBase Clone'!$X$2:$X$500=TRUE)*$I$2)+(('DBase
Clone'!$X$2:$X$500=FALSE)*$J$2)))*(($H$4=TRUE)+((( 'DBase
Clone'!$F$2:$F$500="Produce BOM")*$I$4)+(('DBase Clone'!$F$2:$F$500="BOM
Produced - Check & Pass To SOP")*$J$4)+(('DBase Clone'!$F$2:$F$500="Passed To
SOP")*$K$4)+(('DBase Clone'!$F$2:$F$500="Despatch Has Received
Paperwork")*$L$4)))))

Is there a way in which i can store the comon array seperatly and not repeat
it in the best part of 1500 cells? also will this mean that proportion is
only calculated once?

Sumproduct
 

Create it as a named formula

InsertNameDefine.., Name of CommonArray, RefersTo of the formula, then use
as shown in your SPformula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jive" wrote in message
...
I am using a sum product equation on a spreadsheet which has 500-1000
entrys
at any given time with aaproximately 200 values per entry.

I used a method to filter results that i have used before but because of
the
number of calculations required the sheet itself has become very processor
intensive.

my current equation is of the following format;
=SUMPRODUCT((('DBase Clone'!$L$2:$L$500<=WORKDAY(NOW(),6))-('DBase
Clone'!$L$2:$L$500<=NOW()))*Common Array*('DBase Clone'!$BA$2:$BA$500))

With an array in the middle which is common to every entry, as follows
((('DBase Clone'!$F$2:$F$500<"Order Despatched - Back Orders
Pending")*('DBase Clone'!$F$2:$F$500<"Order Despatched -
Complete"))*((($H$2=TRUE)+((('DBase
Clone'!$X$2:$X$500=TRUE)*$I$2)+(('DBase
Clone'!$X$2:$X$500=FALSE)*$J$2)))*(($H$4=TRUE)+((( 'DBase
Clone'!$F$2:$F$500="Produce BOM")*$I$4)+(('DBase Clone'!$F$2:$F$500="BOM
Produced - Check & Pass To SOP")*$J$4)+(('DBase Clone'!$F$2:$F$500="Passed
To
SOP")*$K$4)+(('DBase Clone'!$F$2:$F$500="Despatch Has Received
Paperwork")*$L$4)))))

Is there a way in which i can store the comon array seperatly and not
repeat
it in the best part of 1500 cells? also will this mean that proportion is
only calculated once?

Sumproduct
 

Thanks Bob

I had already tried that but when i dragged and dropped it i forgot to
change the BA to BB, BC, BD etc, now ive got my head screwed on right it all
works fine :)

"Bob Phillips" wrote:

Create it as a named formula

InsertNameDefine.., Name of CommonArray, RefersTo of the formula, then use
as shown in your SPformula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jive" wrote in message
...
I am using a sum product equation on a spreadsheet which has 500-1000
entrys
at any given time with aaproximately 200 values per entry.

I used a method to filter results that i have used before but because of
the
number of calculations required the sheet itself has become very processor
intensive.

my current equation is of the following format;
=SUMPRODUCT((('DBase Clone'!$L$2:$L$500<=WORKDAY(NOW(),6))-('DBase
Clone'!$L$2:$L$500<=NOW()))*Common Array*('DBase Clone'!$BA$2:$BA$500))

With an array in the middle which is common to every entry, as follows
((('DBase Clone'!$F$2:$F$500<"Order Despatched - Back Orders
Pending")*('DBase Clone'!$F$2:$F$500<"Order Despatched -
Complete"))*((($H$2=TRUE)+((('DBase
Clone'!$X$2:$X$500=TRUE)*$I$2)+(('DBase
Clone'!$X$2:$X$500=FALSE)*$J$2)))*(($H$4=TRUE)+((( 'DBase
Clone'!$F$2:$F$500="Produce BOM")*$I$4)+(('DBase Clone'!$F$2:$F$500="BOM
Produced - Check & Pass To SOP")*$J$4)+(('DBase Clone'!$F$2:$F$500="Passed
To
SOP")*$K$4)+(('DBase Clone'!$F$2:$F$500="Despatch Has Received
Paperwork")*$L$4)))))

Is there a way in which i can store the comon array seperatly and not
repeat
it in the best part of 1500 cells? also will this mean that proportion is
only calculated once?

Sumproduct
 

The workbook would recalculate faster if you broke out the common formula to
a separate helper column.

regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Jive" wrote in message
...
Thanks Bob

I had already tried that but when i dragged and dropped it i forgot to
change the BA to BB, BC, BD etc, now ive got my head screwed on right it
all
works fine :)

"Bob Phillips" wrote:

Create it as a named formula

InsertNameDefine.., Name of CommonArray, RefersTo of the formula, then
use
as shown in your SPformula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jive" wrote in message
...
I am using a sum product equation on a spreadsheet which has 500-1000
entrys
at any given time with aaproximately 200 values per entry.

I used a method to filter results that i have used before but because
of
the
number of calculations required the sheet itself has become very
processor
intensive.

my current equation is of the following format;
=SUMPRODUCT((('DBase Clone'!$L$2:$L$500<=WORKDAY(NOW(),6))-('DBase
Clone'!$L$2:$L$500<=NOW()))*Common Array*('DBase Clone'!$BA$2:$BA$500))

With an array in the middle which is common to every entry, as follows
((('DBase Clone'!$F$2:$F$500<"Order Despatched - Back Orders
Pending")*('DBase Clone'!$F$2:$F$500<"Order Despatched -
Complete"))*((($H$2=TRUE)+((('DBase
Clone'!$X$2:$X$500=TRUE)*$I$2)+(('DBase
Clone'!$X$2:$X$500=FALSE)*$J$2)))*(($H$4=TRUE)+((( 'DBase
Clone'!$F$2:$F$500="Produce BOM")*$I$4)+(('DBase
Clone'!$F$2:$F$500="BOM
Produced - Check & Pass To SOP")*$J$4)+(('DBase
Clone'!$F$2:$F$500="Passed
To
SOP")*$K$4)+(('DBase Clone'!$F$2:$F$500="Despatch Has Received
Paperwork")*$L$4)))))

Is there a way in which i can store the comon array seperatly and not
repeat
it in the best part of 1500 cells? also will this mean that proportion
is
only calculated once?