Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
I post this before and implemented the suggestions, to no avail, so I am
asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
the calcs will take as long as they take... if the formulas are to external
workbooks, ensure they are open when you go to save. Perhaps it's a matter of either separating these 15 tabs into several workbooks or finding ways to use less formulas.... "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
Thanks I will give that a try.
-- CK "Elkar" wrote: In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
And an embedded if can be a bit disconcerting as well.. Dependong on how many
layers we're looking at....... "ColleenK" wrote: Thanks I will give that a try. -- CK "Elkar" wrote: In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
the SUMIF and COUNTIF functions are not very
efficient for calc times. I always use SUMPRODUCT instead. Are you sure you don't have that backwards? -- Biff Microsoft Excel MVP "Elkar" wrote in message ... In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
I'm quite certain. Set up some test data and try it out for yourself. A
quick test using 2 columns of 65000 rows each, the SUMIF function noticeably hangs for a few seconds while calculating. The SUMPRODUCT function is virtually instant. "T. Valko" wrote: the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Are you sure you don't have that backwards? -- Biff Microsoft Excel MVP "Elkar" wrote in message ... In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
Hmmm...
That's interesting. I can assure you that a general SUMIF/COUNTIF formula is significantly more efficeint than the equivalent SUMPRODUCT formula. Here's what I did... Filled A1:A65000 with random letters A or B. Filled B1:B65000 with random numbers 0 to 100. Using Charles Williams RangeTimer method to measure the calculation time: http://msdn2.microsoft.com/en-us/library/aa730921.aspx Average of 5 calculations for each formula: COUNTIF and the equivalent SUMPRODUCT =COUNTIF(A:A,"A") = 0.007 secs =SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs =SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs =COUNTIF(B:B,"=50")-COUNTIF(B:B,"75") = 0.011 secs =SUMPRODUCT((B1:B65000=50)*(B1:B65000<=75)) = 0.120 secs =SUMPRODUCT(--(B1:B65000=50),--(B1:B65000<=75)) = 0.123 secs SUMIF and the equivalent SUMPRODUCT =SUMIF(A:A,"A",B:B) = 0.009 secs =SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs =SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs -- Biff Microsoft Excel MVP "Elkar" wrote in message ... I'm quite certain. Set up some test data and try it out for yourself. A quick test using 2 columns of 65000 rows each, the SUMIF function noticeably hangs for a few seconds while calculating. The SUMPRODUCT function is virtually instant. "T. Valko" wrote: the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Are you sure you don't have that backwards? -- Biff Microsoft Excel MVP "Elkar" wrote in message ... In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
Very interesting indeed. Using your examples, the calc time is better with
SUMIF. However, I tried filling column A with =ROW(), column B with =RIGHT(A1,1) and Column C with =RAND(). In D1, I placed the formula: =SUMIF(B1:B65535,"2",C1:C65535) It takes over 10 seconds to calculate. I then placed an apostrophe in front of D1 to "disable" it. Then, in D2 I placed the formula: =SUMPRODUCT((B1:B65535="2")*C1:C65535) It takes less than 1 second. Repeated the process with the same results. So, I'm going to play around with this more, but apparently other factors have influence on the performace of SUMIF and SUMPRODUCT. "T. Valko" wrote: Hmmm... That's interesting. I can assure you that a general SUMIF/COUNTIF formula is significantly more efficeint than the equivalent SUMPRODUCT formula. Here's what I did... Filled A1:A65000 with random letters A or B. Filled B1:B65000 with random numbers 0 to 100. Using Charles Williams RangeTimer method to measure the calculation time: http://msdn2.microsoft.com/en-us/library/aa730921.aspx Average of 5 calculations for each formula: COUNTIF and the equivalent SUMPRODUCT =COUNTIF(A:A,"A") = 0.007 secs =SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs =SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs =COUNTIF(B:B,"=50")-COUNTIF(B:B,"75") = 0.011 secs =SUMPRODUCT((B1:B65000=50)*(B1:B65000<=75)) = 0.120 secs =SUMPRODUCT(--(B1:B65000=50),--(B1:B65000<=75)) = 0.123 secs SUMIF and the equivalent SUMPRODUCT =SUMIF(A:A,"A",B:B) = 0.009 secs =SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs =SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs -- Biff Microsoft Excel MVP "Elkar" wrote in message ... I'm quite certain. Set up some test data and try it out for yourself. A quick test using 2 columns of 65000 rows each, the SUMIF function noticeably hangs for a few seconds while calculating. The SUMPRODUCT function is virtually instant. "T. Valko" wrote: the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Are you sure you don't have that backwards? -- Biff Microsoft Excel MVP "Elkar" wrote in message ... In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
Now that is indeed interesting!
I don't know why that does that and I'll have to consult a real expert on that one but I suspect (know) the RAND function is involved somehow. If you convert column C to constants the SUMIF version is faster to calculate than the SUMPRODUCT version. Although the sample you describe does what it does, it's *not* because SUMIF is less efficient than SUMPRODUCT. If you time just the SUMIF formula and compare its calc time to just the SUMPRODUCT version you'll see that the SUMIF version is faster to calculate, about 0.054 secs vs 0.058 secs. All that other time is taken by the RAND() functions recalculating. Let me see what I can find out. -- Biff Microsoft Excel MVP "Elkar" wrote in message ... Very interesting indeed. Using your examples, the calc time is better with SUMIF. However, I tried filling column A with =ROW(), column B with =RIGHT(A1,1) and Column C with =RAND(). In D1, I placed the formula: =SUMIF(B1:B65535,"2",C1:C65535) It takes over 10 seconds to calculate. I then placed an apostrophe in front of D1 to "disable" it. Then, in D2 I placed the formula: =SUMPRODUCT((B1:B65535="2")*C1:C65535) It takes less than 1 second. Repeated the process with the same results. So, I'm going to play around with this more, but apparently other factors have influence on the performace of SUMIF and SUMPRODUCT. "T. Valko" wrote: Hmmm... That's interesting. I can assure you that a general SUMIF/COUNTIF formula is significantly more efficeint than the equivalent SUMPRODUCT formula. Here's what I did... Filled A1:A65000 with random letters A or B. Filled B1:B65000 with random numbers 0 to 100. Using Charles Williams RangeTimer method to measure the calculation time: http://msdn2.microsoft.com/en-us/library/aa730921.aspx Average of 5 calculations for each formula: COUNTIF and the equivalent SUMPRODUCT =COUNTIF(A:A,"A") = 0.007 secs =SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs =SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs =COUNTIF(B:B,"=50")-COUNTIF(B:B,"75") = 0.011 secs =SUMPRODUCT((B1:B65000=50)*(B1:B65000<=75)) = 0.120 secs =SUMPRODUCT(--(B1:B65000=50),--(B1:B65000<=75)) = 0.123 secs SUMIF and the equivalent SUMPRODUCT =SUMIF(A:A,"A",B:B) = 0.009 secs =SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs =SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs -- Biff Microsoft Excel MVP "Elkar" wrote in message ... I'm quite certain. Set up some test data and try it out for yourself. A quick test using 2 columns of 65000 rows each, the SUMIF function noticeably hangs for a few seconds while calculating. The SUMPRODUCT function is virtually instant. "T. Valko" wrote: the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Are you sure you don't have that backwards? -- Biff Microsoft Excel MVP "Elkar" wrote in message ... In my experience, the SUMIF and COUNTIF functions are not very efficient for calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more flexible. Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut down your calc time substantially. HTH Elkar "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
"T. Valko" wrote...
Now that is indeed interesting! I don't know why that does that and I'll have to consult a real expert on that one but I suspect (know) the RAND function is involved somehow. If you convert column C to constants the SUMIF version is faster to calculate than the SUMPRODUCT version. .... Speculation, but it sure seems that SUMIF triggers recalc after accessing each cell in its 1st argument. SUMIF and COUNTIF give the same abysmal recalc performance against a large range filled with formulas calling TODAY. If so, the rule is simple: use SUMIF and COUNTIF on ranges containing constants or formulas calling no volatile functions; use SUMPRODUCT on ranges containing formulas calling volatile functions. When in doubt, use SUMPRODUCT because it'll be slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be slow by TENS OF SECONDS when they're suboptimal. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
"Sean Timmons" wrote in message
... the calcs will take as long as they take... if the formulas are to external workbooks, ensure they are open when you go to save. Perhaps it's a matter of either separating these 15 tabs into several workbooks or finding ways to use less formulas.... "ColleenK" wrote: I post this before and implemented the suggestions, to no avail, so I am asking for further ideas. This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of the following formulas: sumif, sumproduct, vlookup and embedded if statements. This takes about 10 minutes to calculate and when I hit "save as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with 1 GB of Ram, does anyone have any suggestions as to how I can speed up the calculations? -- CK CK wrote <<Perhaps it's a matter of either separating these 15 tabs into several workbooks A downside of this is that any structural changes in a workbook will not be referred to correctly in another workbook and any recalculation will be incorrect. Move just one cell and everything could go to pot! Bill Ridgeway. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
Here's a reply from Charles Williams:
-------------------- Its a calculation chain sequence problem: in this case rather extreme. Assuming that you are using manual calculation mode you will find that the second time you use F9 the SUMIF calculation is much faster. This is because the second F9 recalculation reuses the optimised calculation chain sequence from the first calculation. You can also check this (in manual calc mode) by a) Enter all the formulae (SUMIF but not SUMPRODUCT) b) reenter (by copy down) column a, then col b then col C. F9 is now fast c) re-enter the SUMIF formula, F9 is now slow the first time and fast the second time This is because re-entering a formula places it at the top of the calculation chain. When Excel attempts to calculate a formula it gets rescheduled if it depends on uncalculated cells. So in the slow SUMIF case it is being rescheduled thousands of times, and in the fast SUMIF case its already in the optimum place in the calc chain so only gets calculated once. With SUMPRODUCT the rescheduling does not seem to happen thousands of times: I don't know why (maybe something to do with it being handled like an array formula?) regards Charles ------------------- -- Biff Microsoft Excel MVP "Harlan Grove" wrote in message ... "T. Valko" wrote... Now that is indeed interesting! I don't know why that does that and I'll have to consult a real expert on that one but I suspect (know) the RAND function is involved somehow. If you convert column C to constants the SUMIF version is faster to calculate than the SUMPRODUCT version. ... Speculation, but it sure seems that SUMIF triggers recalc after accessing each cell in its 1st argument. SUMIF and COUNTIF give the same abysmal recalc performance against a large range filled with formulas calling TODAY. If so, the rule is simple: use SUMIF and COUNTIF on ranges containing constants or formulas calling no volatile functions; use SUMPRODUCT on ranges containing formulas calling volatile functions. When in doubt, use SUMPRODUCT because it'll be slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be slow by TENS OF SECONDS when they're suboptimal. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
I think I have narrowed it down to Array formulae (& SUMPRODUCT) containing
calculated virtual columns versus ordinary formulae. If you take the example test problem (I only used 10000 rows) In Manual calc Mode col A=ROW() col B=Right(col A,1) col C=Rand() enter these formulae Then if you enter =SUM(A1:A10000,C1:C10000) in D3 and press F9 it takes 4.9 seconds on my system Pressing F9 a second time takes less than a millisecond If you enter {=SUM(A1:a10000*C1:C10000)} as an array formula in D3 and press F9 it takes 0.007 seconds Pressing F9 a second time takes the same time If you array enter {=SUM(A1:A10000,C1:C10000)} it also takes 4.9 seconds Conclusion: forcing Excel to calculate a virtual column which references uncalculated cells bypasses the multiple recalculation of formulae which would otherwise happen. So here is a case where SUMPRODUCT and Array formulae are substantially faster than ordinary formulae! regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "T. Valko" wrote in message ... Here's a reply from Charles Williams: -------------------- Its a calculation chain sequence problem: in this case rather extreme. Assuming that you are using manual calculation mode you will find that the second time you use F9 the SUMIF calculation is much faster. This is because the second F9 recalculation reuses the optimised calculation chain sequence from the first calculation. You can also check this (in manual calc mode) by a) Enter all the formulae (SUMIF but not SUMPRODUCT) b) reenter (by copy down) column a, then col b then col C. F9 is now fast c) re-enter the SUMIF formula, F9 is now slow the first time and fast the second time This is because re-entering a formula places it at the top of the calculation chain. When Excel attempts to calculate a formula it gets rescheduled if it depends on uncalculated cells. So in the slow SUMIF case it is being rescheduled thousands of times, and in the fast SUMIF case its already in the optimum place in the calc chain so only gets calculated once. With SUMPRODUCT the rescheduling does not seem to happen thousands of times: I don't know why (maybe something to do with it being handled like an array formula?) regards Charles ------------------- -- Biff Microsoft Excel MVP "Harlan Grove" wrote in message ... "T. Valko" wrote... Now that is indeed interesting! I don't know why that does that and I'll have to consult a real expert on that one but I suspect (know) the RAND function is involved somehow. If you convert column C to constants the SUMIF version is faster to calculate than the SUMPRODUCT version. ... Speculation, but it sure seems that SUMIF triggers recalc after accessing each cell in its 1st argument. SUMIF and COUNTIF give the same abysmal recalc performance against a large range filled with formulas calling TODAY. If so, the rule is simple: use SUMIF and COUNTIF on ranges containing constants or formulas calling no volatile functions; use SUMPRODUCT on ranges containing formulas calling volatile functions. When in doubt, use SUMPRODUCT because it'll be slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be slow by TENS OF SECONDS when they're suboptimal. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed
So here is a case where SUMPRODUCT and Array
formulae are substantially faster than ordinary formulae! Good stuff! I just hope I'll be able to remember this. Thanks Charles! -- Biff Microsoft Excel MVP "Charles Williams" wrote in message ... I think I have narrowed it down to Array formulae (& SUMPRODUCT) containing calculated virtual columns versus ordinary formulae. If you take the example test problem (I only used 10000 rows) In Manual calc Mode col A=ROW() col B=Right(col A,1) col C=Rand() enter these formulae Then if you enter =SUM(A1:A10000,C1:C10000) in D3 and press F9 it takes 4.9 seconds on my system Pressing F9 a second time takes less than a millisecond If you enter {=SUM(A1:a10000*C1:C10000)} as an array formula in D3 and press F9 it takes 0.007 seconds Pressing F9 a second time takes the same time If you array enter {=SUM(A1:A10000,C1:C10000)} it also takes 4.9 seconds Conclusion: forcing Excel to calculate a virtual column which references uncalculated cells bypasses the multiple recalculation of formulae which would otherwise happen. So here is a case where SUMPRODUCT and Array formulae are substantially faster than ordinary formulae! regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "T. Valko" wrote in message ... Here's a reply from Charles Williams: -------------------- Its a calculation chain sequence problem: in this case rather extreme. Assuming that you are using manual calculation mode you will find that the second time you use F9 the SUMIF calculation is much faster. This is because the second F9 recalculation reuses the optimised calculation chain sequence from the first calculation. You can also check this (in manual calc mode) by a) Enter all the formulae (SUMIF but not SUMPRODUCT) b) reenter (by copy down) column a, then col b then col C. F9 is now fast c) re-enter the SUMIF formula, F9 is now slow the first time and fast the second time This is because re-entering a formula places it at the top of the calculation chain. When Excel attempts to calculate a formula it gets rescheduled if it depends on uncalculated cells. So in the slow SUMIF case it is being rescheduled thousands of times, and in the fast SUMIF case its already in the optimum place in the calc chain so only gets calculated once. With SUMPRODUCT the rescheduling does not seem to happen thousands of times: I don't know why (maybe something to do with it being handled like an array formula?) regards Charles ------------------- -- Biff Microsoft Excel MVP "Harlan Grove" wrote in message ... "T. Valko" wrote... Now that is indeed interesting! I don't know why that does that and I'll have to consult a real expert on that one but I suspect (know) the RAND function is involved somehow. If you convert column C to constants the SUMIF version is faster to calculate than the SUMPRODUCT version. ... Speculation, but it sure seems that SUMIF triggers recalc after accessing each cell in its 1st argument. SUMIF and COUNTIF give the same abysmal recalc performance against a large range filled with formulas calling TODAY. If so, the rule is simple: use SUMIF and COUNTIF on ranges containing constants or formulas calling no volatile functions; use SUMPRODUCT on ranges containing formulas calling volatile functions. When in doubt, use SUMPRODUCT because it'll be slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be slow by TENS OF SECONDS when they're suboptimal. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on Calculation Speed | Excel Discussion (Misc queries) | |||
Calculation Efficiency (Speed)? | Excel Discussion (Misc queries) | |||
Calculation Speed | Excel Worksheet Functions | |||
Calculation speed issue | Excel Discussion (Misc queries) | |||
Calculation Speed | Excel Discussion (Misc queries) |