Forumla for %, i.e. 4 over 0 should equal 400% but get #DIV/0!

[Leland7]

I am doing comparison figures to determine percent of change. Some figures
compare for example 4 to 0 and instead of getting an increase of 400%, get
#DIV/0! .... anyone know a formula that would give me the right percent?

 

[JE McGimpsey]

This would give you the result you want:

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

formatted as a percentage, but that makes zero sense mathematically.

For instance

A B C
1 4 0.00 400%
2 4 0.01 39900%
3 4 0.80 400%

So your resulting "increase" would be 400% if you started at 0 or 0.80.
How would you tell the difference?

 

[Toppers]

Something like:

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

Where A1= original Value
B1=new Value

 

[Leland7]

Fortunately, I'm not going to have any decimal point digits ... so for my
needs, this will work !!! THANKS A BUNCH !

 

[Leland7]

This works ...... Thank you !!!

Something like:

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

Where A1= original Value
B1=new Value

 

[Bruce Sinclair]

Fortunately, I'm not going to have any decimal point digits ... so for my
needs, this will work !!! THANKS A BUNCH !

It may well give you the answer you want ... just be aware that it is still
wrong. There are good reasons why dividing by zero makes no sense and should
be an error.
Hiding the resulting error is another question

Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Dharmesh]

It may well give you the answer you want ... just be aware that it is still
wrong. There are good reasons why dividing by zero makes no sense and should
be an error.
Hiding the resulting error is another question


Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)