Calculating % when 0 is 100%
 

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

Calculating % when 0 is 100%
 

Hi Alison,

As a percentage of what?

--
Kind regards,

Niek Otten

"Alison" wrote in message
...
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

Calculating % when 0 is 100%
 

On Thu, 23 Feb 2006 04:07:27 -0800, Alison
wrote:

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

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

Calculating % when 0 is 100%
 

"Alison" wrote:
I am trying to calculate how far off each value
is from 0 as a %...

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.