I know how to calculate % change, but have run into a snag when the preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%, but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

=(B1-A1)/ABS(A1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Patrick" wrote in message
...
I know how to calculate % change, but have run into a snag when the
preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No
problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%,
but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I
want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

That's exactly what I was looking for, thank-you!

(I figured it was something rather simple, but just couldn't hit the nail on
the head.)

"Bob Phillips" wrote:

=(B1-A1)/ABS(A1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Patrick" wrote in message
...
I know how to calculate % change, but have run into a snag when the
preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No
problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%,
but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I
want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

Patrick wrote:
I know how to calculate % change, but have run into a snag when the preceding
period had a negative value.
[....]
Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

=if( A2=0, "", (B2-A2) / abs(A2) )

Since you are dealing with mixed sign, it seems prudent to do
__something__ with A2=0.

On Mon, 16 Oct 2006 15:44:01 -0700, Patrick
wrote:

I know how to calculate % change, but have run into a snag when the preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%, but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

The problem, Patrick, is that the percent change is not particularly
meaningful, in my opinion, when the underlying value is negative (or zero, for
that matter). The absolute value is meaningful, and the change from loss to
profit is meaningful, but percent change is not.

For example, given the same positive Current value, and using, let us say,
Bob's formula, you will note that the profit becomes greater as the Prev
approaches zero. This implies that the profit has gone up by an increasing
percent, when the amount of profit has decreased, and the baseline has
increased:

Prev Curr Bob's
-5 10 300%
-1 10 1100%

In financial reporting, in these circumstances, a percent change is not
reported by the few sources I've used.

Reuters only reports percent change in earnings if both periods have positive
earnings. Otherwise it reports (NM) (not meaningful).

The fact of P(rofit) or L(oss) may be reported.

From WSJ.com HELP:Digest of Earnings
http://online.wsj.com/public/resourc...s/doe-help.htm

"Net Income percent change is the change from the same period from a year ago.
Percent change is not provided if either the latest period or the year-ago
period contains a net loss. On the digest page, if a company posts a profit in
the latest period against a loss in the year-ago period, the percent change is
represented as a "P". Similarly, if a company posts a loss in the latest period
against a profit in the year-ago period, the percent change is represented as a
"L"."

For what it's worth, I've been told by a math PhD that percent change with
values of opposite signs is not defined, but I've never been able to find that
documented anyplace.
--ron

Interesting. I just hope I can remember this if/when it next comes up.
--
Kevin Vaughn


"Ron Rosenfeld" wrote:

On Mon, 16 Oct 2006 15:44:01 -0700, Patrick
wrote:

I know how to calculate % change, but have run into a snag when the preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%, but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

The problem, Patrick, is that the percent change is not particularly
meaningful, in my opinion, when the underlying value is negative (or zero, for
that matter). The absolute value is meaningful, and the change from loss to
profit is meaningful, but percent change is not.

For example, given the same positive Current value, and using, let us say,
Bob's formula, you will note that the profit becomes greater as the Prev
approaches zero. This implies that the profit has gone up by an increasing
percent, when the amount of profit has decreased, and the baseline has
increased:

Prev Curr Bob's
-5 10 300%
-1 10 1100%

In financial reporting, in these circumstances, a percent change is not
reported by the few sources I've used.

Reuters only reports percent change in earnings if both periods have positive
earnings. Otherwise it reports (NM) (not meaningful).

The fact of P(rofit) or L(oss) may be reported.

From WSJ.com HELP:Digest of Earnings
http://online.wsj.com/public/resourc...s/doe-help.htm

"Net Income percent change is the change from the same period from a year ago.
Percent change is not provided if either the latest period or the year-ago
period contains a net loss. On the digest page, if a company posts a profit in
the latest period against a loss in the year-ago period, the percent change is
represented as a "P". Similarly, if a company posts a loss in the latest period
against a profit in the year-ago period, the percent change is represented as a
"L"."

For what it's worth, I've been told by a math PhD that percent change with
values of opposite signs is not defined, but I've never been able to find that
documented anyplace.
--ron

On Tue, 17 Oct 2006 11:24:01 -0700, Kevin Vaughn
wrote:

Interesting. I just hope I can remember this if/when it next comes up.

Me too! :-)) But Google is our friend!
--ron

