Hello, I'm Mr. Little.

Welcome to today's computing lesson from the unit Introduction to Spreadsheets.

The lesson titled today is "Spreadsheet Formulas." Now spreadsheets can do some amazing things, and we're gonna learn all about them today.

So make sure your brains are ready, you work hard, and try your best.

Let's get started.

By the end of today's lesson, you'll be able to calculate new data in a spreadsheet using formulas.

There are four key words I want you to look and listen out for.

I'll go through them now, but we'll learn about them more throughout today's lesson.

The first word is calculation, and that's a word I'm sure you would've used in maths before.

That means working out an answer using numbers and mathematical operations.

The second key word is operation.

That's a mathematical action, which I know you'd have done before, like adding, subtracting, multiplying, or dividing numbers.

The third key word is formula.

That's something, in today's lesson, that performs a calculation in a spreadsheet.

You'll be using lots of formulas today.

And the fourth key word is replicate.

That's to make a copy or repeat something in the same way.

I've split today's lesson into three parts.

The first part is about calculations and formulas in spreadsheets.

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

Izzy says, "I know spreadsheets are used to store and organise data.

Each cell in a spreadsheet has a cell reference." Alex says, "I know that you can format data in a spreadsheet." What else can spreadsheets do? Spreadsheets can be used to perform mathematical calculations, such as addition, subtraction, multiplication, or division.

Imagine 'em like a calculator.

They can do the simplest mathematical calculations to even the most complex.

Alex says a spreadsheet is like a calculator.

I can use a spreadsheet to perform calculations, like 14 add 24, 72 subtract 56, and 23 multiplied by 192.

Alex is right here.

Spreadsheets can do all sorts of different calculations and we're gonna learn about them today.

Okay, let's check your understanding of what calculations a spreadsheet can do.

I want you to select all the types of calculations that a spreadsheet can perform.

So there's four choices, A, addition, B, subtraction, C, multiplication, and D, division.

So select all the types of calculations that a spreadsheet can perform.

It's all of them, isn't it? Spreadsheets can perform all of these calculations.

Izzy asks here, "Could I get a spreadsheet to perform these calculations?" 54 add 10.

Yes, a spreadsheet could perform that calculation.

And likewise, it could also perform 7 multiplied by 11.

It wouldn't be able to do trainers divided by 5 or trainers subtract shorts.

That's because some data formats can be used in calculations, others can't.

Cells with numbers can be used in calculations.

Trainers and shorts here are plain text, so they can't be used in formulas.

Let's check your understanding using a true or false question about this statement.

A spreadsheet can calculate 345 multiplied by 389.

Is that true or false? It's true.

Why is that? Remember, spreadsheets can perform calculations with number cells.

Spreadsheets can't perform calculations with plain text cells.

So 345 multiplied by 389 here uses number cells.

Now we know spreadsheets can perform calculations using numbers, but how do we get spreadsheets to do this? Izzy asks, "How do I get a spreadsheet to perform a calculation?" So to get a spreadsheet to perform a calculation, you need a formula.

A formula performs a calculation in a spreadsheet.

So it's a little bit like a recipe that helps the computer do the maths and other calculations for you.

It's a little bit like if you've used a calculator before.

You're typing or tapping the numbers and operations on a calculator, but in a spreadsheet, you'll type them into the cells.

Izzy asked, "Why do people use formulas in spreadsheets?" perhaps rather than calculators or other things.

Now, formulas help save you time.

They help avoid mistakes and they help work out really tricky calculations quickly.

Let's check your understanding of what a formula is.

There's four responses to this question.

I want you to choose the correct one.

What is a formula in a spreadsheet? Is it A, a box in a spreadsheet that contains data? Is it B, numbers in cells? Is it C, something that performs calculations? Or, is it D, the numbers and letters that tell you the cell reference? That's right.

It's C.

A formula in a spreadsheet is something that performs calculations.

So let's take a look at a formula in action.

Alex here has saved his class's scores into a spreadsheet, and that's from a game.

They've played two games.

He's gonna use a formula to calculate the total score.

Now look at this spreadsheet in column A.

We've got the scores from game one.

And in column B, we've got the scores from game two.

