finding percentage formulas

[mary bono]

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.

 

[Bernard Liengme]

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

 

[joeu2004]

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.

 

[joeu2004]

PS....

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 -----

 

[Niek Otten]

=============================================================
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 %

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)

=============================================================

 

[joeu2004]

PS....

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 -----