Lesson video

Good day to you all, and thank you for joining me, Mr. Gratton, for another maths lesson.

In today's lesson, we will be learning the mechanics behind how to construct pie charts using technology, specifically computer-based spreadsheet applications, such as Microsoft Excel and Google Sheets.

Here are some key words that will be very helpful for you to be familiar with when attempting this pie chart lesson.

Pause here if you want to remind yourself of the meaning of any of these words.

Right, before we start creating pie charts using spreadsheets, we need to become more familiar with the basic functions of a spreadsheet.

This includes how to use spreadsheets for basic maths, leading up to calculating angles in a pie chart using proportional reasoning.

There are several spreadsheet softwares and applications that are available, but in this lesson, we'll only be focusing on Google Sheets and Microsoft Excel.

All functions and facilities should be similar with all of these different applications if you choose to use them.

Here are some basic features of a spreadsheet that will come in very helpful in today's lesson.

Pause here to make a note of any of these features.

We will be using all sorts of facilities on these online spreadsheets to perform all sorts of impressive calculations.

But first, here are some of the basics.

So whenever you want to start doing any sort of mathematical calculation, whether that is addition, multiplication, or anything else, the symbol you must type in first is the equals symbol.

If you do not type in the equals symbol, the spreadsheet software will not know that you want to do a mathematical calculation.

After typing in the equals symbol, you can do any mathematical calculation.

In order to represent the four basic operations in a spreadsheet, you need to look at your keyboard and find these buttons to represent addition, subtraction, multiplication, and division.

Most of these are found on the top row of the keyboard and accessed by pressing the Shift key and then a number, but one exception to this is the divide symbol, which is a slash found on the bottom right of your keyboard.

If you have a number pad, they are all found on the number pad on the far right of your keyboard by the other set of numbers.

Please note, you cannot multiply two numbers together in a spreadsheet by using the letter X.

X is not used for any operation or any mathematical calculation in spreadsheets.

So here's the first example of how to use a spreadsheet to do a basic mathematical calculation.

I want to type the answer to this maths question in the cell E11.

Let's go through the steps.

Step one, click on the cell E11.

You can check that you have the correct cell by looking at this on the top left-hand corner of the spreadsheet.

E11 is the cell name, and that little section on the top left will always tell you the cell name of the cell that you are currently in.

Let's zoom in on the cell E11 a little bit more so we can see what we're gonna type in there a bit more clearly.

Going on to step number two.

Remember, pressing that equals symbol on your keyboard is always your first step.

It tells the spreadsheet, "Hey, can you start a mathematical calculation?" Okay, step number three, click on the cell with the first number that you want to include in your calculation.

In this case, I will click on the cell A11 because I want to start my calculation with 360.

For step four, this is more of a note.

Notice how your calculation shows the cell number A11, not the number 360.

This is correct.

When you start a mathematical calculation, when you click on a different cell, you don't move to that cell.

You just copy over the value of that cell into, in this case, 11, where your calculation is being written.

Step number five, we want to divide.

And so on your keyboard, you click that slash button towards the bottom of the keyboard.

Step number six is just the same as step number three, click on the cell C11, which contains the second number I want to use in my calculation.

At the moment, it will just say, =A11/C11.

To make the spreadsheet perform the calculation.

Step number seven, press Enter.

When you press Enter, that spreadsheet function =A11/C11 will disappear and the correct answer to the calculation, 40 in this case will appear.

Right, onto some checks for understanding for basic operations that you can do in a spreadsheet.

For check number one, which button must you always press first on your keyboard to start a mathematical calculation? Pause to think of your answer.

The button you must always press first is the equals sign.

Check number two.

Match the operations to the keyboard symbols.

Notice how, on the right-hand side, there are more symbols than there are operations on the left.

Some of the options on the right do not match with one of the four operations.

Pause to give yourself some time to match everything that you can.

Here are the answers.

Addition matches with the plus symbol, multiplication matches with the asterisk, subtraction matches with the subtraction or dash symbol, and division is the slash.

Remember, the letter X does not relate to any operation and definitely not multiplication.

