Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() usage
Now that I understand the Subject formula I use it extensively and in
particular projects Bringing back multi-criteria data between sheets. The issue I seem to be having is that after I create 3 or 4 sheets where there are maybe 2,000 or 4,000 rows using the Sumproduct() many "things" begin to happen. Recalc time being the first, resorting to Manual Re-calc mode; But now I seem to be having problems doing the normal File Save function. Has anyone experienced this situation? Are there no-no's involved in what I've done? More important is there a remedy, apart from a total abandonment of the functions usage? Thanks in Advance, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() usage
The bigger the range and the more formulas you have, the slower recalc will be.
Lots of times, a bunch of =sumproduct()'s could be replaced with a single pivottable. And look at: tools|Options|Calculation tab (xl2003 menu system) If you're using manual calculation, you may want to turn off the: "Recalculate before save" option. Jim May wrote: Now that I understand the Subject formula I use it extensively and in particular projects Bringing back multi-criteria data between sheets. The issue I seem to be having is that after I create 3 or 4 sheets where there are maybe 2,000 or 4,000 rows using the Sumproduct() many "things" begin to happen. Recalc time being the first, resorting to Manual Re-calc mode; But now I seem to be having problems doing the normal File Save function. Has anyone experienced this situation? Are there no-no's involved in what I've done? More important is there a remedy, apart from a total abandonment of the functions usage? Thanks in Advance, -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() usage
Sounds like you have more dependancies than XL can effectively handle. Check
out this link... http://www.decisionmodels.com/calcsecretsf.htm There is a bunch more useful stuff on this site. I highly recommend a quick read of it... -- HTH... Jim Thomlinson "Jim May" wrote: Now that I understand the Subject formula I use it extensively and in particular projects Bringing back multi-criteria data between sheets. The issue I seem to be having is that after I create 3 or 4 sheets where there are maybe 2,000 or 4,000 rows using the Sumproduct() many "things" begin to happen. Recalc time being the first, resorting to Manual Re-calc mode; But now I seem to be having problems doing the normal File Save function. Has anyone experienced this situation? Are there no-no's involved in what I've done? More important is there a remedy, apart from a total abandonment of the functions usage? Thanks in Advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Errors in Sumproduct Usage | Excel Worksheet Functions | |||
100% cpu usage | Excel Discussion (Misc queries) | |||
How to project usage. | Excel Worksheet Functions | |||
Cursor Usage | Excel Discussion (Misc queries) | |||
HOW MANY 'IF CONDITIONS' CAN BE USED IN ONE SUCH USAGE? | Excel Worksheet Functions |