Hello, welcome to today's computing lesson.

My name's Mr. Little, and today we are going to be learning about using formulas and spreadsheets, and that's from the unit an introduction to spreadsheets.

Are you ready to get started? Brilliant.

Me too.

Let's get cracking.

By the end of today's lesson, you'll be able to use spreadsheet functions that work with ranges of cells.

There's two keywords that I want you to look and listen out for in today's lesson.

The first word is range, and that's a group of cells that are located next to each other.

We are going to be talking about ranges of cells, so groups of cells that are next to each other today.

The second keyword is function, and that's a ready-made instruction that performs a specific mathematical job.

You might have learned already about formulas, and a function is a specific type of formula that's ready-made.

So look out and listen for the words range and function.

There's two parts to today's lesson.

First of all, we're going to identify ranges of cells.

Izzy and Alex are talking about what they know about cells in spreadsheets.

Izzy says, "I know spreadsheets are used to store and organise data, and each cell on a spreadsheet has a cell reference." Alex says, "A cell is a rectangular box, and you can input data into cells." So remember, we use cell references to describe individual cells.

So here we've got cell A1, and inside A1, inside cell A1 is the plain text game 1.

However, when we're talking about a group of cells, we are going to talk about a range, because a range is a group of cells that are located next to each other.

So let's look at this example.

Here on the first row, the range of cells here is A1 to D1.

This means that the cells A1, B1, C1, and D1 are in that range.

And we're describing a horizontal range here.

And remember, horizontal goes from left to right or right to left.

Here we've got a different range of cells.

The range of cells here is C1 to C4, and this means the cells C1, C2, C3, and C4 is in this range.

And this is a vertical range.

Remember, the word vertical means from top to bottom or bottom to top.

So just to recap, a range of cells is a group of cells that are located next to each other.

And we've got two different ranges on this slide.

Let's check your understanding of what a range of cells is.

So true or false? A range of cells is an individual cell.

It's false, isn't it? But why is that? Remember, a range of cells is a group of cells.

An individual cell is just called a cell.

When you are typing ranges of cells into a spreadsheet, perhaps into a formula, it's really important that you follow this format.

So here is a range of cells, A1 to D1, and I've selected it here on my spreadsheet.

So the first thing I type is the first cell.

So the first cell I want to choose in my range here is cell A1.

I then type a colon, and you might recognise this as a piece of punctuation from English.

And have a look down at your keyboard or your keypad now and see if you can spot where a colon is.

And then you type the last cell in the range.

So the last cell in the range here is D1.

So I've typed A1:D1, and there's no spaces.

So just to recap, the range A1 to D1 means cells A1, B1, C1, and D1 here.

Okay, time to check if you can see which range of cells here is highlighted.

So I want you to select the image with the range of cells that is B1 to B4.

Well done if you selected C.

Similarly in this check for understanding, I want you to choose the image that shows the range of cells A2 to D2.

Well done if you selected A.

Now, a range of cells can include more than one row or more than one column.

Let's take a look at this example.

Here I've got a range of cells, and the range of cells here is A1 to B4.

And this means the cells in a rectangle from A1 to B4.

In this check for understanding, which image shows the range of cells B2 to D4? Well done if you selected B.

Time for a task now.

You need to write the cell range underneath each image.

So there are three to do.

And for the second part of the task, instead of writing the cell range, I want you to draw a box around the cell range for each image.

So the first one, a, you need to draw a box around the cells, or the cell range, B1 to B4.

Then for b, you need to write the cell range, or draw a box for the cell range, A4 to D4.

And then for c, you need to draw a box for the cell range A3 to D4.

Let's take a look at the answers.

For 1a here, the cell range is A1 to A4.

For b, it's B2 to C4.

And for c, it's A4 to D4.

Well done if you got those right.

For this part of the task, see if your box matches the answers here.

So for a, B1 to B4 should look like the example.

Same for b, A4 to D4.

And for c, A3 to D4.

For the second part of today's lesson, you are going to use spreadsheet functions.

Izzy and Alex are talking about why using cell ranges are useful.

