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

Hi Alison,

As a percentage of what?

--
Kind regards,

Niek Otten

"Alison" <(E-Mail Removed)> wrote in message
news0EE43B9-FA83-4A16-BC31-(E-Mail Removed)...
> 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

On Thu, 23 Feb 2006 04:07:27 -0800, Alison <(E-Mail Removed)>
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

"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.