Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
"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. |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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. . |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a cell that accumulates, numbers from another cell... | Excel Discussion (Misc queries) | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |