Formulas - #DIV/O! Result

NICOLAW

New Member
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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try

=IF(B1,A1/B1,0)

Try this:
Code:
``=IF(B1<0.1,0,A1/B1)``
This will result in 0 when B is zero or blank.

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

Thanks ever so much everyone what speedy responses!!

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!

try

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

I think would suit your needs

HTH

Dave

Many Thanks!!
Much appreciated!

Hi,

Im afraid this isnt working??

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

Replies
2
Views
164
Replies
2
Views
298
Replies
5
Views
206
Replies
2
Views
102
Replies
5
Views
330

Forum statistics

1,221,050
Messages
6,157,623
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

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.

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