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

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

Gilles
"Brett Romero" wrote in message
...
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" 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.

"Harlan Grove" wrote in message
...
"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?

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

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

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.

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