PC Review


Reply
Thread Tools Rate Thread

Calculating % when 0 is 100%

 
 
=?Utf-8?B?QWxpc29u?=
Guest
Posts: n/a
 
      23rd Feb 2006
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
 
Reply With Quote
 
 
 
 
Niek Otten
Guest
Posts: n/a
 
      23rd Feb 2006
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



 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      23rd Feb 2006
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
 
Reply With Quote
 
=?Utf-8?B?am9ldTIwMDRAaG90bWFpbC5jb20=?=
Guest
Posts: n/a
 
      23rd Feb 2006
"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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
not calculating zero Excel Wizard.... NOT! Microsoft Excel Misc 6 27th May 2009 07:56 PM
not calculating zero Excel Wizard.... NOT! Microsoft Excel Misc 0 27th May 2009 07:26 PM
Calculating recurring date in following month, calculating # days in that period Walterius Microsoft Excel Worksheet Functions 6 4th Jun 2005 11:21 PM
CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS! RICHARD Microsoft Excel Programming 0 15th Mar 2005 01:41 PM
calculating frank Microsoft Excel Misc 4 9th Feb 2005 03:54 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:45 PM.