Converting a Lotus @nsumfunction to Excel

[kcmart]

I am trying to convert an old Louts 1-2-3 spreadsheet to Excel and am
having problems with one function - the Lotus @nsum function.
Ultimately what I need to do is be able to write a function that will,
beginning in row 2 sum every 5th row.

Any suggestions?

 

[Frank Kabel]

Hi

as I don't know the Lotus 1-2-3 function my assumption is that you want
to sum row 2, 7, 12, etc. Then use
=SUMPRODUCT((MOD(ROW(A1:A999)-2,4)=0)*(A1:A999))

HTH
Frank

 

[Daniel.M]

Frank,

By definition, you can't have a MOD(YYY,4) function resulting in a 0 every 5th
row.

You need to have a 5 in there ==> MOD(YYY,5)

YYY varies depending on where you want to start (as you indicated).

Regards,

Daniel M.

 

[Frank Kabel]

Hi Daniel

you're right, was a typo while testing this formula and not changing it
back for the posting. Thanks for pointing this out!

Frank

 

[kcmart]

Frank,

I am actually trying to sum the rows within a column - for example a1,
a6, a11, a16 etc.

Kyle

 

[Daniel.M]

Kyle,

What's wrong with:

=SUMPRODUCT((MOD(ROW(A1:A999)-1,5)=0)*(A1:A999))

Regards,

Daniel M.

 

[Harlan Grove]

as I don't know the Lotus 1-2-3 function my assumption is that you want
to sum row 2, 7, 12, etc. Then use
=SUMPRODUCT((MOD(ROW(A1:A999)-2,4)=0)*(A1:A999)) ...

For single column, multiple row ranges, and after the already mentioned
correction of MOD(.,5) instead of MOD(.,4), the formula above works. However,
the 123 @NSUM function has syntax @NSUM(offset;n;list) where list may be an
arbitrary number of arguments, e.g., @NSUM(0;1;A1..A5,123,D1..X1,987), and the
offset value may be larger than the n (step) argument. Providing closest
possible functionality in Excel would require a user-defined function. One
thorny problem is how to handle 3D blocks.

FTHOI, here's another approach that handles nontrivial 2D ranges and allows the
offset value to be greater than the n (step) value.

=SUMPRODUCT(((COLUMN(E4:I9)-CELL("Col",E4:I9))*ROWS(E4:I9)+ROW(E4:I9)
-CELL("Row",E4:I9)>=11)*(MOD((COLUMN(E4:I9)-CELL("Col",E4:I9))*ROWS(E4:I9)
+ROW(E4:I9)-CELL("Row",E4:I9),7)=MOD(11,7))*E4:I9)

Note: this follows 123's iteration order, down then right, rather than Excel's,
which is right then down.