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?

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.

In article ,
"stephenm" wrote:

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?

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


"JE McGimpsey" wrote:

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.

In article ,
"stephenm" wrote:

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?

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.



In article ,
"JBoulton" wrote:

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.

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


"JE McGimpsey" wrote:

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.



In article ,
"JBoulton" wrote:

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.

In article ,
"JBoulton" wrote:

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.

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