Results 1 to 8 of 8

How to get rid of #Div/0! and ) from Average formula

This is a discussion on How to get rid of #Div/0! and ) from Average formula within the Excel Questions forums, part of the Question Forums category; Hi, I have a spreadsheet where a 12 month trend is displayed and I calculate the average of the previous ...

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Location
    Houston, Texas
    Posts
    139

    Default How to get rid of #Div/0! and ) from Average formula

    Hi,

    I have a spreadsheet where a 12 month trend is displayed and I calculate the average of the previous 3 months and previous 11 month averages (ignoring the current month of the trend). The 3 month trend is in column Q and the 11 month trend is in column R. When I copy the formula down, I sometimes get a 0 (because of a blank row) or a Div/0 error (due to having lines in the data both ---- and =====) and I would rather have the formula display a blank in these situations. How would I be able to accomplish this? Here are examples of my formula:

    Cell Q16 =average(J16:L16)
    Cell R16 =average(B16:L16)

    Thanks for any help!!

  2. #2
    Board Regular levipe's Avatar
    Join Date
    Nov 2006
    Location
    Atlanta
    Posts
    296

    Default

    Try

    =IF(SUM(J16:L16)>0,AVERAGE(J16:L16),"")

  3. #3
    Board Regular
    Join Date
    Jul 2004
    Posts
    108

    Default How to get rid of #Div/0! and ) from Average formula

    Try this:

    =IF(ISERROR(AVERAGE(J16:L16)),"",AVERAGE(J16:L16))

    dforgacs

  4. #4
    Board Regular Thorin's Avatar
    Join Date
    Feb 2007
    Location
    Suffolk
    Posts
    240

    Default

    The following formula will give an average whilst ignoring both div/0 errors and zero's, is this what you were after ?

    Code:
    =AVERAGE(IF(ISERROR(C4:C10),"",IF((C4:C10)<>0,C4:C10)))
    This is an array formula, so you will need to press Ctrl+Shift+Enter to get the {} around it, amend the C4:C10 range to your own range.
    Kind regards
    Andy

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Location
    Houston, Texas
    Posts
    139

    Default

    The number can also be a negative, so I dont think that formula will work in all cases. How can I compensate for that?

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    Houston, Texas
    Posts
    139

    Default

    Thanks Dforgacs - that works perfectly.

  7. #7
    Board Regular
    Join Date
    Jul 2006
    Posts
    122

    Default

    Hi,
    Sheet2

    *ABCDE
    1eeeeeeeeeeee*
    2*****
    312342.5

    Spreadsheet Formulas
    CellFormula
    E1=IF(COUNT(A1:D1)=0,"",AVERAGE(A1:D1))
    E2=IF(COUNT(A2:D2)=0,"",AVERAGE(A2:D2))
    E3=IF(COUNT(A3:D3)=0,"",AVERAGE(A3:D3))


    Regards

  8. #8
    Board Regular
    Join Date
    Jul 2004
    Posts
    108

    Default How to get rid of #Div/0! and ) from Average formula

    I beleive that the ISERROR will handle any errors in the calculation and return a ""
    dforgacs

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