Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: averages - Ignoring some cells

  1. #1
    Board Regular
    Join Date
    Sep 2013
    Posts
    1,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default averages - Ignoring some cells

    I need to chart the average of some data over a number of months.

    For example, I have 12 columns, Jan to Dec.

    In each month is a percentage.

    Jan - 46%
    Feb - 25%
    Mar - 60%
    Apr - 55%
    May - 47%
    June - 38%

    Now, I need to calculate the average of these percentages, which is easy aprt from this.

    I need to take the average of all 12 months, but only include the months will good data, ie, jan to jun. July to december has a formula in there but not good data as yet.

    So when data is good for July and entered this will become part of the average. As it is now my average takes into account all 12 months therefore incorrect.

    All help appreciated.

  2. #2
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,852
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: averages - Ignoring some cells

    What do you mean by "good data"?
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: averages - Ignoring some cells

    I think what he means is, that his formula goes all the way along from Jan to Dec, but because we're only in June now, July to December is either blank or 0%. He wants the chart data range to encompass all 12 months, so that as the months progress, the chart keeps 'drawing' the additional data. But he doesn't want a line that goes from 38% down to 0% and all the way along the axis.
    MS Excel 2010

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Posts
    1,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: averages - Ignoring some cells

    Exactly Zakkaroo. Good data is meaningful data, the result of data added, not just the result of a formula, waiting for data.

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,852
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: averages - Ignoring some cells

    If that is the case then try averageif

    =averageif(A2:L2,">"&0,A2:L2)
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    Board Regular
    Join Date
    Jul 2009
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: averages - Ignoring some cells

    the only way I know to do this, is to add an #N/A into your formula - as graphs ignore #N/A's.

    So lets say your percentage is calculated with the formula

    =A1/B1

    And 'bad' data is when A1 = 0

    you could do ...

    =if(A1=0,NA(),A1/B1)
    MS Excel 2010

  7. #7
    Board Regular
    Join Date
    Sep 2013
    Posts
    1,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: averages - Ignoring some cells

    Thanks to both of you for your help. Gaz-Chops, formula works as I need it to. Superb, thanks.

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,852
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: averages - Ignoring some cells

    You're welcome.
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  9. #9
    Board Regular
    Join Date
    Jul 2011
    Posts
    599
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: averages - Ignoring some cells

    Quote Originally Posted by gaz_chops View Post
    If that is the case then try averageif

    =averageif(A2:L2,">"&0,A2:L2)
    for older versions of excel use

    =SUM(A2:L2)/COUNTIF(A2:L2,">0")

  10. #10
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,852
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: averages - Ignoring some cells

    Quote Originally Posted by uk747 View Post
    for older versions of excel use

    =SUM(A2:L2)/COUNTIF(A2:L2,">0")
    I think (maybe wrong), countif & averageif became available from Excel 2007.
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

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
  •