Results 1 to 9 of 9

20080604, 14:44 #1
 Join Date
 Jan 2001
 Location
 Newfoundland
 Posts
 41
 Thanks
 4
 Thanked 0 Times in 0 Posts
Addition every nth row (Excel 2002 SP3)
Is there a function in Excel which will add values from every nth row in a data set?

20080604, 14:52 #2
 Join Date
 Dec 2002
 Location
 Perth, Western Australia, Australia
 Posts
 730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
Not specifically, but a simple formula should do it (wouldn't surprise me if Hans didn't have one up his sleeve, ready to go).
A littlle more info about what you're trying to achieve, maybe a sample scenario, should get you a more solution.<font face="Comic Sans MS" color="blue">TimOz</font>
<img src=/S/flags/Finland.gif border=0 alt=Finland width=30 height=18> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

20080604, 15:03 #3
 Join Date
 Jan 2001
 Location
 Newfoundland
 Posts
 41
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
Within one worksheet, I have set up 50row blocks to contain data related to a single item (60 columns (years) wide). There are 2530 items within the worksheet. At the bottom of the worksheet, I want a total, for example, for all of the 3rd rows within each 50row block.

20080604, 15:07 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Addition every nth row (Excel 2002 SP3)
There's no builtin function for this (as far as I know).
Let's say that you want to add every 5th cell in A1:A100, starting at row 2. You can use the following formula:
=SUMPRODUCT((MOD(ROW(A1:A100),5)=2)*A1:A100)

20080604, 15:11 #5
 Join Date
 Jan 2001
 Location
 Newfoundland
 Posts
 41
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
Thanks very much. That's exactly what I was looking for.

20080604, 17:02 #6
 Join Date
 Jan 2001
 Location
 Newfoundland
 Posts
 41
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
This formula works beautifully ... but I'm not sure I understand why. Would you be able to break it down, so mere mortals like myself can understand step by step. Thanks.

20080604, 17:26 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Addition every nth row (Excel 2002 SP3)
The SUMPRODUCT function takes two (or more) arrays, multiplies the corresponding elements and adds the results. For example, SUMPRODUCT(A1:A3,B1:B3) is evaluated as A1*B1 + A2*B2 + A3*B3.
In the formula I posted, the two arrays are MOD(ROW(A1:A100),5)=2 and A1:A100. The latter is the column containing the values that you want to add.
ROW(A1:A100) returns the list of row numbers in A1:A100, i.e. 1, 2, 3, 4, 5, 6, 7, 8, ...., 99, 100.
MOD(...,5) returns the remainder of the first argument after division by 5.
So MOD(ROW(A1:A100),5) returns 1, 2, 3, 4, 0, 1, 2, 3, ..., 4, 0.
MOD(ROW(A1:A100),5)=2 returns a list of TRUE/FALSE values  TRUE if the remainder is 2, FALSE otherwise: FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, ...
In calculations in Excel, FALSE is equivalent to 0 and TRUE is equivalent to 1, so we have 0, 1, 0, 0, 0, 0, 1, 0, ...
As you see, every 5th element starting with the 2nd equals 1, all others are 0.
When we combine this with A1:A100 in SUMPRODUCT, only the cells in row 2, 7, 12 etc. contribute to the result because they are multiplied with 1, the others don't because they are multiplied with 0.
Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

20080604, 18:22 #8
 Join Date
 Jan 2001
 Location
 Newfoundland
 Posts
 41
 Thanks
 4
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
Thank you so much for taking the time to explain this. Amazing!

20080604, 18:52 #9
 Join Date
 Dec 2002
 Location
 Perth, Western Australia, Australia
 Posts
 730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Addition every nth row (Excel 2002 SP3)
Same sleeve as the formula?
If Hans' explanation needs further explanation you can also use the builtin Excel resources.
The "fx" (insert function) button on the formula bar (or ShiftF3) describes and allows you to select among available functions, and guides you through parameter entry, step by step. If the brief guidance in the dialog is insufficient, you can click on the "Help on this function" link (bottom left) to take you direct to function specific help.
What I often do to build up a complex composite of functions, as per Hans' solution, is to work out each component in a separate cell before combining them. This process is especially useful to get my head around unfamiliar functions.
(If you only have single copy of your (precious) formula, make a copy before trying the following.)
The other trick I use is the F9 key for partial calculations (to debug formulae). If you select a part of the formula in the formula bar, and press F9, the selection is replaced by its calculated value. The selection can be anything from a single cell reference, to one, or more, functions. When you make the selection, limit cell references to to just the cell reference, and for functions include everything from the function name to the closing parenthesis. Repeated select/F9s let you "step" through a formula to see how the final result is calculated. You can use ESC to "back out" from the partial calculation(s)  ENTER will save the formula with the calculated values in place.
If you're really keen, you can always dive straight into the Excel help, but be prepared to guess the sometimes elusive, correct terminology (as with most help systems).<font face="Comic Sans MS" color="blue">TimOz</font>
<img src=/S/flags/Finland.gif border=0 alt=Finland width=30 height=18> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>