Results 1 to 7 of 7

Excluding #DIV/0! from yearly average

This is a discussion on Excluding #DIV/0! from yearly average within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet that averages the year service level percentage. However, the upcoming months have no data and bring ...

  1. #1
    New Member
    Join Date
    May 2003
    Location
    Orange County
    Posts
    12

    Default Excluding #DIV/0! from yearly average

    I have a spreadsheet that averages the year service level percentage. However, the upcoming months have no data and bring back a #DIV/0! in the total field, which messes up my yearly average. How would I average the year, but exclude those error messages.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,862

    Default Re: Excluding #DIV/0! from yearly average

    What is the range? Are the upcoming months cells really empty or do they house real 0's?

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: Excluding #DIV/0! from yearly average

    Hi,

    Aladin has some posts that handle this, and his question still needs a response to give you the best answer.

    Here are two alternatives, producing different results depending on what you require. Aladin has a few more as well, depending on your foloow-up to his post.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    11**
    2
    21**
    3
    31**
    4
    #DIV/0!0**
    5
    51**
    6
    61**
    7
    71**
    8
    81**
    9
    #DIV/0!0**
    10
    101**
    11
    ****
    12
    ****
    13
    4.2***
    14
    5.25***
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Bye,
    Jay

  4. #4
    New Member
    Join Date
    May 2003
    Location
    Orange County
    Posts
    12

    Default Re: Excluding #DIV/0! from yearly average

    Basically, the workbook is set-up with individual spreadsheets. Each spreadsheet represents a month. The last spreadsheet pulls the numbers from the other spreadsheets and gives me an average for the year.

    So, the last spreadsheet is set-up with Jan-Dec and pulls from a specific area on the other spreadsheets.

    Hope this helps.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,862

    Default Re: Excluding #DIV/0! from yearly average

    Quote Originally Posted by GordoB
    Basically, the workbook is set-up with individual spreadsheets. Each spreadsheet represents a month. The last spreadsheet pulls the numbers from the other spreadsheets and gives me an average for the year.

    So, the last spreadsheet is set-up with Jan-Dec and pulls from a specific area on the other spreadsheets.

    Hope this helps.
    It seems you have an ordinary average formula in a certain cell in each month worksheet. If so, you could use a different average formula in such a worksheet: something like...

    =IF(SUM(Range),AVERAGE(Range),"")

    the result of which would not mess up the last worksheet.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,862

    Default Re: Excluding #DIV/0! from yearly average

    Jay,

    How about the simpler...

    =SUMIF(A1:A10,"<>#DIV/0!")/MAX(1,COUNT(A1:A10))

    or

    =SUMIF(A1:A10,"<>#DIV/0!")/MAX(1,COUNT(A1:A10)-COUNTIF(A1:A10,0))

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: Excluding #DIV/0! from yearly average

    Hi Aladin,

    I am all for the alternatives. No complaints from me. Simple and efficient. Can't beat that combination.

    I will go back to the VBA stuff now. I made this much more complicated than necessary. Sometimes the "whatever works..." is not the best way to do something.
    Bye,
    Jay

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
  •  


DMCA.com