Another #DIV/0! Issue

[Debbie]

I have the following:

A1: 17.33 B1: 0
A3: 43.25 B3: 0
A5: 40.07


I have in cell A5:
=(a1+b1)/(a3+b3)
The result is: A5: 40.07

On several of my associates, I have a "0" in those fields.

I tried entering =IF(a5=0,"",(a1+b1)/a3+b3))
This returns a result of 0 where I should have 40.07.

I can use this calculation in any other cell and I get the correct results.
Is there a way to have Excel do the calculation, if the result is "0", place
a "0", else enter the calculated amount.

Thanks for your help.

Debbie

 

[Mike H]

I'm a bit confused

a1+b1= 17.33
a3+b3= 43.25

17.33/43.25= 0.400694
the answer you get of 40.07 is the correct answer*100 to 2 decimal places
also there is n0 div by zero
why do you think the correct answer is 40.07?

Mike

 

[Meebers]

My results are 0.400694 A5 must be formated as %?

 

[Mike H]

ahh it's a percentage, you never mentioned that!!

 

[Debbie]

Correct! It's a percentage! Sorry I didn't mention that.

So, is there a way for the cell to calculate the results and if the results
are >0 please post the results. If the results are 0, please show 0 not
#DIV/0!.


Thanks,

Debbie

 

[Tyro]

On several of my associates, I have a "0" in those fields.
I don't understand that statement. Could you elaborate?

Tyro

 

[Tyro]

Sure

=IF(ISERROR(Your_formula),0,Your_formula)

Tyro

 

[David Biddulph]

I'm confused as to how you think that (17.33+0)/(43.25+0) would come to
40.07. I think you mean 0.4007?

But if you are trying to avoid a divide by zero error, you could use
=IF(a3+b3=0,"",(a1+b1)/(a3+b3)) or even =IF(a3+b3,(a1+b1)/(a3+b3),"")

 

[Debbie]

Yes! I have Developers that don't work on projects directly. So, they have
non billable hours. As do my Tech Support folks. However, I have creative
people that stricktly work on the clients sets. These are all billable hours.
That's what I'm trying to calculate. I have a cell for my non billable hours
and a cell for my billable hours. The cell I'm filling is for the Billable
percentage. However, as explained, not all my folks are "billable". So, when
setting up the sheets, they have 0's in those fields creating the error
issue.

 

[Mike H]

1 way

=IF(A3+B3=0,"",(A1+B1)/(A3+B3))

Mike

Yes! I have Developers that don't work on projects directly. So, they have
non billable hours. As do my Tech Support folks. However, I have creative
people that stricktly work on the clients sets. These are all billable hours.
That's what I'm trying to calculate. I have a cell for my non billable hours
and a cell for my billable hours. The cell I'm filling is for the Billable
percentage. However, as explained, not all my folks are "billable". So, when
setting up the sheets, they have 0's in those fields creating the error
issue.

 

[Debbie]

Thank you so much for your time!
Those suggestions worked perfectly.



:
(David, this was a
percentage, sorry)

 

[Meebers]

So....how can we get in the "billing" column <g>

 

[Debbie]

I'll work on that next.

So....how can we get in the "billing" column <g>