Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to show negative percentage?
If I divide two negative numeric cells and put the result into a
percentage cell, it will positive, even if the change is negative. For example: A B 1 -6249 -5810 A1/B1 = 107.56% The change is moving from B to A. That means I should have a negative change. How can I show that? Thanks, Brett |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to show negative percentage?
Harlan Grove is pretty smart.
Biff "brett" wrote in message ups.com... If I divide two negative numeric cells and put the result into a percentage cell, it will positive, even if the change is negative. For example: A B 1 -6249 -5810 A1/B1 = 107.56% The change is moving from B to A. That means I should have a negative change. How can I show that? Thanks, Brett |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to show negative percentage?
On 8 Jan 2006 18:19:12 -0800, "brett" wrote:
If I divide two negative numeric cells and put the result into a percentage cell, it will positive, even if the change is negative. For example: A B 1 -6249 -5810 A1/B1 = 107.56% The change is moving from B to A. That means I should have a negative change. How can I show that? Thanks, Brett You could always multiply your answer by -1. However, a "negative" percentage is not generally a meaningful concept. What real world problem are you trying to solve? --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to show negative percentage?
"brett" wrote:
If I divide two negative numeric cells and put the result into a percentage cell, it will positive, even if the change is negative. For example: A B 1 -6249 -5810 A1/B1 = 107.56% The change is moving from B to A. That means I should have a negative change. What makes you think so? -5810*107.56% is -6249, the correct answer. If you used -107.56% arbitrarily, you would get +6249, which is wrong. How can I show that? Since your choice of negative "change" (factor) seems arbitrary, I don't know what you would want in all cases -- for example, B is 2 and A is -4, and B is -2 and A is 4. Note that when B is 4 and A is 2, the "change" (factor) should not be negative. Your request makes more sense to me when we are talking about actual change, not growth factor. Whenever A is less than B, we might reasonably want to express the percentage change as negative. But we must use that notion of change carefully. For example: To compute percentage change (C1): =IF(B1=0, A1, SIGN(A1-B1)*ABS((A1-B1)/B1)) To apply (use) percentage change to B1 (D1), which should equal A1: =IF(B1=0, C1, B1+SIGN(C1)*ABS(C1*B1)) Examples (B1=before, A1=after): A1 B1 C1 D1 (should = A1) 2 4 -50% 2 4 2 100% 4 -2 -4 50% -2 -4 -2 -100% -4 2 -4 150% 2 -4 2 -300% -4 Some people will quibble with my choice when B1=0. It is arbitrary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show negative in cell? | Excel Worksheet Functions | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Show both value and percentage on Waterfall Chart | Charts and Charting in Excel | |||
How do I show negative number in brackets using excel? | Excel Worksheet Functions | |||
with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) |