# How to show negative in cell?

1. ## How to show negative in cell?

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  Register To Reply

2. ## Re: How to show negative in cell?

Just multiply it by -1. =(A1/B1)*-1

Gilles
"Brett Romero" <no@spam.net> wrote in message
news:OqyrZYAFGHA.344@TK2MSFTNGP11.phx.gbl...
> 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
>  Register To Reply

3. ## Re: How to show negative in cell?

"Brett Romero" <no@spam.net> 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?

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.  Register To Reply

4. ## Re: How to show negative in cell?

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:uAzshvBFGHA.1124@TK2MSFTNGP10.phx.gbl...
> "Brett Romero" <no@spam.net> 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?

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

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  Register To Reply

5. ## Re: How to show negative in cell?

"Brett Romero" <no@spam.net> wrote...
....
>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.

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.  Register To Reply

6. ## RE: How to show negative in cell?

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

[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?

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.  Register To Reply

7. ## Re: How to show negative in cell?

That means if the following are used:

A B
1 6249 5810

They will come out negative rather than the positive they should be.  Register To Reply

8. ## Re: How to show negative in cell?

Brett Romero 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

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?

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.  Register To Reply