MacMost Now 928: Percentages In Numbers

Calculating percentages in Numbers is just a matter of dividing two numbers. But you can use cell formatting to display the result as a percentage instead of a decimal fraction. You can also use functions to calculate percentages from lists of numbers or lists of items.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi this is Gary with MacMost Now. On today's episode let's take a look at percentages in Numbers.
I get a lot of questions about doing percentages in Numbers. Now percentages aren't anything special that you do in spreadsheets. Percentages are basically just simple division. You take a smaller number, divide it into a larger number and you get a fractional result. For instance, if you had five people out of a group of twenty, you would divide five by twenty and get .25 which is 25%. It is pretty simple math but there are some special things in spreadsheets that can help you, at least, display percentages.
So let's look at a simple spreadsheet and take that example. Say we have five as the group and twenty as the total so we want to calculate the percentage of five out of twenty. 
We would do that by simply using a formula starting with the equals = sign here and then clicking on this cell divided by this cell. We see the answer there 0.25 which is the fraction that represents five over twenty. It is also the percent except percents are usually represented with a decimal point moved over two places. So instead of 0.25 you would simple say 25%. 
We can do that by multiplying the number by 100 but it is actually easier with the spreadsheet to leave it in its true format, as a fraction, and then use a formatter like this percentage up here to show it as a percent. That will actually move the decimal point over two places and put the percent symbol after the number.
Now we can further customize this by bringing up the Inspector. So let's go and bring up the Inspector here and we will look at Formats here and then under Cell Format that is already set to percentage, that is the percentage there, we can change the number of decimal places. We may just want to see without any decimals at all like that. So there is a few other things you can do but that is the main thing and you get 25% there.
If this group would change the formula would update automatically. Seven out of twenty would be 35% and fourteen would be 70%. Now this is a simple example. I am just dividing two numbers.
So let's remove these and create something a little more complex. I'm going to add a Footer row here where I can do the total. Let me shrink this Table a bit and make it easier. Say I'm going to enter in a bunch of different numbers. What I want to do here is create the total at the bottom. There. So now I've got something you might typically do in a spreadsheet. You've added up a bunch of numbers and there is the total. If I add more it will increase the total. Here I can automatically update it like that.
Say now I wanted to get what percentage each of these groups was. So I can do equals = and do that cell right there divided by this cell and I get that fraction there. I can change the cell format there to percentage. Let's have zero decimals and I can see that is 15%.
Now what if I wanted to do that for each one of these. Of course that would be the idea. So I can copy this formula and paste it in here and what I'm going to get is that it is not going to work because if I look at that formula it has moved these two cells down instead of this one here being B2 divided by B7 now it is B3 divided by B8. There is no B8 because it goes all the way down to seven and stops.
So I want to modify the formula. I always want it to be the total here. I can do that in one of many ways. One is I can change this here, instead of being B7 I can do sum of B and that will work and this is kind of independent of this row here at the bottom. So you don't even need this row anymore. It is kind of independent of this.
Now if I copy this and pasted it I would get the percentage of each one right there.
Another way to do this would be to change this formula so I do actually use the total at the bottom like that but I change it to be an absolute. So I can do the absolute row, the important thing, so with the dollar sign $ there after 7 means 7 isn't going to change. So if I were to copy this and paste it here you can see it is B3 divided by B7, B4 divided by B7, B5 divided by B7, etc. I get the same result.
Either way I get a percentage here of what each one is. Six represents 15% of 41, 4 represents 10% of 41, 16 represents 39% of 41, etc. So it is another useful way to use percentages in a spreadsheet.
Here is another example that I have set up. I have here a Table that shows a list of items and I want to see how often items appear. I have apples, oranges, and bananas in this list. I want see what percent of the time apples appear. Now this is a short list but imagine if this was hundreds of thousands of items long.
So I have a second Table here where I have set up the first column to be the equivalent apple, orange, banana exactly as these are. I want to see how many times does apple appear here and then figure out the percentage.
I'm going to do a formula and the formula that I am going to use, I'm going to use a function called CountIf. So always look through the formula browser here and you can see all sorts of cool functions that you can use for different situations. It is good to just browse that especially if you use spreadsheets a lot. 
So I'm going to CountIf and I'm going to put the range in here so it is going to go to Table 1 and it is going to say from A1 to A17 and then I'm going to give it the value. I could put "apple" but instead since I have the word apple over here already I'm just going to click there and it will put A1 in, the equivalent to putting "apple" but it will be more useful later on. I see that 8 appears. Eight times that apple appears on this list. 
Now if wanted to further copy and paste this into orange and banana the first thing I want to do is make sure that the column and row here for the beginning and end of this is all absolute. So I'm going to change it like that so that it is absolute row and absolute column so as I copy and paste it this remains steady even though the second one A1 changes to A2 and A3.
Another way I can do this is I can simply use the entire set there. So I can say Table 1 A and that will work as well.
So let's do that one. I am going to copy this and paste it here and I can see now it is still Table 1, Column A and A2 instead of A1 which is orange. I'm going to paste it here and there I've got banana and there is three of them.
So now what I want is percentage. I want to know what percent of these are apples. So it is going to be eight divided by the total number here. So looking through the functions database I can see that there is a way to tell how many items are in this column. That is to use the CountA function. 
If you would look through it you would think Count but you would read the definition of it and say no it is CountA that I want. CountA, I would just put in Table 1A in there by clicking on the column heading. Now it is going to be basically the number of times apple appears in that column divided by the total number of items in that column. You can see I get .47. I know that is going to be 47%. Let me then convert that to a percentage here. Go to the formatting here and I don't want to get rid of the decimal places. Here we go. 47%. Great.
Now I can copy that and paste it here and orange is 35% and banana is 18%
So here are three ways to use percentages in spreadsheets. Now if you are looking to use percentages chances are your situation is different. There is nearly an infinite number of ways you can use percentages in spreadsheets so hopefully this gives you the basics and the understanding of what percentages are and how to use them in spreadsheets so you can apply them to your needs.
I hope you found this useful. Until next time this is Gary with MacMost Now.

Comments: 2 Responses to “MacMost Now 928: Percentages In Numbers”

nick

11 years ago

Gary:
sorry to hijack this opportunity for asking my question, but it's something about Numbers that's always bugged me. I've always used Excel so when I started using Numbers I never understood why the first row and column are highlighted in grey. Any functional reason for this?
thanks
nick

Gary Rosenzweig

Yes. See http://macmost.com/numbers-headers-and-footers.html.

Comments Closed.

MacMost Now 928: Percentages In Numbers

Comments: 2 Responses to “MacMost Now 928: Percentages In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information