Hello, my name is Mrs. Holbrow and welcome to Computing.

I'm so pleased you've decided to join me for the lesson today.

We're going to be using spreadsheets in today's lesson, we're going to be using functions to analyse data and we will look at how we can create charts to represent and visualise information.

Welcome to today's lesson from the unit Data Modelling.

This lesson is called Analysing and Visualising Data, and by the end of today's lesson, you'll be able to analyse data using functions and summarise findings using charts.

You'll need to have access to some spreadsheet software for this lesson, shall we make a start? We will be exploring these keywords during today's lesson.

Function.

Function.

A predefined formula that performs a calculation.

Chart.

Chart.

A graphical representation of data.

There are two main parts to today's lesson.

We'll start by using functions to analyse data and then we'll move on to create appropriate charts to visualise information.

Let's make a start by using functions to analyse data.

To add the value in two cells together, you can use the formula with the addition operator.

So you can see here we've got the example of equals A1 plus B1.

So this will add the contents held in cell A1 to the contents held in cell B1.

Sam's got a good question though.

She's asking, "But what can I do if I want to add up the value of lots of cells?" Adding up multiple cells using a formula and addition operators can be time-consuming.

So you can see here I'm adding up the contents of 10 cells and I've had to repeat that addition operator 10 times.

June is asking, "Is there another way?" A function is a predefined formula in a spreadsheet that can help with calculations.

For example, there are functions that will find the total value of a group of cells, find the highest value of a group of cells, and find the lowest value of a group of cells.

There are lots of functions, but these are just a few examples.

Here's our first example, the SUM function.

The SUM function returns the total value of a range of cells and it looks like this.

So we start with the equals sign.

We write the word SUM, S-U-M, we open our brackets, and then we write down the range of cells that we want to use in the calculation.

So this SUM function is using the range, A1 to A10.

We then close our brackets and we hit Enter to perform the calculation.

Remember, a cell range starts with the first cell you've selected and then has a colon and then the last cell you select in the range.

The SUM function is much quicker than using a formula with the addition operator, if you want to add up the value of multiple cells.

So you can see here the original formula is turned into the function equals SUM, open bracket, A1:A10, close bracket.

This is much quicker to write.

A function references the first cell and the last cell of the range you want to work on.

Let's have a look at our next function, the MAX function.

The MAX function returns the highest value in a range of cells.

It looks very similar to the SUM function in the way it works, but this time we've got the equals sign and then the word MAX instead of SUM.

We still open our brackets and have the range of cells we want to include in the function and then close the brackets afterwards.

So it's equals MAX, open bracket, A1:A10, close bracket.

The MIN function does the opposite to the MAX function, so the MIN function returns the smallest value in a range of cells.

Equals MIN, open brackets, the cell range, close the brackets.

Time to check your understanding.

Which function returns the total value of a range of cells? Is it a, MIN, b, SUM, or c, MAX? Pause the video here whilst you have a think.

Did you put b, great work? The SUM function returns the total value of a range of cells.

Here's another function.

This function is called the COUNTA function, the COUNTA function count cells that have anything at all in them.

So this could be data, it could be a formula, it could be text, it could be numbers.

As long as they're not empty, it will count the number of cells.

So this formula is equals COUNTA, open brackets, the cell range, close brackets.

Here's another example of the COUNTA function, but notice we've used a new referencing notation here.

This notation is used to reference a range that includes the entire column.

So you can see here we've just got A:A and not the row numbers like we've had in our previous examples.

This means that this COUNTA function will check the whole of column A in the spreadsheet and return the total number of non-blank cells.

Time to check your understanding.

How could you create a cell reference that would reference the entire column C? Pause your video here whilst you have a think.

Did you have C:C? Great job.

So let's have a look at this in an example of a function.

So I've got equals COUNTA, open brackets C:C, close brackets.

This will reference the whole of column C in my COUNTA function.

Remember, the fill handle can be used to replicate a formula or function across a range of cells.

To do this, you select the first cell, you click on it and then you hover over the bottom right-hand corner until your cursor changes to the cross icon.

You then click and drag down and the formula or function will be replicated.

You're doing a great job so far, so well done.

We're going to move on to the first set of tasks for today's lesson.

For Task A, Part 1, I'd like you to download the party planning spreadsheet and go to the recipe ingredients tab along the bottom.

For each ingredient, calculate the biggest and smallest amount any of the recipes need using the MIN and MAX function.

Imagine somebody wants to bake one of every recipe on the spreadsheet.

Using the SUM function, work out how much of each ingredient they will need in total.

And then count how many ingredients each recipe needs using the COUNTA function.

Pause the video here whilst you complete the activities.

How did you get on with your spreadsheet? You're doing a great job, so well done.

Here is an example of the completed spreadsheet, but shall we go and have a look and see how it was created? So the first task was to calculate the maximum and minimum ingredient for each recipe item.

So what we're going to do is go to this first cell, which is cell J2, and we are gonna calculate the maximum out of baking powder as an ingredient.

So we're going to say equals and then the word MAX, we're gonna open our brackets, we're gonna highlight the range of cells, so that's all the way along here for the different recipes.

We're going to close our brackets.

So that's the cell range, C2:I2, and then we are going to hit Enter.

Okay, so that's put in three, which is the maximum, which we can see here is correct, 'cause three is the highest number.

What we can then do, so we can use the fill handle to drag that formula down and that's done that for all of the other recipe items. For the minimum, what we can do is very similar, so we are going to do equals and then the word MIN this time instead, open our brackets, highlight the same range as we did for the maximum function, close the brackets and hit Enter.

