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" <[email protected]> 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"
<[email protected]> 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" <[email protected]> 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" <[email protected]> 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.