At some point in Excel you're going to need to work with percentages, but there's a bit
of confusion around this issue because you can FORMAT as a percentage and you can CALCULATE
as a percentage and they're actually two different things.
So in this video I'm going to explain all.
Stay tuned.
The first thing to understand about percentages, is there a percentage format and a percentage
formula.
So something like 50 percent, it's just a percentage format.
It's half.
The decimal equivalent will be zero point five.
To check that, you just click the cell here, click the comma here, and it converts to point
five.
So think about a scale that goes from zero to one where zero is nothing and one is everything.
In percentage terms that zero percent through to a hundred percent.
Think about halfway on the scale, you know, you're talking zero point five, half.
Fifty percent, half.
Both of those are halfway along the scale.
Three quarters long, you get zero point seven five for the number and seventy five percent
for the percentage.
So that's the relationship between percentages and numbers.
The zero point something number is what converts to a percentage.
And so you know, if you're taking, say, point seven five, convert that to a percentage,
you get seventy five percent.
Now let's take a bunch of numbers here.
A lot of people make the mistake, especially when they're beginners, of selecting the whole
range of numbers and simply converting to percentage format.
As you can see there's huge numbers here because if you think back to say zero point seven
five here, you've multiplied that by a hundred to get seventy five and then put the percentage
on the end.
You can't just do that here.
What you need to do instead is to do a calculation.
So let's go back to the numbers there.
I'll just press CTRL Z to undo my last step.
The first thing I'm going to do is to get a total.
So, just using AutoSum here.
There's the total of all those numbers.
I'm also just going to name this cell, so if I click that, go to your name box here.
Let's give it a name called 'Total'.
Then I'm going to use that in my formula in just a second.
So here, to work out the percentage of each of these numbers, you simply divide, so you
start with '=', take the number here and divide by the total.
That will give you a zero point something number which you can then convert to a percentage.
So if I just take this and auto-fill it down, there's all my zero point something numbers
for each of these whole numbers.
And if I convert those to a percentage, there's my percentages.
You may or may not need to show a few more decimal places just to show the accuracy there.
If I was to total these percentages up, again using AutoSum, you get a hundred percent.
Now let's take another example.
So let's say you're buying a house, okay, the house price is, say, four hundred thousand
dollars.
And let's say you're talking about a fixed interest rate here.
So your interest rate might be, say, five point five percent.
Now to calculate five point five percent of four hundred thousand, to work out what interest
you're paying, your formula will be '=' one multiplied by the other.
Okay, so twenty two thousand is the interest component of that figure.
Okay, I'm going to finish with two examples, business examples.
Here in Australia we have something called the GST, it's like a General Services Tax,
a Sales Tax, which gets added onto a base price, and that goes to the government.
So it's got a current value of ten percent.
So let's just write in 'Base Price', 'GST', 'Total'.
Let's say the base price for your computer is, say, a thousand dollars.
The GST to calculate that is ten percent of a thousand dollars.
As we saw before, it's one times the other.
So let's take your base price, multiply it by ten percent.
The total then is just these two added together.
I'm using AutoSum to get it done quickly.
And that's your total.
Now, quite often you do want the separate columns so that process is fine and obviously
then you can copy this formula down for as many prices as you have.
But if you want to do it in one step rather than two, and this might look a little bit
funny the first time you see it, but you do it like this.
You say '=' the thousand dollars times the ten percent.
You then add on the base price.
So you're using A20 in this case twice, the first use of it is to work at the ten percent
of the base price and then add on the actual base price.
That gives you eleven hundred but it's all done in one cell.
So you don't actually need this middle one if that's the way you want to go.
If you want to reverse the process, so let's say you've got eleven hundred dollars as your
total price and you want to strip off the GST to get back to your base price, the way
to think about this, the total is in effect a hundred and ten percent of the original
price.
So what I'm going to do is this, I'm going to say '=' the total price.
I'm going to divide it by - in brackets I'll say a hundred percent plus the ten percent.
A hundred percent plus ten percent is a hundred and ten percent.
So you do that and you get to base price.
That's it for this one.
Cheers guys.
See you for the next one.