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

[jerrymoon1]

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!!

 

[levipe]

Try

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

 

[dforgacs]

Try this:

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

dforgacs

 

[Thorin]

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

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

 

[jerrymoon1]

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

 

[jerrymoon1]

Thanks Dforgacs - that works perfectly.

 

[Exceljeanie]

Hi,
Sheet2

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >eee</td><td >eee</td><td >eee</td><td >eee</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2.5</td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >=IF(COUNT<span style=' color:008000; '>(A1:D1)</span>=0,"",AVERAGE<span style=' color:008000; '>(A1:D1)</span>)</td></tr><tr><td >E2</td><td >=IF(COUNT<span style=' color:008000; '>(A2:D2)</span>=0,"",AVERAGE<span style=' color:008000; '>(A2:D2)</span>)</td></tr><tr><td >E3</td><td >=IF(COUNT<span style=' color:008000; '>(A3:D3)</span>=0,"",AVERAGE<span style=' color:008000; '>(A3:D3)</span>)</td></tr></table></td></tr></table>

Regards

 

[dforgacs]

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