Sumproduct

[Jive]

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?

 

[Bob Phillips]

Create it as a named formula

Insert>Name>Define.., 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]

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

 

[Charles Williams]

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