Div, meaning divide, is not applicable for spreadsheets, but the hat symbol is useful just not for any of the operations that we will use today.

Here's a check that requires you to have a look at four possible spreadsheet functions and figure out which one is correct for the multiplication of 60 and 6 in the cell E13.

Pause here to give yourself time to review all four possible spreadsheet functions.

The answer is B.

Remember it is best to click on the cell that you want the value of.

In this case, I've typed in equals then clicked A13.

This means that the number 60 in A13 is included in my calculation.

Onto the final check for this part of the lesson.

There is an error in the cell E15 where I want to perform a mathematical calculation.

Pause the video here to look at that operation and figure out where the error is.

Here is the correct answer.

Notice how the correct answer is =A15+C15, not B15.

If I tried to click Enter to activate this operation, it would come up with an error.

The spreadsheet function actually says =45 + +.

As a mathematical calculation, that does not make any sense.

Right, onto some more complex functions using a spreadsheet.

Imagine I had this big long list of numbers that I wanted to add together.

I could do, =A37+A38+A39 and so on, but that's long-winded, especially for massive lists of numbers.

Instead, I can type into my cell, well, the equal sign and then the word sum.

Remember, sum just means add.

And on spreadsheet softwares, sum means add together a large list of numbers.

After typing in sum, make sure to open a bracket, then click, hold, and drag to select all the cells that you want to add together.

If I were to click hold and drag all of those numbers, it would look like this, A37:A43.

This means add all the numbers between the cells A37 and A43.

Make sure to close your bracket, and then press Enter to add all of those values together.

In this case, all of those values add up to 360.

Here's a quick check based on that sum function.

What two things are incorrect about this sum function? Pause to notice both of the incorrect features of this function.

The incorrect features are this.

There should be an open bracket immediately after the word sum.

Furthermore, this should be a colon, a double dot, not a semicolon which is a dot with a comma.

And this is what the correctly written function should look like.

Next check.

In the cell A58, I've written this, =sum(A54:A56).

What would the answer to this calculation be? Be careful about which cells are included in this function.

Pause now to notice the details and add together all the relevant numbers.

So we are looking at the cells A54 to A56.

That is these three cells.

The cells that contain 1, 2, 3, and 7 are not included at all.

And so the sum of 4, 5, and 6 is 15, and so 15 is your answer.

Now that we've learned about the four basic operations and the sum function, we have all the tools necessary in order to calculate angles from a pie chart.

In this example, a teacher has collected data showing the number of students from each year group who've attended a homework club.

The teacher wants to represent this data as a pie chart using a spreadsheet.

In order to convert this information into angles for a pie charts, we need to apply the following steps.

Step number one, calculate the total frequency.

We do this by selecting the cell underneath all the frequencies that are currently on the spreadsheets here.

Next up, we initiate the sum function by typing in =sum and then open brackets, like so.

After that, you need to click, hold, and drag to select all the frequencies above the cell that you are currently in, like so.

Make sure to put the close bracket after selecting all the cells you want to add together.

Press Enter, and this will convert your spreadsheet function into a total frequency.

After finding the total frequency, our next step is to find the multiplier.

In order to do this, we need to create a mathematical calculation that divides 360, the total angle inside of pie charts, by the total frequency that we just found.

To do this, we select the cell to the right of the word multiplier, which is over here, and then in that cell, we type in =360/.

Because we are always gonna start this calculation with 360, not a variable frequency that's in a cell, we don't need to click on a cell.

We can just type in the numbers 360.

Remember, that slash means divide, and so our calculation currently says 360 divided by something.

What is that something going to be? Well, We select the cell with the total frequency in it, in this case C12, to complete our calculation, 360 divided by 192.

Press Enter to convert that calculation into a value.

1.

875 in this case.

After calculating the multiplier, let's go on to step number three.

The value of the multiplier can be multiplied to any of the frequencies to calculate the angle that that subgroup will represent on a pie chart.

So select a cell in the angle column.

Let's do it for the first of the rows for Year 7.

