Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


Hi,

I'm not sure I fully understand but perhaps this

=AVERAGE(IF(A2:B5<0,A2:B5))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"dernspiker" wrote:

Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below.
The last to rows equal zero. So when I average all the rows of course it
sees those cells as zero. I tried using N/A but it stil sees those as a
zero. Please help I have been working on this huge spreadsheet for a while
now and really need to get it right.

A B C

5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3)
5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4)
0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5)
0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6