Summing Every 40th cell?
 

How can I Sum

A43+A83+A123+A163 into infinity.

This is crucial to making a worksheet to track #'s of resumes from various
web postings over time.

"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.

=SUMPRODUCT(--(MOD(ROW(A43:A65536),40)=3),A43:A65536)

If it doesn't work as I show, try entering it as an array formula with
CTRL+SHIFT+ENTER.

On Thu, 31 Mar 2005 18:03:01 -0800, "Mutcluck"
wrote:

How can I Sum

A43+A83+A123+A163 into infinity.

This is crucial to making a worksheet to track #'s of resumes from various
web postings over time.

But the latter contains a volatile function. Naughty.

-----Original Message-----
"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.


.

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.