Then type in equals to initiate a mathematical calculation.

Then select the frequency directly to the left of the cell that you are currently in.

In this case, where the number 42 is, that is the cell C7.

After that, you type in the asterisk symbol to multiply, not the letter X, and then you click the cell containing the multiplier, in this case, this cell, which is D11.

Make sure to press Enter to activate your calculation, giving you 42 times by 1.

875, 78.

75.

Once you've done it for one of those angle cells, it is simply a case of doing it again for each of the other angle cells that you still need to calculate, like so.

These are all the angles that you want to plot onto a pie chart.

We can check that all of these angles are correct by in the, one remaining cell, the total angle cell, checking that that equals some of all the angles above it.

Total, 360 degrees, the total angles inside a pie chart.

Okay, a couple of checks for calculations for angles in a pie chart.

Which of these functions is correct for the cell marked m? Think about where the cell m is located and what you would do in that cell.

Pause to look through your options and think about the purpose of the cell M is in.

The answer is C.

Below all the frequencies, you want to find the total frequency.

The total frequency is found by finding the sum of all of the frequencies above it.

A says the same thing, but because it doesn't have the equal sign, it is not a valid function.

Next up, which of these functions is correct for the cell marked k? We know that k is the multiplier, and so what calculation do you have to do to find a multiplier? Pause, have a look at those options.

The answer is B.

To find any multiplier, you're always going to start with =360.

You then divide by whichever cell contains the total frequency.

For the same question, We now know that the multiplier is 1.

3 recurring.

I want to find the value of the angle P.

Which of these options helps you find the value of angle P.

Pause here to look at the options, look at the cell names on each of the options to calculate which one properly calculates the value of angle P.

The answer is D.

I14 references the multiplier and H7 references the frequency directly to the left of angle P.

The frequency times by the multiplier is always the calculation that you need to do to correctly find the angle.

Onto the independent practise.

Click the link on screen to load up a Google Docs full of questions to practise this topic.

For the purpose of this first cycle, please only attempt questions on the two tabs, Practising functions and Calculating angles.

You can find these tabs at the bottom of the spreadsheet.

Pause now to open up the Google Sheets and attempt all of these questions.

Right.

In order to mark all the questions that you've just done, please click on this new link to load up the answers spreadsheet containing all of the same questions, but this time with the answers correctly written.

Please pause to open this up and mark and compare to what you've done on your own spreadsheet.

Right, we've spent a lot of this lesson messing around with spreadsheets using basic mathematical operations and using ratio tables to calculate angles for pie charts, but we haven't actually constructed any pie charts yet.

Well, the constructing pie charts part of this lesson is a lot quicker and a lot easier than it might first seem.

That is because spreadsheets are so good at creating pie charts for you with the information that you give it.

Let's have a look.

For this part of the lesson, please either click on the link on screen or use the Google Sheets that you were using for the previous practise.

You may also copy this table into a Microsoft Excel spreadsheet if you prefer.

Right.

For the purpose of this part of the lesson, please either use the Google Sheets that you were using for the previous practise, or click on the link that's on screen or copy and paste this table into a Microsoft Excel spreadsheet.

We want to create a pie chart from the information in that table to the right.

How do we do this? Well, our first step is to click, hold, and drag the Year Group and the Frequency columns, but not the angles columns, nor the total frequency row.

This is the information that you should be click, hold, and dragging until all of that information selected is highlighted.

In Google Sheets, at the top of the screen, there should be a button that looks like this.

This is the insert chart button.

It should be located around here on the top of the screen.

Click on this insert chart button and automatically a diagram will appear.

The diagram that gets chosen can sometimes be random, but, more often than not, it will be what they call a column chart.

We know this as a bar chart.

They are the same thing just with different names.

To change the diagram into a pie chart, go to the chart editor, then select chart type where it currently says column chart, and find pie chart.

Click on that and it will create a pie chart that looks like this.

After you've created your pie chart, you can right click on the pie chart to change a lot of the settings and options of the pie charts, including the style and colour of the pie chart or sectors of the pie chart, as well as tweak and modify the titles and the labels.

