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

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

We're going to be using spreadsheets to perform calculations.

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

This lesson is called Spreadsheet Calculations, and by the end of today's lesson, you'll be able to use formulas in a spreadsheet to complete calculations.

You'll need to have access to some spreadsheet software for this lesson.

Shall we make a start? We will be using these key words during today's lesson.

Formula.

Formula performs calculations in a spreadsheet.

Cell reference.

Cell reference tells you the location of a cell and is made up of a column letter and row number separated by a colon.

Fill handle.

Fill handle, a tool used in a spreadsheet to replicate cell data across a range of cells.

There are two main parts to today's lesson.

We'll start by using cell references in formulas and then we'll look at how we can replicate formulas across a range.

Let's make a start by using cell references in formulas.

Okay, here I have a table that has been started for the two times table.

Complete the table so it goes all the way up to two times 12.

Pause the video here whilst you complete the activity.

Okay, I'm sure you will know your two times table, so this didn't take you very long, but Aisha's saying that's great work, but we can get a spreadsheet to do the calculations.

Let's see how we can create a formula to do this calculation for us.

So we want the value to be held in cell D3.

So we're going to start by the cell A3 and then we're going to multiply it by.

Now notice in computing the arithmetic operator for multiply is the asterisk or the star symbol, not the X, which is sometimes used in mathematics, and then we're multiplying it by the value held in cell C3.

So the formula is =A3*C3 and then we press Enter.

Using the cell reference in the formula means that if the values held in A2 or C3 change, then the calculation will automatically update, whereas if I was to write =2*1, it would always be those set values.

In spreadsheet calculations, all formulas must start with the equals sign.

So you may have seen in the example I just showed you, our formula started with the equals sign.

The following operators are used in calculations.

So some of these are familiar.

Some of these may not be familiar.

So for addition, we use the plus operator.

For subtraction, we use the dash or the subtraction operator.

For multiplication, as we've just seen, we use the asterisk or the star operator.

And then for division we use the forward slash, which again is different from what you may have seen in mathematics.

Time to check your understanding.

In a spreadsheet formula, which symbol is used for division? Is it A, B, or C? Pause your video here whilst you have a think.

That's correct.

B, the forward slash is used for division in a spreadsheet formula.

Fill in the gaps to complete the sentence.

In spreadsheet calculations, all formulas must start with an.

Using in formulas means that the answer will automatically update if the value held in the cell changes.

Pause the video here whilst you have a think.

Let's have a look at the answers together.

In spreadsheet calculations, all formulas must start with an equals.

Using cell references in formulas means that the answer will automatically update if the value held in a cell changes.

Okay, now time for the first task of today's lesson.

Start by downloading the spreadsheet calculation file.

Then complete the data and formula for the three and four times tables.

Pause your video here whilst you complete the activity.

How did you get on? Great work! So hopefully you had something like this for your formulas.

So =C20 multiplied, so the asterisk symbol, by E20 and then for the next one down, it would've been =C21*E21 and so on all the way down for the times table.

If you didn't quite get that correct, maybe pause the video here and have another go.

For the final part of task A, I'd like you to complete the calculations.

Pause the video here whilst you complete the activity.

How did you get on? Did you manage to do all of your formulas correctly? Let's have a look at some sample answers.

So for question number one, we've got =C4* or multiplied by E4.

For two, we've got =C5+E5, so the addition operator.

For three, we've got =C6-E6.

For four, it's another multiply, so we've got =C7*E7.

And then for five it's a divide, so we've got =C8/E8, and notice we use the forward slash there for the division operator.

Great work so far.

Well done.

We're now moving on to the second part of today's lesson where we're going to replicate formulas across a range.

A cell range is a group of cells in a spreadsheet that have been selected.

A cell range is made up of the cell references of the first and last cells in the range separated by a colon.

Time to check your understanding.

What is the range of these selected cells? That's right, the range is A2:A5 because A2 is the first cell selected in the range and A5 is the last cell selected in the range.

Ah, Aisha's got a really good point.

Duplicating the times table calculations was very repetitive.

Sometimes writing out all those formulas can take quite a long time.

Ah, Jun's got a good point.

Spreadsheets have tools to help you replicate cell data across a range.

The fill handle tool can replicate formulas across a range of cells.

This is particularly useful if you're using the same formula in a column that includes lots of data.

The clever thing is that the fill handle will automatically update the cell references in the formula for you.

Let's see how we use the fill handle.

So we start by clicking on the cell which contains the formula.

We then hover over the right-hand corner until the cell cursor changes from an arrow to a cross and then we click and drag down the data.

See, the formulas have automatically updated.

Time to check your understanding.

What is the name of the tool that can replicate formulas across a range of cells? Is it A, fill handle, B, drag handle, or C, formula? Pause your video here whilst you have a think.

Did you put A? Great work! The fill handle is the name of the tool that can replicate formulas and other data across a range of cells.

We're now moving on to the last set of tasks for today's lesson.

For task B, part one, you need to download the Spreadsheet Calculations file.

You then need to complete the spreadsheet to calculate the ingredients needed for different quantities.

Remember to use the fill handle to replicate formulas across a range of cells.

Pause the video here whilst you complete the activity.

Okay, for activity B, you were asked to take one of the recipes and use formulas to do the calculations.

So let's just see how this would work.

So we've got the ingredients or the recipe for chocolate chip muffins here.

We've got the quantity and then we've got what we need if we want to do double, triple, or half that recipe.

So in the double column I'm going to put, select the cell, which is cell D3.

I'm going to put the equals symbol and then I'm going to do two, and then I'm going to do multiplied by two because that will be doubling it.

And then I'm gonna hit Enter.

Now you can see here that my spreadsheet tool has already suggested an autofill, so I could just hit the tick here and that would do it.

But what I can also do is use the fill handle like we've seen in today's lesson.

So I'm going to hover over the bottom right-hand corner.

I'm going to click and I'm going to drag down and that will do the same thing.

So in this cell, for example, I've got C4*2.

Next one down is C5 and so on.

Okay, for triple the recipe, we are going to be timesing by three, so similar formula.

So we're going to do equals, the quantity again, but this time we're gonna be multiplying by three instead of two.

This time I'm going to accept the suggestion here and I'm just gonna tick that and let that finish that off.

And then the last one is half.

So this time we're going to do equals, the quantity needed, but to do half, I'm going to need to divide it by two.

So I'm going to do the forward slash and then two.

And then I'm gonna hit Enter.

I'm not going to accept the autofill.

I'm going to do that manually.

So I'm gonna hover over the bottom right-hand corner and drag it down and we should see that the quantity there should be half in that new column.

For the final part of task B, explain why the fill handle is useful when performing calculations in a spreadsheet.

Pause the video here whilst you complete the activity.

How did you get on? You've done a great job, so well done.

Let's have a look at the sample answer together.

The fill handle allows you to duplicate data across a range of cells.

Rather than having to type in lots of formulas, the fill handle will automatically copy the formulas and update the cell references for each formula.

Remember, if you need to add extra detail to your answer, you can always pause the video here.

You've done a fantastic job today, so well done.

Let's summarise what we have learned.

Spreadsheet calculations always start with the equals symbol.

Using cell references in a formula means that if the data is updated in the cell, then the calculation is automatically updated.

The fill handle tool can be used to replicate formulas across a range of cells.

I hope you'll join me for another lesson again soon.

Bye.