Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: subtotaling

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Start with a list of 100 numbers in column A. Display the average of the first five numbers in B5, the average of the next 5 numbers in B10, etc.

    John Adams

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is this a question?

    if so, then a simple formula in B5 as follows...

    =AVERAGE(A1:A5)

    ...will give you the value you want, and then you can copy and paste this cell every 5 cells. If you want a formula that you can fill down the whole list to give the relevant average every 5 cells, use the following in cell B5 and fill down from there...

    =IF(ROUND((CELL("row",A5)/5),0)=(CELL("row",A5)/5),AVERAGE(A1:A5),"")



    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or, simply enter...

    =IF(MOD(ROW(),5),"",AVERAGE(OFFSET(A1,-4,,5)))

    ...into B1 and copy down.

    [ This Message was edited by: Mark W. on 2002-04-19 13:36 ]

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 13:30, daleyman wrote:
    is this a question?

    if so, then a simple formula in B5 as follows...

    =AVERAGE(A1:A5)

    ...will give you the value you want, and then you can copy and paste this cell every 5 cells. If you want a formula that you can fill down the whole list to give the relevant average every 5 cells, use the following in cell B5 and fill down from there...

    =IF(ROUND((CELL("row",A5)/5),0)=(CELL("row",A5)/5),AVERAGE(A1:A5),"")
    Hi daleyman:

    Why not use your earlier simpler formula
    =AVERAGE(A1:A5)

    highlite cells A1:A5 and then drag down ... just curious about the merits of using the other long formula you suggested.

    Regards!

    Yogi Anand

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hi daleyman:

    Why not use your earlier simpler formula
    =AVERAGE(A1:A5)

    highlite cells A1:A5 and then drag down ... just curious about the merits of using the other long formula you suggested.

    Regards!

    Yogi Anand
    I for one prefer to have a uniform formula in a given column. That way after row insertions/deletions one can easily fill down the formula from the top row without having to worry about which rows should contain the formula. Also, it makes it easier to use the Edit | Go To... Special Column Differences command to detect inconsistencies among the formulas of a given column.

    Of course, I also prefer my own formulation...

    =IF(MOD(ROW(),5),"",AVERAGE(OFFSET(A1,-4,,5)))

    ...but no surprise there!

    [ This Message was edited by: Mark W. on 2002-04-19 13:52 ]

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I for one prefer to have a uniform formula in a given column. That way after row insertions/deletions one can easily fill down the formula from the top row without having to worry about which rows should contain the formula. Also, it makes it easier to use the Edit | Go To... Special Column Differences command to detect inconsistencies among the formulas of a given column.
    Good point Mark! ... makes for a more robust setup. It is a pleasure to get your insightful comments.

    Regards!

    Yogi Anand

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yep, i bow at your feet! Got the ball rollin' though huh?

    Yogi, was just doin it logically with my existing skills, not with Marks expertise (his MOD solution is very nice).

    _________________

    DALEY


    [ This Message was edited by: daleyman on 2002-04-19 15:38 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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