How to show negative in cell?

  • Thread starter Thread starter Brett Romero
  • Start date Start date
B

Brett Romero

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
 
That means if the following are used:

A B
1 6249 5810

They will come out negative rather than the positive they should be.
 
Brett Romero said:
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?
Click to expand...

If you want to show this as a negative, no one will stop you, but it's
*correctly* appearing as a positive. Percentages > 100% imply increase in an
absolute sense, while those > 0% but < 100% imply decrease (and < 0% means
sign change, in which case the absolute value has no interpretive value). If
you start off with a negative value and end with a larger negative value,
you have *INCREASED* the negative value, not decreased it.

Percentages are multiplicative concepts, not additive ones (this is a common
misunderstanding), so additive change should be considered irrelevant.

If you want to register negative change, use addition: =A1-B1. If you want
to measure percentage (relative) change, then you're faced with the
ambiguity of ratios: the ratio has problematic interpretive value unless you
know the sign of the initial value. In most financial publications, ratios
with either or both terms negative are often shows as NMF - not meaningful.
 
Harlan Grove said:
If you want to show this as a negative, no one will stop you, but it's
*correctly* appearing as a positive. Percentages > 100% imply increase in
an absolute sense, while those > 0% but < 100% imply decrease (and < 0%
means sign change, in which case the absolute value has no interpretive
value). If you start off with a negative value and end with a larger
negative value, you have *INCREASED* the negative value, not decreased it.

Percentages are multiplicative concepts, not additive ones (this is a
common misunderstanding), so additive change should be considered
irrelevant.

If you want to register negative change, use addition: =A1-B1. If you want
to measure percentage (relative) change, then you're faced with the
ambiguity of ratios: the ratio has problematic interpretive value unless
you know the sign of the initial value. In most financial publications,
ratios with either or both terms negative are often shows as NMF - not
meaningful.
Click to expand...
If both numbers are negative and the change is increasing in a negative
direction (as above), how can I flag the resulting cell as red? If the A1
had been 5000, the resulting cell would remain black.

Thanks,
Brett
 
Brett Romero said:
If both numbers are negative and the change is increasing in a negative
direction (as above), how can I flag the resulting cell as red? If the A1
had been 5000, the resulting cell would remain black.
Click to expand...

You can't without referring to the denominator cell itself. If that's OK,
then base conditional formatting (Format > Conditional Formatting) on the
sign of the denominator.

My point was that ratios are inherrently ambiguous because +/+ is different
than -/-, as is +/- vs -/+. There's no way to make sense out of ratios
(percentages) without knowing the sign of the starting value.
 
Brett said:
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
Click to expand...

Due to a Google error, reposting my response to your
Jan 8 posting ....

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?
Click to expand...

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.
 
Brett Romero said:
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.
Click to expand...

[3rd reposting to work around Google problems.]

Repeating my response to your Jan 8 posting ....

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?
Click to expand...

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.
 
Back
Top