馃攳
Calculate Percentages the Right Way in Excel (% Change & Amount after % increase) - YouTube
Channel: Leila Gharani
[0]
Today, let's take a look
[1]
at calculating percentages in Excel.
[3]
We're gonna take a look
at the formula you need
[6]
to calculate percentage change
[8]
and we're gonna do it in a way
[9]
that's gonna help you remember it.
[12]
If you're working as a financial
analyst creating reports,
[16]
this is a formula you need to get right.
[18]
We're also gonna take a look
at how we can do calculations
[22]
that involve percentage
increase or decrease.
[25]
So for example, we have a price.
[28]
We want to increase price by 15%.
[31]
What's gonna be our end price?
[33]
Let's get to it.
[35]
First off, let's take a look
[36]
at calculating percentage change.
[39]
Assume your boss gives you an Excel file
[41]
that contains actual sales
data and budget sales.
[45]
Your task is to calculate
percentage change.
[48]
Let's take this to the board.
[50]
So you have actual and you have budget.
[54]
To calculate the difference,
[56]
you're gonna do actual minus
budget divided by budget.
[61]
An alternate way of writing this formula
[64]
is actual divided by budget
minus budget divided by budget,
[70]
which turns into actual
divided by budget minus one.
[75]
Now it's all clear, right?
[77]
So our formula is actual
divided by budget minus one.
[81]
Double click on the bottom right side
[83]
and send the formula down.
[84]
(light percussive music)
[89]
Now let's move on
[89]
to calculating percentage
increase or decrease.
[92]
Our starting point is in column A.
[95]
We want to increase the price
[97]
by the percentage we see in column B.
[99]
And if it's minus, we want to decrease it.
[102]
Let's take this to the board.
[103]
So we have our price.
[105]
To calculate the price
[107]
after we add the increase
or decrease to it,
[109]
we're gonna add this with price
[113]
multiplied by that percentage.
[115]
An alternate way of writing this
[117]
is to factor out the price,
[119]
so it's price times one
plus the percentage.
[126]
So that's another way
of writing the formula.
[128]
So in Excel, this would be starting price
[131]
multiplied by one plus percentage change,
[135]
close bracket, press enter.
[137]
So if my starting price was 100,
[139]
I increased price by 10%, I get 110.
[143]
If this was 5% instead, I get 105.
[148]
In Excel, there's one thing
you have to be aware of
[150]
and that's how you input your percentages.
[153]
One method to input percentages
is to add it while you type.
[158]
So if I come here and type in 20
[160]
followed by the percentage
sign and press enter,
[163]
the cell is automatically
formatted as a percentage.
[167]
Another option is to
input it as a decimal,
[170]
so 0.05 translates to 5%.
[175]
So if I was gonna pull this formula down,
[178]
we can see it adds up correctly as 5%.
[182]
Now I can change this to
percentage and I get 5%.
[186]
Now another thing you can do
[188]
is to format your cell as percentage first
[191]
before you input your full number.
[194]
But don't do it the other way around.
[196]
So for example, here,
if I was gonna input 30
[199]
and then I decide, well,
this should be a percentage
[201]
and I click to format
this as a percentage,
[204]
I get the wrong number.
[206]
So if this was already
formatted as a percentage,
[209]
I go in and input 30, then it works fine.
[213]
Okay, so let's just
pull this one down too.
[215]
So that's it.
[216]
Just remember, for percentage change,
[218]
it's new divided by old minus one
[221]
or end divided by start minus one.
[224]
For percentage increase,
we have the formula
[227]
your value multiplied by one
plus the percentage change.
[231]
And if it's a percentage decrease,
[234]
it's your value times one
minus the percentage change.
[237]
But if you input your
percentage as negative in Excel,
[241]
you can use the same formula.
[243]
Just drag it down.
[244]
So the next time you're asked to calculate
[246]
the monthly or yearly change,
you know how to do it.
[249]
If you like this video,
click that thumbs up.
[252]
And if you want to
improve your Excel skills,
[255]
consider subscribing to this challenge.
[257]
(mellow music)
Most Recent Videos:
You can go back to the homepage right here: Homepage