Excel 2007 : If formula- help with the IF formula.

[juliekimho1964]

Please help with the IF formula.

Example: If A1-B1/A1 = 0

I would like my answer is 0 instead of #DIV/0!

Thank you in advance.

[Cutter]

Welcome to the forum.

Try:

=IF(A1=0,0,A1-B1/A1)

[juliekimho1964]

Welcome to the forum.

Try:

=IF(A1=0,0,A1-B1/A1)

Cutter,

Thank you for your help.

One of my co-workers showed me a different formula but I think yours is easier for me to understand but just wanted to make sure you and I understand correctly before I switch to your formula

Here it is:

Column L51 (Billed)
Column M51 (Cost)

My formula is like this L51-M51/L51. This give me Profit % on REVENUE. But with this formula, it give me #DIV/0! instead of $0.

Is this your IF formula to me? =IF(L51=0,0,L51-M51/L51)

And Profit % on COST should be =IF(M51=0,0,L51-M51/M51), right?

THANK YOU again for your help!!!!

[Cutter]

The #DIV/0 error happens when there is a value being divided by 0 - which is not valid.
So, to avoid a value being divided by 0 you first test to make sure that there won't be a 0.
In your first example:
=IF(L51=0,0,L51-M51/L51)
you're testing to see if L51 is 0. If it is you'll get a 0 result instead of #DIV/0.
If L51 is not 0 you'll get L51-M51/L51 but you should have (L51-M51)/L51 to make the L51-M51 calculate before that result is divided by L51. I would say that would be your Profit on Revenue and your Profit on Cost would be =(L51-M51)/M51 so, yes, you would have =IF(M51=0,0,(L51-M51)/M51)

[juliekimho1964]

Thanks Cutter,

I saw my mistake before replied back to you so I put (L51-M51) in the bracket before devided by L51.

Thanks again! You are so very nice to help me out!!!

This site is very very helpful!!!

[Cutter]

You're welcome - don't forget to mark your thread as SOLVED (click FAQ at top of page for instructions).