What is more efficient

[Brad]

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:$G4>0),--($G$4:$G5-$I4>0),(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:$G4>0),--($G$4:$G5-$I4>0),(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.

 

[Dave F]

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

 

[Harlan Grove]

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:$G4>0),
--($G$4:$G5-$I4>0),
(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:$G4>0),--($G$5:$G5-$I4>0),($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
)
)