percent change

S

stephenm

I cannot find a formula to calculate the percentage change between two
numbers. I can write a formula which gives the answer, but I'm curious why
Excel doesn't have this formula in the drop down lists. Am I missing it?
 
J

JE McGimpsey

It's so easily done with a formula:

A1: <starting number>
A2: <ending number>

A3: =(A2-A1)/A1

(which gives percent change when you format A3 as a percentage), that I
suspect the designers didn't feel a separate function, with its
overhead, was necessary.

Note: One could also be a bit more efficient:

A3: =A2/A1 - 1


again formatting A3 as a percentage.
 
J

JBoulton

JE,

Do you know of a formula that will give the correct %change regardless of
the sign of the two values? I've always used nested IFs but I think there's
likely a better way.

If the starting number is negative (-10) and the ending number is positive
(5) those calcs produce a negative %change eventhough the change is obviously
positive.
 
J

JE McGimpsey

even though the change is obviously positive.

Is it?

If I had a loss of $10 per share last year and a profit of $5 this year,
how much did my loss increase?

What should the answer be?

Should it be =-(5- (-10))/-10 = 150%?

What if I had a loss of $5 instead. One would normally say that my loss
decreased by 50% (i.e., -50%). If I broke even, my loss decreased by
100% (i.e, -100%). Where then does the sign change come from if I then
have a profit?

Normally percent change requires that both values have the same sign in
order to be meaningful.
 
J

JBoulton

Is it?

Yes. You've had a positive change if you have more this year than last
year. And that's true whether last year was positive or negative its self.
 
J

JE McGimpsey

Is it?
Click to expand...

Yes. You've had a positive change if you have more this year than last
year. And that's true whether last year was positive or negative its self.[/QUOTE]

Perhaps in context it's positive in the "feel good" sense (but maybe not
if it were, say a deficit), but not in terms of percent change.

For example. Lets say you have

Start: -5
Finish: -10
Percent change: =(-10 - -5)/-5 = +100%

which makes sense - a +100% change is a doubling. In this case the loss
has doubled.

So, given

Start: -5
Finish: +5

What POSITIVE percentage change could you expect to fulfill this
condition?

Read any SEC annual report , or the Wall Street Journal earnings
reports, where a loss has been followed by a profit, or vice versa. The
Percent Change column will read "NMF" for "No Meaningful Figure".

You may or may not find this helpful:

http://mathforum.org/library/drmath/view/62206.html
 
Top