Division by 0

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I have a column that averages three others: =AVERAGE(A5:C5). However, the
unused (not yet used) rows in that column display #DIV/0!

How can I hide that, please?

Thank you

JB
 
2 ways to do it
=IF(COUNT(A5:C5)<5,"",AVERAGE(A5:C5))
=IF(ISERROR(AVERAGE(A5:C5)),"",AVERAGE(A5:C5))
The former will "restrict" evaluation unless all 3 cells in the row-range
contain numbers, while the latter is a generic error trap w/o the implicit
condition. It'll evaluate once there are numbers within any of the 3 cells
in the row-range.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 
Thanks

I will try the second as I need the average of whatever fields contain a
value, while some may, at times, be empty.

JB
 
Great. It worked

Now, I need to do the same in another column that at the moment is =E4/18

Before I lose all my data ... should I try: =IF(ISERROR(E4/18),"",E4/18)

Thanks

JB
 
Hi JB,

You wrote..
I will try the second as I need the average of whatever fields contain a
Click to expand...
value, while some may, at times, be empty.

Does that mean you want the average of only the number of cells that
contain a value. EX: A1=2,B1=4,C1=6, your average =4. If B1 was blank,
should your average be also be 4 (8/2 actual values) or 2.66 (8/3).

If the answer should be 4, then try...

=IF(SUM(A6:C6)=0,"",SUM(A6:C6)/COUNT(A6:C6))

Regards,
Justin
 
Thanks Max, that did it.

Can I try one more? One column has the date which I enter manually. If I
type 15may it converts immediately to 15-May which is great. But how can I
get the line to enter dates automatically?

Thank you very much

JB
 
Justin Case said:
=IF(SUM(A6:C6)=0,"",SUM(A6:C6)/COUNT(A6:C6))
Click to expand...

An example of inexperience.

Numbers can be negative and zero as well as positive. If A6:C6 all
contained 0, the average would be zero. However, the sum would also be
zero, and your formula would incorrectly return "".

Trapping #DIV/0! errors involves checking the DENOMINATOR, not the
numerator. Several preceding responses tested COUNT(A6:C6). You either
didn't read those responses or didn't understand them.
 
Back
Top