Size of workbook and calculation time huge because of Sumproducts

[Hari]

Hi ,

In cell B5 of sheet2 I have the following formula

SUMPRODUCT(('sheet1'!$B$2:$B$7000=$A5)*('sheet1'!$G$2:$G$7000=$B$3)*('sheet1
'!$H$2:$H$7000=$B$2)*('sheet1'!$I$2:$I$7000=$B$1)*('sheet1'!$C$2:$C$7000=C$4
))

This formula I am presently having in cell C5 or worksheet "sheet2". The
same formula is copied in cells C5:U500, AC5:AU500, BC5:BU500, CC5:CU500,
DC5U500, EC5:EU500 and FC5:FU500 of sheet2 with only one difference. That
is the second criteria $B$3 above is changed to $AB$3 for columns AC5:AU500,
changed to $BB$3 for BC5:BU500 , changed to $CB$3 for CC5:CU500, changed to
$DB$3 for DC5U500, changed to $EB$3 for EC5:EU500 and changed to $FB$3 for
FC5:FU500.

Just to explain the formula ranges in complete :-

Here, in the Sumproduct formula the first criteria checked for $A5 is for
the cells which has formulas in the row 5. Row 6 will have criteria as $A6
and so on.

Second criteria $B$3 is constant for columns from C to U only. For Columns
AC to AU the second criteria will be $AB$3 and for Columns BC to BU the
second criteria will be $BB$3 and so on for columns CC5:CU500, DC5U500,
EC5:EU500 and FC5:FU500.

Third criteria $B$2 is common for whole of sheet2

Fourth criteria $B$1 is common for whole of sheet2.

Fifth criteria C$4 is for cells in the column C. Column D cells will have
criteria as D$4 and so on.



Now I will be having atleast 30 more worksheets "sheet3" to "sheet 31" which
will be of the same format/layout as sheet2 and will be having the same
formulas in the cells C5:U500 , AC5:AU500, BC5:BU500, CC5:CU500, DC5U500,
EC5:EU500 and FC5:FU500 of respective sheets.

Only difference among sheets is that the value of $B$2 and $B$1 will change
depending on the sheet.Also the values in $B$3, $AB$3, $BB$3, $CB$3, $DB$3,
$EB$3 and $FB$3 will change depending on the sheet


The problem as stated in the subject and above is that I will be having
around 66500 cells having this sumproduct formula in one worksheet and I
will be having this same formula/format in 30 other sheets.

So that makes 66500*30 cells having huge sumproduct formulas in each one of
them.

Presently with just sheet1 ( which has all the raw data) and sheet 2 it
takes atleast 10 minutes for the calculations on a P3 and the size of the
workbook is around 2.7 MB

Once I add the rest 29 worksheets or so I expect my workbook to boot to ....
size and it will take Eons for calculation ( presently I have set to manual
so that it doesnt cripple me with "Recalculating..." for every minor change
in data).

My question is can a better formula be put to reduce the size of the
workbook or calculation time.

I have come across ARRAY formulas and read in some post that they are more
efficient but I dont know how to work with them. Please guide me if possible
for the present situation.

Regards,
Hari
India

 

[Hari]

Hi ,

Please ignore the first line in the below post "In cell B5 of sheet2 I have
the following formula".

As stated just after the sumproduct formula I have this in "This formula I
am presently having in cell C5 or worksheet "sheet2". "

Sorry for the confusion.

Regards,
Hari
India

 

[Biff]

Hi Hari!

A couple of ideas.

Rewrite or edit you formulae like this:

SUMPRODUCT(--('sheet1'!$B$2:$B$7000=$A5),--('sheet1'!
$G$2:$G$7000=$B$3),--('sheet1'!$H$2:$H$7000=$B$2),--
('sheet1'!$I$2:$I$7000=$B$1),--('sheet1'!$C$2:$C$7000=C$4
))

Using the double unary "--" in a SUMPRODUCT function
eliminates one calculation cycle thus making it faster.
Since you have many formulae, this may make a significant
difference.

As far as the size of your workbook, consider breaking it
up into many workbooks.

Biff

 

[Mark E. Philpot]

Too many formulas will make the workbook collpase on
itself thus creating a black hole! This means you will not
be able to open it.

If the formulas do not change depending on new values then
copy & paste xlvalues.

I am intereseted in what you are doing and will study the
given information.

A lot of people seem to want to do heavy traffic in their
workbooks when simple footpathing will do the trick.

Basically my main "skill" is Easy Task Manager forms:
making tedious, laborious tasks done with a few clicks of
the mouse.

If you think I can be of some help, it's free, send me
details.

visit
http://au.geocities.com/excelmarksway
for some of my samples.

Regards
Mark E. Philpot

(e-mail address removed)
PS
send a sample sheet of the data.

 

[Charles Williams]

Hi Hari,

Take the part of the formulae that is common and create a helper column that
calculates only this part for each cell in the range.

Then reference the helper column in the SUMPRODUCT formulae.

Doing this will significantly reduce the number of calculations you are
asking Excel to do.


regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Hari]

Hi Biff,

Thanx for ur reply. I changed the formula but Im not able to notice any
difference.Still takes a very long time.

Please tell me what this "unary operator" means. I have not come across this
usage before.How do I interpret calculations involving this operator.

Regards,
Hari
India

 

[Biff]

