# No More #DIV/0!

#### Billionzz

##### Board Regular
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?

Bill

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Juan Pablo González

##### MrExcel MVP
AVERAGE should ignore text and blanks, so, this

=AVERAGE(C2:C100)

should work ok.

Look at the example:
Book3
ABCD
11
2
32
4
53
6
74
8
92.5
Sheet1

_________________
Regards,

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

#### Billionzz

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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

Replies
6
Views
403
Replies
3
Views
797
Replies
21
Views
2K
Replies
0
Views
471
Replies
1
Views
548

1,190,696
Messages
5,982,343
Members
439,775
Latest member
mathewduffy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back