Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is more efficient
Do it make any difference having the following equation:
=($I4=$G$4)*SUMPRODUCT(--($I4-$G$4:$G4=0),(INDIRECT("R"&1044-$A4)):$R$1043,$C$3:$C3)+($I4$G$4)*SUMPRODUCT(($I4-$G$3:$G4),--($I4-$G$3:$G40),--($G$4:$G5-$I40),(INDIRECT("R"&1044-$A4-1)):$R$1043)+($I4<$G$4)*($I4*(INDIRECT("R"&1044-$A4-1))) In one cell - or three cells defined below? =($I4=$G$4)*SUMPRODUCT(--($I4-$G$4:$G4=0),(INDIRECT("R"&1044-$A4)):$R$1043,$C$3:$C3) +($I4$G$4)*SUMPRODUCT(($I4-$G$3:$G4),--($I4-$G$3:$G40),--($G$4:$G5-$I40),(INDIRECT("R"&1044-$A4-1)):$R$1043) +($I4<$G$4)*($I4*(INDIRECT("R"&1044-$A4-1))) I think that for auditing purposes three cells would be easier - but did not know if there would be any other advantage splitting the equation up into three cells - and then combining the answer. Note the equation could be copied down 2600 times. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is more efficient
You may want to take a look at this paper:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx It's about improving performance in XL 2007 but most of it is applicable to earlier versions of Excel as well. It also includes a number of macros which can calculate how much time various formulas take to calculate. Dave -- Brevity is the soul of wit. "Brad" wrote: Do it make any difference having the following equation: =($I4=$G$4)*SUMPRODUCT(--($I4-$G$4:$G4=0),(INDIRECT("R"&1044-$A4)):$R$1043,$C$3:$C3)+($I4$G$4)*SUMPRODUCT(($I4-$G$3:$G4),--($I4-$G$3:$G40),--($G$4:$G5-$I40),(INDIRECT("R"&1044-$A4-1)):$R$1043)+($I4<$G$4)*($I4*(INDIRECT("R"&1044-$A4-1))) In one cell - or three cells defined below? =($I4=$G$4)*SUMPRODUCT(--($I4-$G$4:$G4=0),(INDIRECT("R"&1044-$A4)):$R$1043,$C$3:$C3) +($I4$G$4)*SUMPRODUCT(($I4-$G$3:$G4),--($I4-$G$3:$G40),--($G$4:$G5-$I40),(INDIRECT("R"&1044-$A4-1)):$R$1043) +($I4<$G$4)*($I4*(INDIRECT("R"&1044-$A4-1))) I think that for auditing purposes three cells would be easier - but did not know if there would be any other advantage splitting the equation up into three cells - and then combining the answer. Note the equation could be copied down 2600 times. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is more efficient
Dave F wrote...
You may want to take a look at this paper: http://msdn2.microsoft.com/en-us/library/aa730921.aspx It's about improving performance in XL 2007 but most of it is applicable to earlier versions of Excel as well. It also includes a number of macros which can calculate how much time various formulas take to calculate. .... Can't hurt if one has the time to read it. "Brad" wrote: .... [reformatted] =($I4=$G$4)*SUMPRODUCT( --($I4-$G$4:$G4=0), (INDIRECT("R"&1044-$A4)):$R$1043, $C$3:$C3 )+($I4$G$4)*SUMPRODUCT( ($I4-$G$3:$G4), --($I4-$G$3:$G40), --($G$4:$G5-$I40), (INDIRECT("R"&1044-$A4-1)):$R$1043 )+($I4<$G$4)*($I4*(INDIRECT("R"&1044-$A4-1))) .... Efficient in terms of recalculation speed or storage? Generally, one formula in one cell making the same number of function calls as N formulas in N cells combined would use less storage. As for recalc speed, the first rule is avoid volatile function calls, such as INDIRECT, whenever possible. Second rule is to avoid unnecessary calculations. Third is to eliminate as much redundancy as possible. Fourth is to simplify terms when possible. With respect to the first rule, you could (should) replace your (INDIRECT("R"&1044-$A4)):$R$1043 terms with the equivalent nonvolatile INDEX($R:$R,1044-$A4):$R$1043 and similarly for (INDIRECT("R"&1044-$A4-1)):$R$1043. With respect to the second rule, you have 3 additive terms that each begin with related boolean expressions. Use IF calls to avoid calculating the other expressions in each term when the boolean expressions are FALSE, in which case you'd be multiplying by zero. As for eliminating redundancy and simplifying terms, note that if I4 = G4, then the first entry in the first argument of the first SUMPRODUCT call is necessarily TRUE, and similarly if I4 G4, the first term in the third argument of the second SUMPRODUCT call is necessarily FALSE. Specifically, this means the second SUMPRODUCT call could be rewritten as SUMPRODUCT(--($I4-$G$4:$G40),--($G$5:$G5-$I40),($I4-$G$4:$G4), INDEX($R:$R,1044-$A4):$R$1043) This can be simplified further by eliminating the unnecessary subtractions. SUMPRODUCT(--($I4$G$4:$G4),--($G$5:$G5$I4),($I4-$G$4:$G4), INDEX($R:$R,1044-$A4):$R$1043) At this point, the first two SUMPRODUCT calls are summing over the same constructed range INDEX($R:$R,1044-$A4):$R$1043. More efficient to combine the two SUMPRODUCT calls. Also make use of the last term's IF call. =IF($I4<$G$4, $I4*INDEX($R:$R,1043-$A4), SUMPRODUCT( ($I4$G$4)*($I4$G$4:$G4)*($G$5:$G5$I4)*($I4-$G$4:$G4) +($I4=$G$4:$G4)*$C$3:$C3, INDEX($R:$R,1044-$A4):$R$1043 ) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Most efficient formula/combining multiple data cell ranges/seperat | New Users to Excel | |||
more efficient way to lookup a range? | Excel Discussion (Misc queries) | |||
recently used files, more efficient | Excel Discussion (Misc queries) | |||
something more efficient that =IF, and sytax questions | Excel Discussion (Misc queries) | |||
Excel Randoming Becoming More Efficient | Excel Worksheet Functions |