Calculating Cells

[jmersing]

I have a wowrkbook that has a few hundred sumproduct formulas in it. When I do certain things like cut a named range and move it somewhere else the file goes into "Calculating Cells" mode and takes about 10 seconds to complete. I'm thinking this is being caused by the sumproduct formlas, but I'm not sure.

Anyone have any advice on how to speed up these calculations other than turning auto calc on and off?

Thanks

 

[dave3009]

Hi There

This may help in a small way to answer you problem

http://www.mrexcel.com/board2/viewtopic.php?t=286068&highlight=

I had an issue with data being anything from 20 to 20000 records in length and needed to speed things up.

Hopefully it'll help you

Dave

 

[jmersing]

I'm not quite sure I understand the example. Here is what one of my formulas look like, can you show how you would propose to change it?

=SUMPRODUCT(--(Site=R$9),--(PlanWeek=WEEK),--(Area=$B$154),--(Account=$C167),Retrievals!$N$131:$N$2136)

Thanks

 

[Aladin Akyurek]

I'm not quite sure I understand the example. Here is what one of my formulas look like, can you show how you would propose to change it?

=SUMPRODUCT(--(Site=R$9),--(PlanWeek=WEEK),--(Area=$B$154),--(Account=$C167),Retrievals!$N$131:$N$2136)

Thanks

What are the ranges Site, PlanWeek, Area, and Account refer to?

 

[jmersing]

THey are all the same rows, the columns are:
Site -H
Plan Week - E
Area - K
Account - l

all are rows 131:2136

 

[Aladin Akyurek]

THey are all the same rows, the columns are:
Site -H
Plan Week - E
Area - K
Account - l

all are rows 131:2136

In O2 enter and copy down:

=E2$","&H2","&I2&","&K2

Then invoke:

=SUMIF((Retrievals!$O$131:$O$2136,WEEK&",",&R$9&","&$C167&","$B$154,Retrievals!$N$131:$N$2136)

 

[jmersing]

Thanks so much Aladin, I guess using the sumproduct formulas were just the wrong approach?

 

[Aladin Akyurek]

Thanks so much Aladin, I guess using the sumproduct formulas were just the wrong approach?

Too many of them with many terms can be a burden in calculation efficiency.

 

[jmersing]

I just finished replacing all of my sumproduct formulas with Sumif formulas (11000 of them)

I'm still having the "calculating Cells" message on the status bar take about 10-15 seconds for each calc?

Any other suggestions to reduce this calc time, other than the obvious i.e. have auto calc off, use less formulas etc.

Thanks

 

[Aladin Akyurek]

I just finished replacing all of my sumproduct formulas with Sumif formulas (11000 of them)

I'm still having the "calculating Cells" message on the status bar take about 10-15 seconds for each calc?

Any other suggestions to reduce this calc time, other than the obvious i.e. have auto calc off, use less formulas etc.

Thanks

Do you also have formulas with volatile functions like INDIRECT, OFFSET, etc.?