Okay, so this time it's one, because that is the lowest number of baking powder needed in a recipe and then can use the fill handle again to drag that down.

So the next activity was to calculate the total using the SUM function.

That's going to go into column L.

So this time we're going to do equals and then the word SUM, and then we're going to open our brackets and we're going to highlight the same range as we did before.

And then we are going to close our brackets.

Remember not to include the maximum and a minimum in your total, because that would make the result incorrect.

And then I'm gonna hit Enter.

So that's added all of those up.

So 3 plus 2 plus 1 is 6, and then if I use the fill handle, I can scroll that down and that duplicates that formula.

Then what we were asked to do is we were asked to look at the number of ingredients in each recipe.

So this time we using the COUNTA function.

So going down into cell C24, I'm going to do equals COUNTA, open my brackets, and I'm going to highlight those ingredients there in column C.

So that's the range C2 to C22.

I'm going to close my bracket and hit Enter.

And that tells me that eight recipes in total, sorry, the chocolate chip has eight ingredients in total.

And then if I just duplicate that along, oh, one more, you can see that that has done those for each of the recipes.

What you were then asked to do is give the total number of ingredients listed in column A.

So how many ingredients in total are there? So again, what we're going to do is we're going to do another COUNTA function, so equals COUNTA, open our brackets, but this time we're gonna highlight all of the ingredients there, close our brackets and hit Enter.

And in total there's 21 ingredients.

We're now moving on to the second part of today's lesson where we're going to create appropriate charts to visualise information.

Which information is easier to interpret and understand? Maybe pause your video here and have a think.

That's right.

The information that is displayed in the chart is easier to understand.

Sam says, "The chart makes it easy to see which country won the most medals." I think she's right.

You can easily see that the bar representing the United States is much higher than the rest.

Graphs and charts can be used to visualise information.

Different types of charts visualise information in different ways.

So here I've got three different examples of charts.

Let's have a look more carefully at each of them.

Bar charts are used to compare different categories.

The height of the bar corresponds to the data that is being represented.

So the higher bar has more data and the lower bar has less data.

In some spreadsheet software, these charts are called column charts.

Pie charts are represented as a circle divided into segments.

Pie charts are particularly useful for presenting percentages.

Line graphs are used to show changes in data over time.

So this example is showing the Great Britain Olympic medals between the years 1992 and 2021.

Time to check your understanding.

You want to present some information on the percentage of pupils who travel to school by walking, which would be the most suitable chart or graph to use? Is it a, a bar chart, b, a pie chart, or c, a line graph? Pause the video here whilst you have a think.

That's right.

The correct answer is b.

A pie chart would be most suitable here, because we are trying to present the percentage of pupils who travelled to school by walking.

Remember, pie charts are particularly useful for representing percentages.

You want to present some information on the average temperature over the past year, which would be the most suitable chart or graph to use? Is it a, a bar chart, b, a pie chart, or c, a line graph? Pause the video here whilst you have a think.

That's right, c is the correct answer.

A line graph is most suitable here, because we are representing how data has changed over a period of time.

Let's watch this video to see how you can create a chart from data stored in a spreadsheet.

So you can see we start by selecting the data that we want to use in the chart.

We then go to the chart icon and we select the type of chart we want to use.

We can also go into the properties and change things like the title of the chart or the title of the axes.

We're now going to move on to the next set of tasks for today's lesson, Task B.

Start by opening the party planning spreadsheet that you used in Task A, but this time go to the inside one cake tab at the bottom.

For Part 2, you're going to compare the proportions of each ingredient in a particular recipe.

This comparison will be done according to weight.

Choose one of the recipes to work with.

Convert each measurement into grammes according to the conversion table provided.

Make a pie chart of the quantity in grammes of each ingredient in the recipe.

Which ingredient makes up the biggest proportion in your recipe? Which ingredient makes up the smallest proportion in your recipe? Pause the video here whilst you complete the activities.

Okay, how did you get on? Did you manage to create a pie chart? Here's my example.

So I've chosen the recipe for chocolate chip muffins, and what I've done is I've created a pie chart to show the percentage of each ingredient in the recipe.

I can see quite clearly from my pie chart that flour makes up the most of this recipe.

It has the largest.

And then the smallest quantity is the salt followed closely by the baking powder.

Now open the party shopping list tab.

Make a pie chart comparing how much money will be spent on different items on the list.

Make a bar chart showing how much of each item will be purchased.

And then below the shopping list, add in a row showing the total cost of the shopping.

And as a hint, you need to use a function to work out the cost.

Pause the video here whilst you complete the activity.

How did you get on? Did you manage to create the charts? Here's some sample answers.

So I've created a pie chart for the cost of party items, and then I've created a bar chart for the quantity of items to buy.

For Part C, you were asked to work out the total cost of the items that needed to be purchased for the party.

So you can see here I've got the costs and then in the total cost, this has been calculated as 20.

48.

Did you use a function to calculate this? What function did you use? Hopefully you used the SUM function.

So in this example, it would've been equals SUM, open brackets, F2:F6, close brackets.

You've done a great job today and you've learned how to use functions and charts in spreadsheets, so well done.

Let's summarise what we have learnt.

A function is a predefined formula in a spreadsheet that can help with calculations.

The MIN function returns the smallest value in a range.

The MAX function returns the largest value in a range.

The SUM function returns the total of a range of cells.

The COUNTA function returns the total number of non-blank cells.

Charts can be used to visualise information.

Different types of charts visualise information in different ways.

I hope you'll come back and join me again soon, bye.