And Alex is gonna use a formula to work out the totals.

So instead of adding them up himself, the spreadsheet's gonna do it for him.

Now, for Alex to get the spreadsheet to do a calculation, he needs to enter a formula.

And remember that's a rule that uses an operation.

So here Alex wants to add together the total of game one and game two.

So he's gonna use the operation addition, and he's gonna type this formula into cell C2.

So he types in equals.

Now, he's typing in equals because every formula starts with equals.

Then he's typing in the cell reference A2.

He's using the operation addition, and then B2.

So he's typed in =A2+B2 Now, to get the spreadsheet to work out the formula, Alex needs to press enter, Then the answer will be displayed in the cell.

So once again, Alex types in the formula =A2+B2.

He then presses Enter, and cell C2 shows the number 10.

Because the formula Alex entered, =A2+B2, has taken the number 8 from cell A2, the number 2 from cell B2, and added them together.

So when Alex presses Enter, he gets the answer of 10.

Izzy asks a really great question here.

She asks, "What is the formula, operation, and calculation?" 'Cause it's really important you understand that before we move on.

Alex replies with the correct answer.

The formula is what Alex typed into cell C2 which was =A2+B2.

The operation Alex uses, the mathematical symbol, is addition, and the calculation that the spreadsheet is doing is 8 add 2, which equals 10.

Let's check your understanding before we move on to a task.

I want you to fill in the missing gap.

A formula performs a, in a spreadsheet.

Is it A, cell reference, B, calculation, or C, task? It's calculation.

Remember that's one of our key words.

A formula performs a calculation in a spreadsheet.

So time for a task now to put all that knowledge to the test.

Using the word bank, complete the table to define what each word means.

So which word means working out an answer using numbers and mathematical operations, which word means a mathematical action like adding, subtracting, multiplying, or dividing numbers.

Which word means something that performs a calculation in a spreadsheet.

And here, I want you to choose the correct words which help define what a cell is.

A cell is a rectangular box in a something used to store something.

Use the words at the bottom to help fill in the gaps.

Let's take a look at the answers.

A calculation is working out an answer using numbers and mathematical operations.

An operation is a mathematical action like adding, subtracting, multiplying, or dividing numbers.

A formula is something that performs a calculation in a spreadsheet.

And a cell is a rectangular box in a spreadsheet used to store data.

Well done if you got those right.

The second part of today's lesson is about using formulas in spreadsheets.

So when you are using formulas in spreadsheets, some of the operations that you'll be doing using addition, multiplication, subtraction, and division, some of the symbols are the same as used in maths, but some are different.

Now let's take a closer look.

So this table shows the operator, the mathematical symbol you might use, and what you need to actually type into the spreadsheet when you're using a formula.

So notice in that first row when you're using addition, you use the add sign, the plus sign, and that's the same in maths and as a spreadsheet operator.

Same for subtraction.

When you're doing subtraction in maths, you use the dash, the subtraction sign, and the same when you're typing in a formula in a spreadsheet.

When you're doing multiplication, instead of using x or cross as a spreadsheet operator, like you might do in maths, you use something called the asterisk.

And we'll do a bit of my turn your turn so you can say the word correctly.

My turn, asterisk.

Your turn.

Once more.

My turn, asterisk.

Your turn.

And when you are dividing using spreadsheets, you don't use the division symbol that you might use in maths, you actually use the forward slash, and that's on your keyboard, like the asterisk is too.

So have a look down at your keyboard now.

See if you can spot the asterisk and forward slash Izzy asks a really good question here.

"Why can't I use x to multiply and divide symbol in a formula?" And that's because the asterisk and forward slash are part of a standard way of writing maths that computers have been programmed to recognise.

It's almost like an agreed rule that computers have decided upon, that instead of using x to multiply and the divide symbol in a formula, they use the asterisk and forward slash.

And it makes it easier to perform those calculations correctly.

So let's check the understanding.

What is the operator for multiplying in a spreadsheet? I'm not gonna read out the answers here.

I want you to choose which one you think is correct.

It's C.

And what's that called? That's right.

It's the asterisk.

So the operator for multiplying in a spreadsheet is the asterisk.

