# 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. ## 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. Try

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

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

Try this:

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

dforgacs

4. 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.

5. 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. Thanks Dforgacs - that works perfectly.

7. Hi,
Sheet2

 * A B C D E 1 eee eee eee eee * 2 * * * * * 3 1 2 3 4 2.5

 Cell Formula 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. ## 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

#### Posting Permissions

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