Results 1 to 9 of 9
  1. #1
    Lounger
    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?

  2. #2
    5 Star Lounger
    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>

  3. #3
    Lounger
    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 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.

  4. #4
    Plutonium Lounger
    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 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)

  5. #5
    Lounger
    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.

  6. #6
    Lounger
    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.

  7. #7
    Plutonium Lounger
    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>

  8. #8
    Lounger
    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!

  9. #9
    5 Star Lounger
    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 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).
    <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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •