Harlan Grove wrote:
"Mutcluck" wrote...
How can I Sum
A43+A83+A123+A163 into infinity.
...
Two ways.
=SUMPRODUCT(--(MOD(ROW(A43:A65536),40)=3,A43:A65536)
=SUMPRODUCT(N(OFFSET(A43,40*(ROW(1:1638)-1),0)))
The latter does less work.
The temporal profile of the latter, across runs, seems to converge to:
Re Calc: 4.0 millisecs
Full Calc: < 10 millisecs
% Volatility: About between 50.0 and 100.0
Microsecs/Formula: About between 4,000.0 and 7,100.0
=SUMPRODUCT(--(MOD(ROW($A$43:INDEX($A$43:$A$65536,B1))-ROW($A$43)+0,40)=0),$A$43:INDEX($A$43:$A$65536,B1) )
with B1:
=MATCH(9.99999999999999E+307,A43:A65536)
we have:
Re Calc: 0.4 millisecs
Full Calc: Around 50 millisecs
% Volatility: Less than 1
Microsecs/Formula: Around 27,500.0
Substituting CELL("Row",$A$43) for ROW($A$43) worsens the performance
considerably.
I wished FastExcel could be asked to produce average values across N
runs, where N can be set by the user.
|