However, if you are using Microsoft Excel, the process is near identical.

Click, hold, and drag to select the Year Group and Frequency Columns, but not the Total Frequency row.

At the top of the screen, click the Insert tab, and find the charts icon.

Conveniently, the charts icon looks like a pie chart.

After clicking that charts icon, a dialogue box will come up with a bunch of different charts and graphs.

Scroll down to find the pie chart icon, click on that, and a very similar pie chart will appear on screen compared to the one we were able to create in Google Sheets.

So onto some checks for understanding about the Google Sheets interface.

On Google Sheets, which of these icons is the insert chart icon? Pause now to make your choice.

And the answer is C.

Notice how the insert chart icon looks a little bit like a bar chart.

Okay, here's where we start to make some connections.

You can also create a pie chart by plotting the angles rather than the frequencies.

To do this, click, hold, and drag only the Year Group column, ignoring the row that says Total Frequency, like so.

Notice how we're only selecting one column for the moment.

Next up, press and hold the Ctrl key on the keyboard and keep it pressed until I say to you to let go.

Click, hold, and drag the Angles column so that both the Year Group column and the Angles column are selected.

Please note you should not at any point be selecting the Frequency column or the row for totals.

Once you have successfully selected the Year Group column and the Angles column, you can let go of the Ctrl key on the keyboard.

The Ctrl key allows you to select the first and third columns whilst ignoring the second.

Without the Ctrl key, you cannot select two columns or two sets of data that are split apart on a spreadsheet.

And once you have successfully selected the Year Group and the Angles column, the process is exactly the same again.

Go to the insert chart icon and select pie chart if the default is a bar chart.

The top pie chart is the one from the Frequencies column, whilst this bottom pie chart is from the angles column.

What do you notice about the two pie charts? Pause here to have a look and compare the two pie charts, and come up with an explanation for why the two pie charts look the way they do.

You might have noticed that both pie charts look exactly the same, minus the title of course.

Why is that? This is because both the pie charts are based off of the exact same proportions.

The top pie chart is based off of frequency data, whilst the bottom pie chart is based off of angles calculated from those exact same frequencies that we used to make the top pie chart.

Because, ultimately, they both come from the same frequency data, they will both show exactly the same thing and look exactly the same.

They will always have the same sector angles as well as every other property.

Right, onto the last couple of checks for understanding.

Here are five different instructions to create a pie chart from angles and not frequencies.

Pause here to put these instructions into the correct order.

And the correct order is D, A, E, B, C D's first because you always have to click hold and drag the leftmost column, in this case, the car column.

A comes next because you have to click the Ctrl key on the keyboard before selected column that is not directly next to the first column that you want to select.

E is next because you actually have to click, hold, and drag to select your second column, which, in this case, is the angles column.

B is Next because now that you've selected your two columns of data, you need to go to the chart icon to create your pie chart.

And C is last because, well, remember, when you click the select chart icon, it may come up as a different chart.

You just need to tweak it to a pie chart if it isn't already.

Okay, onto the final set of practise questions, which is on the exact same document that you are using earlier for the first set of practise questions.

You can also use the link on screen to re-access that exact same document if you want to.

Find the creating pie charts tab at the bottom of the screen, pause the video, and have a go at all of these creating pie chart questions.

Here are the answers.

Use the link on screen to access the answers document.

On the answers document are all of the completed pie charts and completed ratio tables with all of the correct angles, frequencies, and multipliers.

Pause here to open up that document and compare all of the answers on it to the document that you were working on earlier.

Thank you so much for everything that you've done today on pie charts using spreadsheets.

In today's lesson, we have learned about the four basic operations in pie charts.

That is addition, subtraction, multiplication, and addition, as well as the sum function to add together large sets of data.

We have used all of those operations practically to calculate angles in a pie chart.

Furthermore, we have also learned how to create pie charts in spreadsheet software, either from frequency data or from the angles that we have used the operations to calculate.

That is all for this lesson.

Thank you so much for joining me and I hope to see you again in another math video.

Have a good day.