Calcuating % of increase - negative amts

[Mark]

I am trying to calculate the % of increase in revenue
from one year to the next.

If Cell A2 (2003) is $100.00
If Cell A3 (2004)is $500.00
% of Increase in Cell A4 is 400% using the formula
=(A3-A2)/A2

This works great but my problem is that in SOME cases,
the previous year's revenue is in negative numbers. In
that case, it calculates the % of increase correctly but
it makes it a negative % instead of a positive one.


If Cell A2 (2003) is ($100.00)
If Cell A3 (2004)is $500.00
% of Increase in Cell A4 is -600% using the formula
=(A3-A2)/A2

This should be a positive 600%. Is there a way I can get
the %'s to be displayed correctly?

Thanks!

Mark

 

[Frank Kabel]

Hi Mark
if your previous year is a negative value and you have now a positive
value you CAN'T calculate a percentage for this increase. Even an
increase from
$0.00
to
$100.00
in one year can't be calculated (infinite result)

 

[hglamy]

Phantastic Frank, simply great !

Finest regards,

H.G. Lamy

 

[Ron Rosenfeld]

This works great but my problem is that in SOME cases,
the previous year's revenue is in negative numbers. In
that case, it calculates the % of increase correctly but
it makes it a negative % instead of a positive one.


If Cell A2 (2003) is ($100.00)
If Cell A3 (2004)is $500.00
% of Increase in Cell A4 is -600% using the formula
=(A3-A2)/A2

This should be a positive 600%. Is there a way I can get
the %'s to be displayed correctly?

Mark,

What you are trying to do is mathematically undefined and computationally
meaningless.

In stock analysis programs, the result returned is usually NA.


--ron

 

[Mark]

Thanks for the feedback. Of course, my next question
would then be... why wouldn't Excel return the result as
#N/A?

 

[Ron Rosenfeld]

Thanks for the feedback. Of course, my next question
would then be... why wouldn't Excel return the result as
#N/A?

I think you'd have to ask the designers for a definitive answer.

My opinion is that although the concept of percentage increase from a negative
number is meaningless, Excel doesn't know what you are thinking when you plug
in the numbers for it to manipulate, so it can't tell you that, under the
circumstances from which you derived those numbers, the concept is not
meaningful. Excel is only manipulating numbers. GIGO.

However, if you are writing a stock analysis program, you know the origin of
your numbers, so it is easy to have your program come up with a NA (or NM or
NMR as some do).

--ron