What's the operator for dividing in a spreadsheet? It's B.

And what's that called? It's the forward slash.

So remember the operator for dividing in a spreadsheet is forward slash Now we touched on this in the first learning cycle.

Remember formulas in spreadsheets always start with the equal sign, and that tells the spreadsheet that you want to perform a calculation.

So when you are typing a formula, always start using the equal sign and take a moment to look down at your keyboard to see if you can spot the equal sign.

So let's have a look at two spreadsheets below, and I want you to look at cell C2 in each spreadsheet.

Do you notice anything different On the spreadsheet on the left -hand side of the screen, there is an equals sign in the formula.

So what we're asking the formula to do in cell C2 on the left hand side is the formula A2+B2.

On the right -hand side, there is no equals, so the formula won't work.

The spreadsheet doesn't know what you're asking it to do.

So remember when you are typing a formula, always start with equals.

Izzy's full of great questions today.

She asks here, "When typing a formula, can I type data in the cells rather than the cell reference?" For example, could I write =8+2 rather than equals, using the cell reference, A2+B2? Now, you could do and it would give you the same answer, but it's always best in a spreadsheet, when you are writing formulas, to use cell references.

And that's because if you update the values or if the numbers in the cells change, the formula will automatically update the answer for you, instead of having to go back and type the numbers in the formula again.

time for a check for understanding.

It's a true or false question.

When typing formulas in a spreadsheet, you should use the cell references.

Is that true or false? It's true.

Remember you should always try to use the cell references when typing in formulas.

Let's go back to Alex's spreadsheet.

He's entered the game scores into his spreadsheet and wants to use a formula to calculate his total score.

A reminder, in cell C2, Alex has =A2+B2, and you press Enter to get the correct answer.

If he wants to use a formula to work out the other scores, he will need to type in a different formula into the cells he wants to work out.

So let's see what he's done.

Have a look at column C.

And let's look at cell C3.

Notice how the cell references are different 'cause he wants to work out from game one, 6 add 6.

That's sell A3 add B3 So he's typed in =A3+B3, and he is repeated that again for the other rows.

Let's look at the fourth row.

he's typed in =A4+B4.

He's gonna be typing in, or the spreadsheet's gonna be working out 6 add three for him.

And he's repeated that for rows five and six.

He's typed in a different formula for each row.

See if you can spot something Alex has done wrong here when he's entered the formula here.

So I want you to look at this spreadsheet and work out what Alex has done wrong.

Is it A, has he not entered the equal sign? Is it B, has he not entered an operator? Or is it C?, has he used the wrong cell references? It's C, he's chosen cell A1 and B1.

Now, if we look at A1 and B1, A1 has the word game one in it, and B1 has the word game two in it.

And remember, formulas can't do operations with plain text in them.

So he's entered the wrong sale references here.

Another example for Alex's spreadsheet to check your understanding, Alex wants to find out the total from game one and game two for row three.

What formula should he type into cell C3? Is it A, =A2+B2? Is it B, A3+B3=? Or, is it C, =A3+B3? It's C.

Alex should type in equals =A3+B3.

He wants to work out 6 add 6, So he types =A3+B3 into cell C3.

Okay, we've provided a spreadsheet for you to use formulas to add the scores for game one and game two together.

When you are doing that, remember to use the equal sign at the start of your formulas.

Use the cell references in your formulas, rather than the values, whatever's in the cell, and remember to press Enter once you have typed in the formulas.

Let's look at what you might have entered into the spreadsheet.

These are the formulas that I entered.

So in cell C2, I entered =A2+B2 and pressed Enter, and that gives me the answer 10.

In cell C3 I entered =A3+B3, and press Enter, and that gives me the answer 12.

In C4, I entered =A4+B4, and press Enter, and that equaled nine.

In cell C5 I entered =A5+B5 and press Enter, and that gave me the answer 10.

And in cell C6, I entered =A6+B6, and press Enter, and that gave me the answer 11.

The third part of today's lesson is about reusing formulas in spreadsheets.

Let's go back to Izzy and Alex's conversation.

They're talking about what they found difficult when they were typing in formulas.

Izzy says, "Sometimes I type in the wrong cell reference or operator in my formula." Alex says, "Me too.

