Addition every nth row (Excel 2002 SP3)

Topic

New Reply

Is there a function in Excel which will add values from every nth row in a data set?

Reply | Quote

Replies

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.

Reply | Quote

Within one worksheet, I have set up 50-row blocks to contain data related to a single item (60 columns (years) wide). There are 25-30 items within the worksheet. At the bottom of the worksheet, I want a total, for example, for all of the 3rd rows within each 50-row block.

Reply | Quote

There’s no built-in 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)

Reply | Quote

Thanks very much. That’s exactly what I was looking for.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Thank you so much for taking the time to explain this. Amazing!

Reply | Quote

Same sleeve as the formula?

If Hans’ explanation needs further explanation you can also use the built-in Excel resources.

The “fx” (insert function) button on the formula bar (or Shift-F3) 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).

Reply | Quote