Division by 0

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
 
M

Max

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
 
J

JB

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
 
J

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
 
J

Justin Case

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
 
J

JB

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
 
H

Harlan Grove

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignoring Blank cells 9
Averages 4
Need Adaptable Macro 9
Copy Existing Formula Into New Row 0
change cell color depending on two cell values 1
Averageifs - criteria is cell is not blank 1
Averaging only some rows 4
Dealing with a Pivot Table (when I don't want to) 0

Top