When I calculate the percent increase from $-50 (year 1) to $-1 (year
2) using the formula (((-1/-50)-1)*100), Excel returns -98%.
Technically I know this is correct but when I plot this amount on a
chart it shows a decrease between the two years rather than an
increase. How do I fix this without having to adjust each cell in
Excel manually?

---

Request for Question Clarification by joey-ga on 21 Jan 2005 11:38 PST

Hi there, what is the ultimate goal of this? Are you going to have
many years of data, only some of which are negative?

The reason I ask is that it's generally accepted that percentage
increase/decrease isn't particularly relevant when the numbers are
negative (i.e. from -50 to -1, you're trying to see the percentage of
-50 that it increases?  that doesn't really make sense
mathematically).

See this explanation for more information on it:
http://mathforum.org/library/drmath/view/55720.html

If you let me know what, specifically, you're trying to accomplish
with all the data in the long run, I may be able to suggest a better
means for comparing the data.

--Joey

---

Clarification of Question by jkaufman1234-ga on 21 Jan 2005 15:39 PST

Re: Request for Question Clarification by joey-ga

I am attempting to evaluate the effect new subscribers have on
Operating Income. To do this I am dividing the increase (from one
quarter to another) in Operating Income by the total number of
subscribers added during the quarter. If Operating Income declines (on
a quarter-by-quarter basis) but the number of subscribers added
increases, the Operating Income for each subscriber added will decline
from the previous quarter, hence the negative number. If Operating
Income declines again in the successive quarter (but not as much) the
Operating Income for each subscriber added will still be negative, but
not as much as in the previous quarter.

---

Request for Question Clarification by joey-ga on 22 Jan 2005 11:05 PST

If you haven't gotten a chance to look at my thoughts in the comments
section, you may want to take a glance at them now, because they show
how just using ABS() with a percentage change like you did may not
provide you any kind of useful comparison.

My suggestion is to do compare a percentage change in "Per-User
Income" with a percentage change in the number of subscribers.

So, you would set up your Excel spreadsheet something like this (the
numbers are all just fake, sample numbers I've created):

TOI = Total Operating Income
TSub = Total Subscribers
PUI = Per-User Income
%PUI = Per-User Income percentage change (quarter-over-quarter)
%Sub = Total Subscribers percentage change (quarter-over-quarter)
Correlation = How far apart are the %Changes in subscribers and income?

TOI       TSub      PUI       %PUI      %Sub      Correlation
-------------------------------------------------------------
100000    15000     6.67		
95000     14600     6.51      -2.40%    -2.67%     0.27%
93000     14100     6.60       1.37%    -3.42%     4.79%
92950     13900     6.69       1.38%    -1.42%     2.80%
93150     13950     6.68      -0.14%     0.36%    -0.50%
101150    14550     6.95       4.11%     4.30%    -0.19%
101100    14600     6.92      -0.39%     0.34%    -0.74%
103100    14750     6.99       0.94%     1.03%    -0.09%


As you can see, these calculations directly inform you how a change in
subcriber base affects your per-user income.  In this example, when
the number of subscribers increased by 4.3%, the per-user income
increased 4.11%.  When the number of subscribers decreased by 1.42%,
the per-user income increased by 1.38%.  The column on the right just
shows how close these two changes are.

If you want, you could add another column that compares the percentage
change in operating income from quarter-to-quarter, not just the
percentage change in per-user income.

If you think these kind of calculations serve your purpose more,
please let me know.  I'll work through each of the calculations with
you.

--Joey

( (-50 - (-1) ) / -50 )* 100

=ABS((((-1/-50)-1)*100))

or
    Column a            Column b 
1 =(((-1/-50)-1)*100)    =ABS(a1)
output:
1       -98                98

Thanks very much! The ABS function was what I needed!

The ABS() function will keep it from being negative, but it won't
provide you useful information whatsoever

Imagine the following year-to-year changes:

-50
-1
-2
-1
1
10

Using the absolute-value formula, it tells you:
-50 -> -1: 98%
-1 ->  -2: 100%
-2 ->  -1: 50%
-1 ->   1: 200%
1 ->   10: 900%

That shows the change from -50 to -1 as being LESS significant and
less good than from -1 to -2, when in fact, -1 to -2 is a loss. 
Similarly, from -50 to -1 is shown as less significant of an increase
than -2 to -1, which is absurd.

Sorry, rather, -50 to -1 is shown as only about twice as good of an
increase as -2 to -1, which is absurd.

But as you can see, this isn't terribly useful.  There's a better way
to compare this data, but I have to run now, so if someone else is
around and can suggest the better ways, that's cool.  If not, when I
get time later, I'll put some suggestions together.

--Joey