It also took me a long time to type the formula over and over again." Now, one of the great things a spreadsheet can do is replicate a formula.

So instead of typing in a formula again and again into different cells, a spreadsheet can do that and make it quicker and easier for you.

Remember, replicate is one of our key words, and that means to make a copy or to repeat something in the same way.

So you are gonna be replicating formulas in cells.

You're gonna be making a copy of them or repeating them in the same way.

Izzy here has given an example of how she has a replica of her house keys so she can get into her front door, and she's also replicated images before on a presentation.

So she's got a copy of them.

Let's do a check for understanding by filling in the missing word here.

Replicate means to make a what of something or repeat it in the same way.

What's the missing word here? It's copy.

Replicate means to make a copy of something or repeat it in the same way.

So to replicate a formula with a spreadsheet, you need to follow these steps.

Once you've entered a formula, move your mouse to the bottom right corner of the cell until you see a small black cross.

That looks a little bit like the add sign, but don't worry.

You are not entering a formula here.

Then you click and drag the cross down to fill a column or sideways to fill a row.

You can replicate a formula either sideways, across a row, or down a column.

Then when you've highlighted all of the cells you want that formula to be replicated in, release the mouse.

The spreadsheet will automatically copy the formula to the new cells.

So let's take a look here.

I've typed in the formula I need.

I'm gonna hover over the bottom right -hand corner until I see that black cross.

I'm gonna click and drag all the way down, and release the mouse, and the spreadsheet automatically copies the formula for me.

Okay, let's check your understanding.

So when hovering over a cell to replicate a formula, what are you looking for and what do you want the cursor to show? Is there A, a blue dot, B, a small black cross, or C, a small blue cross? It's a small black cross, isn't it? Well done if you got that right.

Okay, Alex has made a new spreadsheet here to keep track of the places his classmates have visited.

So take a moment to read this spreadsheet.

Let's have a look.

The headings are visit name.

It looks like there are five different destinations.

So a theme park, a beach, a forest, a museum, and relatives.

Then we've got the locations, Staffordshire, Poole, Leicester, London, and Windsor.

We've got the travel duration, so how long it took to travel there.

We've got the distance in miles, and we've got the cost per mile, cost to get there.

And then we've got an empty column at the end for the journey cost.

Alex can use a formula to work out the journey cost.

What does Alex's formula need to include? Alex's formula needs to include the distance in miles, so from column D, multiplied by the cost per mile, column E.

And he needs to use the asterisk as his operator 'cause he's doing multiplication.

So look what he types in to sell F2.

He's typed in =D2*E2.

Now, instead of typing Alex's formula into each cell, he can replicate his formula.

And this will save him time, allowing him to work out journey costs more quickly.

So he's got his journey cost for the theme park journey to Staffordshire, and he's gonna hover over that cell, and he's gonna look for that small black cross, and he's gonna replicate his formula down that column for journey costs.

Let's check your understanding.

Which two of the following are benefits of replicating formulas? A, it saves time, B, it makes formulas harder to work out, C, it makes it less likely an error would be made, or D, it takes longer to do.

It's A and C.

When you're replicating formulas, it saves you time, and it makes it less likely there'll be an error.

So time for a task.

Using the spreadsheet provided, I want you to use a formula to work out the cost of the first journey, like Alex did.

Then replicate the formula for all of the journeys.

I then want you to work out a formula to work out the total travel duration, the total distance in miles and the total journey cost.

Here's an example of a completed spreadsheet.

I've got a column for my journey cost completed and I've replicated my formulas there.

And then I've used a different formula to work out the total travel duration, the total distance in miles, and the total journey cost.

We've come to the end of today's lesson, so let's have a summary of what we've learned.

Remember, formulas in spreadsheets are used to perform mathematical operations using data in cells, and only cells with numbers in them can be used in formulas.

We don't want to use plain text.

When you are entering a formula, you should always try and use the cell references.

And remember, formulas always start with the equal sign.

And spreadsheet formulas can be replicated, and that makes it easier and quicker, and makes it less likely you'll make a mistake if you are typing in multiple formulas.

Thanks for learning with me today.

See you soon.