Does complicated sumproduct formula crashes excel?

[vivi]

Hi there I have put a template together which is now 1.66mb, the workbook is
huge with one tab "Entry Form" and the other "Project Costs", then there are
a few reports based on the calculations in Project Costs.

I have monthly budget, actual and forecast for 5 years across the project
costs tab, with columns for project stages, workstreams, departments, Expense
type and resources name, etc. All these informations are brought into the
reporting tabs and i use sumproducts to look for the year, the month, the
workstream, expense type and project stage: my formula is as follow:

=SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project
Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project
Costs'!$CJ$7:$EQ$65536)/1000

This formula is on every report tabs (currently 4) as the project manager
wants to see spendings on different project stage and each worksteams under
the project stages.

It takes over 10 mins to calculate, and sometimes it crashes. What can I do
to reduce the time for it to calculate? I've tried the turning off the
calculate automatically option, but still when it's refresehed or saved, the
problem comes back. Any suggestions? Thanks a lot!!

 

[Charles Williams]

Calculation time for SUMPRODUCT is roughly proportional to the number of
cells involved: in your case
each formula uses 65530*4*60= 15.7 million. I am impressed that Excel
manages to calculate in 10 minutes.

I would suggest that you use less cells by restricting the number of ros and
columns being used in the formula to those cells actually containing data
(or which are likely to contain data). Or you could use Dynamic Range Names.

Alternatively you could probably use Pivot Tables to provide much faster and
more flexible reporting.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[vivi]

Thanks Charles

This is very useful as it gives me indication of why my excel workbook keeps
crashing. To reiterate this - it actually takes 10 mins to calculate 20%..
The reason for using the max number of rows was due to uncertainty on number
of entries. I will try again using smaller ranges like up to 10000
I thought of using pivots, but wasn't sure on how to calcuate varaiances and
the formats and presentations of pivot tables are not in accordance with the
company's other reports.

 

[Don Guillett]

As Charles said, use the MINIMUM number of rows required. Set up a defined
name for your ranges using COUNTA or MATCH or MAX to find the last row. Use
within an offset formula

insert>name>define>name colA>in the refers to box
=offset($a$1,0,0,counta($a:$a),1)
Now colA will be SELF adjusting with additions or deletions.
colB
=offset(cola,0,1)

 

[vivi]

Thanks Don

Just to make sure I understand this as I am not too familiar with this
brilliant formulas using offset and counta combining with name ranges:

Does that mean for all the references I use in sumproducts I have to replace
it by the names ranges?

I have to use the offset formula to define the first cell of the range and
use counta to find the last cell of the column? and what about colB? is this
for the new name range?

My data is broke down into 3 sections : Budget 1st section which is Col V:CH
, Actual: Col CJ : EV and Forecast is Col EX:HJ

Can I still use your suggested forumla, maybe naming the column CJ:EV as
Actual ? Bearing in mind the rows depending on how rows the users are filling
out and column A will always be filled if data entry is required.

=SUMPRODUCT(--($A$38=ColA)*--(B$38=ColB,Actual)/1000

Thanks for all your help, it's very appreciated, finally see a light at the
end of tunnel.

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[T. Valko]

I think I would avoid using OFFSET to define the ranges. I'd use some form
of INDEX.

 

[Don Guillett]

I looked at your workbook and did and edit>replace>workbook>65536(AND 65535)
to 100 and replace>10000 to 100. Since this is the last row of your entry
sheet it seems reasonable. Since you have so many ranges, I am not now
recommending defined names. Should you insert rows in the entry sheet the
formulas will automatically change.
This project is poorly designed and the macros are very clumsy.