Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calclulating percentage difference
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calclulating percentage difference
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: ToolsOptions, 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 " wrote in message ... |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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calclulating percentage difference
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 " wrote: 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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calclulating percentage difference
On 2 Jun, 20:39, pdberger wrote:
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 " wrote: 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?- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage of difference formula | Excel Discussion (Misc queries) | |||
formula assistance - percentage difference | Excel Worksheet Functions | |||
Finding the percentage difference of two or more numbers | Excel Worksheet Functions | |||
Percentage difference calc that knows the largest figure | Excel Discussion (Misc queries) | |||
percentage difference | New Users to Excel |