Izzy says, "Using cell ranges makes it less likely I'll make a mistake when I'm typing.

I can make a mistake when I'm typing a long list of cell references." Alex agrees with Izzy.

He says, "That is right.

Also, they speed things up, that cell ranges, especially when working with large amounts of data.

Imagine having to type in a hundred cell references into a formula." Now, we know that spreadsheets can be used to perform common mathematical calculations such as addition, subtraction, multiplication, and division.

Remember, for multiplication and division, the asterisk and the forward slash are not the normal type of mathematical symbol you might use in a maths lesson.

And remember, the asterisk and forward slash are the computing code for multiplication and division.

Alex asks here, "Can spreadsheets do anything else?" Yes they can, Alex.

Spreadsheets can also perform other functions.

So let's take a look at what a function is.

A function in a spreadsheet is a ready-made instruction that performs a specific mathematical job.

It does some of the hard work for you.

And Izzy asks, "What do functions actually do?" They save time by doing calculations instantly.

So instead of having to type out A2 + B2 + C2 + D2, and so on, functions can do this instantly for you.

Also, functions will reduce errors.

Thinking that example, if you have to type in a hundred cell references, you might accidentally make a mistake.

You might make a typo where you type something incorrectly, or you might choose the incorrect cell reference or the incorrect operator.

So functions help to reduce errors.

And like I said, functions make big tasks easier.

Spreadsheets can calculate formulas with one, ten, a thousand, a million cell references in them.

So it definitely makes bigger tasks easier.

Let's check your understanding.

What is a function? Is it A, a defined group of cells that are located next to each other? Is it B, a ready-made instruction that performs a specific mathematical job? Or is it C, a calculation you give a spreadsheet? It's B.

A function is a ready-made instruction that performs a specific mathematical job.

Another check for understanding here.

Which of the following is not a benefit of using functions? A, it makes big tasks easier; B, it makes you type lots of information; C, it saves time; or D, it reduces errors.

It's B.

Making you type a lot of information isn't a benefit.

Remember, functions make big tasks easier, they save you time, and they help reduce the errors that you might make.

Now let's have a look at some of the functions you could use on a spreadsheet.

Now, there are lots available, but in this lesson we are going to be learning about the following three.

The first one is called SUM, and that's where you add all of the numbers in a range.

The second function is called AVERAGE.

And that works out the mean of the numbers in a range.

So the mean is the average of a set of numbers, and you might have learned about the mean in maths.

And the last function we're going to learn about is called COUNT.

And that counts how many numbers are in a certain range.

Now notice here how SUM, AVERAGE, and COUNT are in capital letters.

That's because when you are using a spreadsheet and you're using spreadsheet functions, the SUMs, the AVERAGE, and the COUNT functions are always in capital letters.

So when you are using functions, you can either type the function using your keyboard or keypad into the cell you need to to work out a formula, or you can select the cell range you need and use the function button.

And that's on the right hand side of the screen here.

If you are typing the function, when you are entering your cell range, you must include that cell range in brackets.

That helps tell the spreadsheet which cell range you want to use.

So let's look at that first example.

I'm using the formula sign for equals and then I've selected or typed the SUM and I've opened my brackets for A2 to B2.

And I've closed my brackets.

And look at the exact same example for AVERAGE and COUNT.

So the SUM function in a spreadsheet adds up the numbers in a selected range of cells.

So instead of having to add them manually or work them out, work out the calculation manually, the spreadsheet will do it for you.

And the SUM function does it instantly.

So let's watch the media clip.

In this clip, I'm going to show you how to use spreadsheet functions and the SUM function first.

So I want to work out the sum and the total of game 2.

So in cell B6, I'm going to type =SUM, open bracket, and then my cell range, which is B2 to B5.

I'm going to close my brackets and then press Enter.

So the total of my game 2 scores is 176.

Let's repeat that for the average.

So let's say I want to work out the average of all of the game 1 and all of the game 2 scores.

In cell B6, I'm going to type =AVERAGE, and my cell range is different this time, it's A2 to B5.

Close brackets and press Enter.

So my average score is 53.

375.

