Formulas - #DIV/O! Result

[NICOLAW]

Hi All

I am trying to work out if there is an excel formula to calculate the below.....

I have 2 cells A & B and if i divide A by B and the result is 0 (ie there being 0 in both A & B) I get a #DIV/O! result when I need it to show a 0

Can anyone help with this? Thanks, Niki

 

[barry houdini]

Try

=IF(B1,A1/B1,0)

 

[Datsmart]

Try this:

=IF(B1<0.1,0,A1/B1)

This will result in 0 when B is zero or blank.

 

[dave3009]

Hi

Welcome to the board

=IF(ISERROR(A2/B2), 0, A2/B2)

Placed in C2 will return a zero (0) when there is an error withh your equation.

HTH


Dave

 

[NICOLAW]

Thanks ever so much everyone what speedy responses!!

 

[NICOLAW]

Sorry just another opne now as not to sure it is working now?

This is a simple calculation of average prices for something
Eg of working sample
A1 4 ROOMS BOOKED
A2 1000 REVENUE
A3 SHOULD BE AVERAGE REV TAKEN PER ROOM A2/A1=250

So I would like a formula so if for example A1 & A2 had 0 in it it would say the averaghe would be 0 instead of #DIV/O!

 

[dave3009]

try

=IF(AND(A1=0, A2=0), 0, IF(ISERROR(A1/A2), 0, A1/A2)

I think would suit your needs

HTH


Dave

 

[NICOLAW]

Many Thanks!!
Much appreciated!

 

[NICOLAW]

Hi,

Im afraid this isnt working??

 

[barry houdini]

Try amending my original suggestion above

=IF(A1,A2/A1,0)

If that doesn't work then please explain why, what result you got and why that wasn't what you expected