Formula

[Chris]

I'm trying to calculate the % variance for two negative
numbers. For example, my budget is a negative number and
my actual is a negative number. normally I would divide
the two together and minus by 1 to get the %. Does not
seem to work with two negative numbers. Any suggestions?
Thanks

 

[Lee]

Percentage change for negative number is not meaningful
and hence undefined.

For example: if the data increase from -5 to -2, the
change is positive 3. When 3 is divided by the base (-5),
we get -60%, which is a contradiction to the increment of
data.

 

[Lee]

The problem with using ABSOLUTE (ABS) for the base:

If the data increases by 3, from a base of (-5) and if ABS
is used, the % increase is 60%.

Another set of data also increases by 3, but from a base
of (-6). Again, if ABS is used the % increase is 50%.

But (-6) is a smaller base than (-5), intuitively, we
would expect, the % increase should be higher than that of
(-5).

 

[Bernard Liengme]

I put these values in A1 and B1 and in C1 =B1/A1-1 and back came -2.49%. By
private email, I am sending you a file.

What value do you get?

Not sure where Lee gets the idea that percentages for negative values are
"meaningless". Wonder if he is thinking about Logs.

Bernard

 

[Lee]

Only when the base (i.e. denominator) is Negative.

 

[Bernard Liengme]

Why?
Bernard

Only when the base (i.e. denominator) is Negative.

 

[davers]

Not quite understanding what is not working....I have two negative
dollar values, in Excel, it looks like this:

in cell A1 $(51,673.00)
in cell A2 $(50,384.00)

formula in cell B1 =A1/A2-1

answer is 3% or for you sticklers for accuracy, 2.56%

I'm not quite understanding the problem...could you clarify a little
and I'll give it a shot...

Dave M.

 

[davers]

Or....aha!! I see in a previous reply that someone else is doing it the
opposite way,

in cell A1 $(51,673.00)
in cell A2 $(50,384.00)

formula in cell B1 =A2/A1-1

and the answer is -2.49%

is that what you meant?

Dave M.