Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
I am trying to create a formula that is probably simple for others but not
me. I am trying to find out how to create a formula that will help me finda drop in a percentage of a number or an increase of a percentage. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
A percentage increase/decrease is just the fractional change multiplied by
100 Let A1 be sales in December and B1 be saes in January The change is =B1-A1 (we normally subtract old from new) So the fractional change is =(B1-A1)/A1 --- the change over the original In school you would multiply this by 100 but in Excel you just format with the % format tool If B1 is higher than A1 we have a positive value indicating an increase since Dec If B1 is lower than A1 we have a negative value indicating a decrease since Dec Note: we need parentheses in =(B1-A1)/A1 to make Excel do the subtraction before the addition best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mary bono" <mary wrote in message ... I am trying to create a formula that is probably simple for others but not me. I am trying to find out how to create a formula that will help me finda drop in a percentage of a number or an increase of a percentage. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
On Jan 24, 10:39*am, mary bono <mary
wrote: I am trying to find out how to create a formula that will help me finda drop *in a percentage of a number or an increase of a percentage. If A1 is an old value and B1 is a new value, typically you would do: =(B1-A1)/A1 But if A1 and B1 might have opposite signs (one negative, the other positive), you would do: =(B1-A1)/abs(A1) Note that that works when the signs are the same as well (both negative or both positive). In both case, the old value (A1) cannot be zero. There is no "right" answer when the old value is 0. But one approach might be: =if(A1=0, sign(B1), (B1-A1)/abs(A1)) That returns 100% or -100% for any change when the old value is zero. In all cases, format the cell as Percentage with an appropriate number of decimal places. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
PS....
On Jan 24, 11:52 am, I wrote: If A1 is an old value and B1 is a new value, typically you would do: =(B1-A1)/A1 More generally, really that is simply the percentage increase or decrease from one number (A1) to another number (B1). It is entirely up to you whether A1 is the old number and B1 is the new number, or A1 is the new number and B1 is the old number. ----- original posting ----- On Jan 24, 11:52*am, joeu2004 wrote: On Jan 24, 10:39*am, mary bono <mary wrote: I am trying to find out how to create a formula that will help me finda drop *in a percentage of a number or an increase of a percentage. If A1 is an old value and B1 is a new value, typically you would do: =(B1-A1)/A1 But if A1 and B1 might have opposite signs (one negative, the other positive), you would do: =(B1-A1)/abs(A1) Note that that works when the signs are the same as well (both negative or both positive). In both case, the old value (A1) cannot be zero. *There is no "right" answer when the old value is 0. *But one approach might be: =if(A1=0, sign(B1), (B1-A1)/abs(A1)) That returns 100% or -100% for any change when the old value is zero. In all cases, format the cell as Percentage with an appropriate number of decimal places. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
================================================== ===========
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 werent 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 % Q: In A1 I have he end price. In B1 I have the VAT percentage, entered like 15% What is the formula to find the base price, excluding VAT? A: =A1/(1+A2) ================================================== =========== -- Kind regards, Niek Otten Microsoft MVP - Excel "mary bono" <mary wrote in message ... I am trying to create a formula that is probably simple for others but not me. I am trying to find out how to create a formula that will help me finda drop in a percentage of a number or an increase of a percentage. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
finding percentage formulas
PS....
On Jan 24, 11:52 am, I wrote: If A1 is an old value and B1 is a new value, typically you would do: =(B1-A1)/A1 Technically, that gives the wrong answer when both values are negative. But arguably, it depends on your point of view. Consider profits that go from -100 to -50. Technically, that is a 50% gain, which the formula =(B1-A1)/abs(A1) correctly yields. Thus, that formula yields the correct result with all combinations of positive and negative values. But some people like to say that losses "decreased" by 50%. And =(B1- A1)/A1 does yield that result (-50%). IMHO, that terminology breaks down when the values differ in sign. Consider two cases: (a) profits go from -50 to 50; and (b) profits go from 50 to -50. In both cases, (B1-A1)/A1 yields -200%(!). In contrast, (B1-A1)/abs(A1) yields 200% in the first case, and -200% in the second case. IMHO, that makes better sense. ----- original posting ----- On Jan 24, 12:07*pm, joeu2004 wrote: PS.... On Jan 24, 11:52 am, I wrote: If A1 is an old value and B1 is a new value, typically you would do: * =(B1-A1)/A1 More generally, really that is simply the percentage increase or decrease from one number (A1) to another number (B1). *It is entirely up to you whether A1 is the old number and B1 is the new number, or A1 is the new number and B1 is the old number. ----- original posting ----- On Jan 24, 11:52*am, joeu2004 wrote: On Jan 24, 10:39*am, mary bono <mary wrote: I am trying to find out how to create a formula that will help me finda drop *in a percentage of a number or an increase of a percentage. If A1 is an old value and B1 is a new value, typically you would do: =(B1-A1)/A1 But if A1 and B1 might have opposite signs (one negative, the other positive), you would do: =(B1-A1)/abs(A1) Note that that works when the signs are the same as well (both negative or both positive). In both case, the old value (A1) cannot be zero. *There is no "right" answer when the old value is 0. *But one approach might be: =if(A1=0, sign(B1), (B1-A1)/abs(A1)) That returns 100% or -100% for any change when the old value is zero. In all cases, format the cell as Percentage with an appropriate number of decimal places. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the percentage | Excel Worksheet Functions | |||
formulas, percentage | Excel Discussion (Misc queries) | |||
Finding Percentage | Excel Worksheet Functions | |||
Formulas for percentage | Excel Discussion (Misc queries) | |||
Finding the percentage difference of two or more numbers | Excel Worksheet Functions |