Hi Hari,

Did you change all your formulae? From your description it
sounded like you had thousands of them. I would think that
changing all of them would make some amount of difference.

The double unary operator "--" has been adopted for it's
efficiency in that it results in faster calculation. My
observations are that it's use has increased greatly over
the last year or so.

What it actually does is "converts" boolean values TRUE,
FALSE, to 1,0 and eliminates that one cycle in the
calculation process thereby making it faster. FASTER being
a relative description.

Consider this simple formula:

=SUMPRODUCT((A3:A9=1)*(B3:B9="y"))

This formula takes 6 cycles or steps to resolve.

Now consider this formula:

=SUMPRODUCT(--(A3:A9=1),--(B3:B9="y"))

This formula takes 5 cycles or steps to resolve.

Biff

 

[Charles Williams]

Hi Biff,

When I tested the speed difference for the kind and size of SUMPRODUCT that
Hari is using I found about a 1%-3% speed improvement, so probably Hari will
not notice the difference.

Have your timeing tests produced a different result?

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Hari]

Hi Charles,

Im sorry, Im new to the technique u have mentioned here.

I do use helper column for simple things ( like if many cells have SUM
formula and have a common argument which could be calculated in a separate
cell and that common cell is referenced in all the sum formulas). But within
sumproduct formula how does one make the use the helper column. My doubt is
there because in sumproduct only when each of the condition is
Simultaneously true the final value comes. For ex.I have mentioned 4 cells
below , C5, D5, C6 and D6 which has the sumproduct formulas. If possible
please give me an indication/demonstration of what u could take common here
or how u would structure it.

( Please note as Biff recommended I have changed all my formulas to "unary
operator" method. Thanx a lot to Biff for teaching me something new)

C5 =
SUMPRODUCT(--('Sheet1.'!$B$2:$B$7000=$A5),--('Sheet1.'!$G$2:$G$7000=$B$3),--
('Sheet1.'!$H$2:$H$7000=$B$2),--('Sheet1.'!$I$2:$I$7000=$B$1),--('Sheet1.'!$
C$2:$C$7000=C$4))





D5 =
=SUMPRODUCT(--('Sheet1.'!$B$2:$B$7000=$A5),--('Sheet1.'!$G$2:$G$7000=$B$3),-
-('Sheet1.'!$H$2:$H$7000=$B$2),--('Sheet1.'!$I$2:$I$7000=$B$1),--('Sheet1.'!
$C$2:$C$7000=D$4))



C6 =
=SUMPRODUCT(--('Sheet1.'!$B$2:$B$7000=$A6),--('Sheet1.'!$G$2:$G$7000=$B$3),-
-('Sheet1.'!$H$2:$H$7000=$B$2),--('Sheet1.'!$I$2:$I$7000=$B$1),--('Sheet1.'!
$C$2:$C$7000=C$4))



D6 =
=SUMPRODUCT(--('Sheet1.'!$B$2:$B$7000=$A6),--('Sheet1.'!$G$2:$G$7000=$B$3),-
-('Sheet1.'!$H$2:$H$7000=$B$2),--('Sheet1.'!$I$2:$I$7000=$B$1),--('Sheet1.'!
$C$2:$C$7000=D$4))


Regards,
Hari
India

 

[Charles Williams]

Hi Hari,

For example, assuming that column Z is available for the helper column then:

Z2= if(AND('Sheet1.'!$G2=$B$3,'Sheet1.'!$H2=$B$2,'Sheet1.'!$I2=$B$1),1,0)

and fill down to Z7000

then your SUMPRODUCTS become something like

C5 =
SUMPRODUCT(--('Sheet1.'!$B$2:$B$7000=$A5),--('Sheet1.'!$
C$2:$C$7000=C$4),'Sheet1.'!$Z$2:$Z$7000)

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Biff]

Hi charles,

I have not conducted any real time tests. That degree of
improvement is just about negligable. This technique was
discussed in-depth here several months ago. If I remember
correctly, I think it was Harlan Grove that concluded that
true performance gains would only be realized in a "huge"
file.

I like the solution you offered. I still think Hari should
break up his one wb into several. I think he said that
with just the one ws, it was already ~2.5mb.

Biff

 

[Charles Williams]

Hi Biff,

Having finally got around to doing some tests this week the only case where
I get significant (~25%) speed improvement with SUMPRODUCT is when
multiplying and adding two or more ranges using "native" sumproduct syntax:
SUMPRODUCT($A$1:$a$10000,$B$1:$B$10000)

This is what David Braden discovered some months back.

as soon as I use conditional expressions the speed drops to close to the
{SUM( ... )} type of array formula, although SUMPRODUCT is generally
slightly faster, and when using SUMPRODUCT the double minus syntax is very
slightly faster.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Biff]

Thanks Charles!

-----Original Message-----
Hi Biff,

Having finally got around to doing some tests this week the only case where
I get significant (~25%) speed improvement with SUMPRODUCT is when
multiplying and adding two or more ranges using "native" sumproduct syntax:
SUMPRODUCT($A$1:$a$10000,$B$1:$B$10000)

This is what David Braden discovered some months back.

as soon as I use conditional expressions the speed drops to close to the
{SUM( ... )} type of array formula, although SUMPRODUCT is generally
slightly faster, and when using SUMPRODUCT the double minus syntax is very
slightly faster.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com





.