No More #DIV/0!

[Billionzz]

I have the following formula in cell D54(=1650/C54)I get #DIV/0! when there is a blank space in coloum C.

This isn't a problem itself but when I try to run the average of C2:C100 it doesn't work because of the blank spaces.

Would someone tell me the way around this?

Thanks for your help.

Bill

 

[Juan Pablo González]

AVERAGE should ignore text and blanks, so, this

=AVERAGE(C2:C100)

should work ok.

Look at the example:

Book3
	A	B	C	D
1	1
2
3	2
4
5	3
6
7	4
8
9	2.5
Sheet1

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-08-26 20:03

 

[Billionzz]

Hi Juan,

When I try to get the average for C2:C100 I get the error #DIV/0! if any of the cells C2:C100 have #DIV/0! in them.

Bill
This message was edited by Billionzz on 2002-08-26 20:52

 

[Juan Pablo González]

Yes, but, that's not what you first said. You said that the error was in column D (D54 actually), and that you were having problems to average column C... so... confused now.

 

[voodoo]

Yes your question is confusing. I presume you mean yu are trying to get the average of column D (as column D is where the #DIV/0 entry is).

You shoule be able to get the AVERage for column C as Pablo has said (even if column C contains blank or NULL entries).

If you are infact tryign to AVERAGE column D then you could try an if statement like this

IF(C64="",0,1650/C64)

This will place zero in column D if column C is blank.

If this is not what you want I think you need to explain your question in more detail.

voodoo

 

[Billionzz]

Hi Juan & voodoo,

Sorry for posting the cells as C when they should have been D.

But voodoo you figured it out and your solution works good.

Thanks to both of you again.

Bill