Let's say I want to use the COUNT function, count how many scores I've got in total.

In cell B6, I will type =COUNT, and then my cell range, open bracket, B, sorry, A2 to B5.

Close brackets and press Enter.

You can see there's eight scores in total.

So to get the total points scored in game 2, instead of typing this into cell B6: =B2 + B3 + B4 + B5, I can use a function.

So I'm going to type this into cell B6: =SUM(B2:B5).

So the SUM function here will add all of the numbers in that cell range together.

The AVERAGE function in a spreadsheet finds the mean.

What it does is it adds up all of the numbers in a selected cell range and then divides them by the number of cells.

So let's take a look at this example.

Instead of typing this long formula where I could make a mistake, =(A2 + A3 + A4 + A5 + B2 + B3 + B4 + B5) divided by eight.

Remember, I use my forward slash for divide.

And there are eight cells in the range, so I'm adding up the total and dividing by eight.

I'm going to type this into cell B6: =AVERAGE(A2:B5).

It's certainly easier doing that.

The COUNT function in a spreadsheet counts how many numbers are in a selected range of cells.

Now, when you're using the COUNT function, you need to make sure you choose the correct cell range, because the COUNT function will only count cells with numbers in, and it will ignore anything with text in or any blank cells.

So in this example, I want to count how many numbers are in this cell range.

So from cell A2 to B5, I can see there are eight numbers, 80, 20, 60, 65, 60, 38, 51, and 53.

So to count those, I would type into B6: =COUNT(A2:B5).

Let's check your understanding here.

Izzy wants to work out the total of all of the scores in game 1 and game 2.

What does Izzy need to type in as her formula? She needs to type in =SUM(A2:B5).

So let's just unpick that a bit because Izzy wants to work out the total, so that's the SUM function, she's adding them all up.

And she selected the cell range A2 to B5.

That's all of the scores from game 1 and game 2.

Another check for understanding now.

Alex wants to work out the number of scores that have been entered into a table.

Which function should he use? Is it A, the SUM function? Is it B, the AVERAGE function? Or is it C, the COUNT function? It's C, the COUNT function, because the COUNT function counts how many numbers are in a selected range.

Izzy and Alex here are debating whether to use functions or their own formulas.

Izzy says, "I know what formula to use, so I'm going to type it in rather than use a function." Alex disagrees.

He says, "I'm going to use a preset function wherever I can." Who do you think is right here? So Izzy wants to type in her formulas.

Alex wants to use the preset functions.

Alex is right here.

You should try to use preset functions wherever you can in spreadsheets.

Let's check your understanding with a true or false statement.

You should write your own formulas as much as possible in spreadsheets.

Is that true or false? It's false.

But why is that? You should always try to use preset functions wherever you can in spreadsheets.

They'll save you time, they'll make less mistakes, and it will make a big task a lot easier.

Time for a task now to put all that knowledge into practise.

Using the spreadsheet functions file that we've provided, I want you to use functions to complete the tasks below.

So firstly, use the SUM function to calculate the total scores for each individual game.

Number two, use the SUM function to calculate the total scored in all games.

Number three, use the AVERAGE function to calculate the average score for each game.

Number four, use the AVERAGE function to calculate the average score for all games combined.

And number five, use the COUNT function to calculate the total number of scores.

The completed spreadsheet should have the following values.

Let's look for the total scores.

So for game 1, 251; game 2, 176; game 3, 142; and game 4, 181.

The average score for game 1 is 62.

75; game 2, 44; game 3, 35.

5; and game 4, 45.

25.

The count is 16, the total score for all games is 750, and the average score for all games is 46.

875.

Well done if you used functions correctly there.

Let's summarise what we've learned in today's lesson.

A range of cells, remember, is a group of cells that are next to each other, and that might be a vertical range, a horizontal range, or it might be a rectangular range of cells.

And you can use functions in spreadsheets to perform specific mathematical jobs, and they're calculations.

And you should always try to use functions wherever possible in spreadsheets because it makes it easier to perform complex calculations on large ranges of cells.

Thanks for learning with me today.

See you soon.