Ron,

That's an excellent point, and one that I also am considering...
It's not for financial data, but rather quantity of products sold.

Nevertheless, your comment is well appreciated and does tend to make me
re-consider the whole approach.

"Ron Rosenfeld" wrote:

On Mon, 16 Oct 2006 15:44:01 -0700, Patrick
wrote:

I know how to calculate % change, but have run into a snag when the preceding
period had a negative value.

If last-period I had 5 and this period I have 10, I want 100%. (No problem.)
Likewise, if last-period I had 10 and this period I have 5, I want -50%
(again, no problem).

If however last-period I had -5 and this period I had 10, I _want_ 300%, but
am getting -300%.
Likewise, if last-period I had -5 and this period I have -10, I want -100%,
but am getting 100%.

Here is a table:
Prev Current Have Want Good?
5 10 100% 100% Y
5 -10 -300% -300% Y
10 5 -50% -50% Y
10 -5 -150% -150% Y
-10 5 -150% 150% N
-10 -5 -50% 50% N
-5 10 -300% 300% N
-5 -10 100% -100% N

I tried using an embedded formula to do this, however ran into fact I can
only nest 7-layers deep.

I'm thinking I'm just simply 'missing' something; something really simple.

If anyone can help, that'd be great.

Thanks

The problem, Patrick, is that the percent change is not particularly
meaningful, in my opinion, when the underlying value is negative (or zero, for
that matter). The absolute value is meaningful, and the change from loss to
profit is meaningful, but percent change is not.

For example, given the same positive Current value, and using, let us say,
Bob's formula, you will note that the profit becomes greater as the Prev
approaches zero. This implies that the profit has gone up by an increasing
percent, when the amount of profit has decreased, and the baseline has
increased:

Prev Curr Bob's
-5 10 300%
-1 10 1100%

In financial reporting, in these circumstances, a percent change is not
reported by the few sources I've used.

Reuters only reports percent change in earnings if both periods have positive
earnings. Otherwise it reports (NM) (not meaningful).

The fact of P(rofit) or L(oss) may be reported.

From WSJ.com HELP:Digest of Earnings
http://online.wsj.com/public/resourc...s/doe-help.htm

"Net Income percent change is the change from the same period from a year ago.
Percent change is not provided if either the latest period or the year-ago
period contains a net loss. On the digest page, if a company posts a profit in
the latest period against a loss in the year-ago period, the percent change is
represented as a "P". Similarly, if a company posts a loss in the latest period
against a profit in the year-ago period, the percent change is represented as a
"L"."

For what it's worth, I've been told by a math PhD that percent change with
values of opposite signs is not defined, but I've never been able to find that
documented anyplace.
--ron

On Tue, 17 Oct 2006 12:41:03 -0700, Patrick
wrote:

Ron,

That's an excellent point, and one that I also am considering...
It's not for financial data, but rather quantity of products sold.

Nevertheless, your comment is well appreciated and does tend to make me
re-consider the whole approach.


I would think so. Although I'm not sure how Quantity of Products sold could be
a negative number. Maybe it's the net of product purchased vs product sold;
but if that's the case, I would think the same analysis would apply.

--ron

On Tue, 17 Oct 2006 20:04:15 -0400, Ron Rosenfeld
wrote:

On Tue, 17 Oct 2006 12:41:03 -0700, Patrick
wrote:

Ron,

That's an excellent point, and one that I also am considering...
It's not for financial data, but rather quantity of products sold.

Nevertheless, your comment is well appreciated and does tend to make me
re-consider the whole approach.


I would think so. Although I'm not sure how Quantity of Products sold could be
a negative number. Maybe it's the net of product purchased vs product sold;
but if that's the case, I would think the same analysis would apply.

--ron

The above doesn't read right and might be offensive.

What I meant to write, in addition to not being sure how negative values would
apply to quantity of products sold, is that I would think that similar
principles would apply if one or both of the values were negative, or zero.


--ron