Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating time | Excel Discussion (Misc queries) | |||
Calculating Cells n% displayed in a cell? | Excel Worksheet Functions | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |