Calculating % when 0 is 100%

A

Alison

Hi, I am trying to calculate a % where 0 is 100%. I am trying to calculate
how far off each value is from 0 as a %...

Thanks,
Alison
 
R

Ron Rosenfeld

Hi, I am trying to calculate a % where 0 is 100%. I am trying to calculate
how far off each value is from 0 as a %...

Thanks,
Alison
Click to expand...

If zero is your base, the percent difference of other values is not defined, so
far as I know.

If you describe in more detail what you are trying to do, perhaps a solution
will become apparent.
--ron
 
J

joeu2004

Alison said:
I am trying to calculate how far off each value
is from 0 as a %...
Click to expand...

Mathematically, it is not possible, and arguably it is
nonsensical. What is 50% of 0? Let's see: 0.5*0 = ?.
Nonetheless, it is something we all want to do, if only
for aesthetic reasons.

The best answer is to leave the cell blank:

=if(b1=0, "", a1/b1 - 1)

But there are often times when we "must have" a number.

Often, I use the the unit difference as the percentage
difference. For example, 1 is a 100% increase over zero,
2 is 200%, etc. This can be accomplished as follows,
formatting the cell as Percentage:

=if(b1=0, a1, a1/b1 - 1)

If the numbers (a1, b1) tend to be in a much higher range
-- for example, 1000s -- this has the unfortunate effect of
making the delta from zero look like 100000% or more.
For very small numbers, the delta looks too small. Several
alternatives to adjust for that:

=if(b1=0, 100%, a1/b1 - 1)
=if(b1=0, a1/1000, a1/b1 - 1) 'for very big numbers
=if(b1=0, a1/0.001, a1/b1 - 1) 'for very small numbers

All of these choices are arbitrary. None is mathematically
more correct than another; they are all equally incorrect
mathematically. You simply make the choice that suits
your purposes and sense of aesthetics best.
 
Top