Calclulating percentage difference

[babypink2807]

I have the following columns

Apr-2006 Apr 2007
0.00 0.00
0.00 0.00
0.00 331.08
0.00 107.90
0.00 0.00
0.00 970.00
1,134.60 0.00
0.00 2,587.59
5,999.06 16,294.34

I want to work out what the differnece is between 2006 and 2007 by
percentage ie is it a decrease of 25% or increase of 25%, what is the
formula for it?

 

[Niek Otten]

My standard list:

=============================================================
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate with; just
multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at least use
percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes) the
value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will be the
number 12 or 12%. What happens in your case depends on a setting:
Tools>Options, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be careful
when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even better,
8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %
=============================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have the following columns
|
| Apr-2006 Apr 2007
| 0.00 0.00
| 0.00 0.00
| 0.00 331.08
| 0.00 107.90
| 0.00 0.00
| 0.00 970.00
| 1,134.60 0.00
| 0.00 2,587.59
| 5,999.06 16,294.34
|
| I want to work out what the differnece is between 2006 and 2007 by
| percentage ie is it a decrease of 25% or increase of 25%, what is the
| formula for it?

 

[pdberger]

bp --

Here's your basic formula:

=if(a2<>0,(b2-a2)/a2,"")

Then just format the cell for a percentage. The basic problem, though, is
that you have to decide what you want to report when the 2006 data point is
zero. For example, one pair shows you going from 0 to 331, and another shows
you going from 0 to 108. I could argue that both changes are 100%, in which
case the formula would be:

=if(a2<>0,(b2-a2)/a2,1)

but I could argue that's misleading, as a jump from 0 to 10 isn't the same
as a jump from 0 to 108, or 0 to 331, or 125 to 250. So you have a logic
decision to make, but whatever you decide will fit into the 'if false' part
of the =IF statement.

HTH

 

[babypink2807]

bp --

Here's your basic formula:

=if(a2<>0,(b2-a2)/a2,"")

Then just format the cell for a percentage.  The basic problem, though, is
that you have to decide what you want to report when the 2006 data point is
zero.  For example, one pair shows you going from 0 to 331, and another shows
you going from 0 to 108.  I could argue that both changes are 100%, in which
case the formula would be:

=if(a2<>0,(b2-a2)/a2,1)

but I could argue that's misleading, as a jump from 0 to 10 isn't the same
as a jump from 0 to 108, or 0 to 331, or 125 to 250.  So you have a logic
decision to make, but whatever you decide will fit into the 'if false' part
of the =IF statement.

HTH







- Show quoted text -

I know thats my stumbling block as £0.00 to £108.00 is actually a new
account, so do I include it as an increase having not done business
withthem inthe previous year? Is is strictly 100% increase? Not sure
never done this before, advice appreciated