Welcome to this Oak Academy lesson, which is called "Formulas in a Spreadsheet Model," and it's taken from the unit "Spreadsheets." Thank you for joining me today, and I look forward to learning with you through this lesson.

The outcome of this lesson is that you'll use formulas to perform calculations in a spreadsheet model and replicate formulas across a range of cells.

We've got three keywords in this lesson.

So replicate is the first key word.

Replicate is to copy or repeat something.

Then we have relative, relative, a cell reference that changes when a formula is copied or replicated to another cell.

And then absolute, absolute, a cell reference that remains constant no matter where it's copied or replicated.

So our three key words replicate, relative and absolute.

We've got two learning cycles in this lesson.

So first of all, you're gonna replicate formulas across a range of cells.

So we'll get started on that in a couple of seconds.

And then later in the lesson we'll perform calculations with absolute cell references.

Spreadsheets are used for many data related tasks.

And here's three examples from Andeep who says, my mum uses one to keep track of her small business costs, like stock prices and profits.

From Sam, who says, we use one in science last term to plot our experiment results and draw graphs.

And from Sophia who says, my brother uses them at work all the time, he tracks budgets and project timelines with them.

So three great examples of how you can use spreadsheets.

Spreadsheets can perform calculations.

To do this, you need to use a formula.

Formulas are special rules that use operations.

So Lucas points out a spreadsheet is like a calculator.

I can use formulas in a spreadsheet to form calculations.

So some things can make entering formulas challenging.

And here's a couple of examples.

So Andy says, "Sometimes I type the wrong cell reference or operator in my formula." And Sofia says, "It takes a long time to type the same formulas or functions again and again." So let's see what we can do about that.

So instead of repeatedly typing in similar formulas into different cells, you can replicate a formula into other cells.

This way you can reuse the formula in different cells quickly and easily.

So just a reminder that replicate means to make a copy or repeat something in the same way.

Here's how you can replicate a formula in a spreadsheet.

So follow these steps.

So first of all, once you've entered a formula, move your mouse to the bottom right hand corner of the cell until you see a small black cross.

This is the fill handle.

That we are on the fill handle on the animation, click and drag the fill handle down to fill a column or sideways to fill a row and then release the mouse.

The spreadsheet automatically copies the formula to the new cells.

So I'll just talk through that as the animation's running.

So you enter the formula, highlight the cell, click on the fill handle, hold and click drag down, and then release.

And there you have your formula copied down, column C.

Time for a question.

Which of the two following are benefits of replicating formulas? So we have A, it saves time.

B, it makes formulas harder to work out.

C, it makes errors less likely.

And D, it takes longer to do.

So which two of the following are benefits of replicating formulas? Well done it's A and C.

So it definitely saves time and it makes errors less likely.

When you use the fill handle to replicate a formula, the cell references change automatically.

Now this is because formulas use relative references by default.

A relative formula changes when moved or copied to another cell.

This is useful when you're doing the same calculation across rows or columns.

So relative formulas automatically update based on their position.

In this example, if the formula in the cell F1 is equals D2 multiplied by E2, when you use the fill handle to replicate it, cell F3 will fill with equals D3 multiplied by E3.

So let's have a look.

Click on the fill handle, drag down, and then it will fill F3 with equals D3 multiplied by E3.

And then you can see the total from the formula.

Using the fill handle to replicate the relative formula will save time, which allows you to more efficiently work with the data.

The fill handle can also be used to replicate text.

So for example, you can replicate text patterns like days of the week.

If you type Monday and drag the fill handle, it will fill in Tuesday, Wednesday, et cetera.

So here we have Monday.

If we drag the fill handle, we'll get Tuesday and Wednesday.

So fill in the blanks from the sentences.

A blank is a tool that helps you organise and calculate data quickly.

A blank formula changes automatically when you copy it to another blank using the blank.

This makes it easy to apply the same calculation to different rows or columns.

Okay, let's fill those in.

A spreadsheet is a tool that helps you organise and calculate data quickly.

A relative formula changes automatically when you copy it to another cell using the fill handle.

And this means it easier to apply the same calculation to different rows or columns.

So here's task A for you.

Download and open the spreadsheet file Tuck Shop.

In cells D2 through to D4.

Use a relative formula to calculate how much money was made from each item.

In cell C5, calculate the total items sold.

So for the first question equals B2 multiplied by C2, then use the fill handle to replicate it to B3 multiplied by C3 and B4 multiplied by C4.

So in cell five, to calculate the total item sold, you need equals sum.

And then in bracket C2:C4 for our range of cells, which is C2 to C4.

So here's the next part of the task.

So change the value in C4 to 50.

Once you've done that, what changed in the spreadsheet after you changed the value? And why did these changes happen? Well, what changed in the spreadsheet after you changed the value? So the total sales in D4 changed the 30 pounds and the total item sold in C5 updated to 93.

So why did these changes happen? So these changes happened because the spreadsheet used formulas with relative references.

When I changed the quantity, the formulas automatically used the new number.

Now let's have a look at our second learning cycle, which is to perform calculations with absolute cell references.

So we've seen relative formulas, they save time by changing the cell references in your formulas when you replicate them.

But Lucas has a really good point here.

But what if you don't want the cell reference to change? How do you do that? So sometimes you want to replicate a formula but keep part of it the same.

So let's have a look at this example.

If you use a relative formula, the cell reference will change when copied and the calculation will be wrong.

In this example, the VAT rate is stored once in cell B6.

If you use a relative formula in C2 to calculate the VAT amount, it will work correctly.

So if we multiply B2 by B6 we will get what we're looking for.

However, if you replicate the cell with the fill handle, it will fill in cell C3 with B3 multiplied by B7.

Why is that asks Sam? Well, it's because with a relative formula, the VAT rate reference is moved down from the column to a blank cell.

This is incorrect and gives us the wrong result.

So here you can see that B6 where the VAT amount is in the second example.

So in row three it's changed to B7 and B7 is a blank cell, so it's not working properly.

So with that in mind, what is a limitation of relative cell references? Is it A, you can't copy formulas with the fill handle.

Is it B, they don't let you calculate budgets.

Or is it C, they change cell references automatically, which may lead to incorrect results.

Well done, the correct answer is C, they change cell references automatically, which may lead to incorrect results.

So let's see what we can do about that.

We can use absolute cell references.

So an absolute reference is a way of keeping a cell reference locked, even when it's replicated.

You mark a reference as an absolute reference by adding dollar signs.

So here's an example.

So we could say equals B2 multiplied by dollar sign B dollar sign six.

This formula will always use the value in B6 even if the formula is replicated using the fill handle.

So there it is again equals B2 multiplied by dollar sign B dollar sign six.

So Sam points out the dollar sign in front of the letter locks the column.

And Andy adds the dollar sign in front of the number locks the row.

And Lucas' notice using both dollar signs locks both row and column, that's a full absolute cell reference.

Now using absolute cell references, the formula works correctly when replicated down.

So our absolute references are in there equals B2 multiplied by dollar sign B dollar sign six.

So that gives us 0.

16 for the VAT amount in C2.

And if we replicate that down, we get equals B3 multiplied by dollar sign B dollar sign six.

And that gives us the correct value in C3 and would also give us the correct value in D3.

So question from Lucas.

How do you know when to use a relative reference or an absolute reference? So here's when you should use both.

Use a relative reference when you want to copy a pattern and update cell references.

Use an absolute reference when you want to lock your formula to a specific cell in your spreadsheet.

Time for a question.

So which formula will keep using cell reference C1 even when replicated with the fill handle? Is it A, equals B two multiplied by dollar sign C dollar sign one? Is it B, equals B2 multiplied by C1.

or is it C, equals dollar sign B4 multiplied by C1? So which formula will keep using cell reference C1 even when replicated with fill handle? Well done, the correct answer is B2 dollar sign C dollar sign one.

So that's answer A, equals B2 multiplied by dollar sign C dollar sign one.

Let's move on to task B.

So download and open the spreadsheet file RSC seat sales.

So the first part of the task, students and people over 60 have discounted ticket rates.

Calculate the price for each seat using an absolute cell reference.

Then calculate the income from seats using relative cell references.

Next, add formatting to any values that should be currency.

And then explain why you used absolute cell references to calculate the discount.

Okay, so the second question was calculate the price for each seat using an absolute sell reference.

So that should look like this, equals F6 minus, and then in brackets, F6 multiplied by dollar sign B dollar sign seven.

So those are our absolute cell reference for cell B7.

The next part was to calculate the income from the seats using relative cell references.

So you needed three that you could copy down here.

So it's B13 multiplied by F6.

And if you drag that down, you get C13 multiplied by G6 and D13 multiplied by H6.

And you need equals sum, And then in bracket B20:D20, to get the grand total.

And the next part was to explain why you used absolute cell references.

To calculate the discount.

And you should have something like this, I used absolute cell references, so the formula would always use the correct discount.

The student discount was in cell B7 and I didn't want it to change when I copied the formula to other rows.

Using dollar sign B dollar sign seven, means a spreadsheet always used that same cell for the discount.

And here's what your spreadsheet should look like.

So the seating discount sections and the seating pricing sections here.

With the numbers correctly formatted as currency where they need to be.

And then your seats sold and income from seat sections should look like this.

Let's look at that solution in the spreadsheet itself.

So first of all, you can see that the formatting has been applied so that the headings are really clear.

So we've got a larger heading for the spreadsheet as a whole.

And then each subsection has a smaller heading each time there's a red background with white text in bold, so it really stands out.

Also, there's further formatting applied to the titles of each column.

So type discount here and section adult and student on the right hand side are also in bold.

So the formatting has been applied and we've got some currency formatting as well and percentage formatting on this spreadsheet.

So lots of lots of formatting has been applied.

So it's really clear what each section represents and is easy to read and understand.

So let's have a look at some of the formulas applied in this spreadsheet.

So first one was calculating the price after discount.

So here we have the adult price is 20 pounds.

So if we want to represent a student price with a 25% discount, we want that shown in cell G6.

So if we click in cell G6, we can see the formula that's been applied.

So we've got at the top equals F6 minus and then in brackets, F6 multiplied by dollar sign B dollar sign seven.

And this is a fixed cell here so that it won't change if we copy the formula down because we always want the student discount to relate back to cell B7.

And similarly for the over 60s, this time it calls cell B8 rather than B7.

But otherwise the formula is the same.

Let's just quickly look, if we change the price of an adult ticket, the other prices should change.

So I'm gonna change the price of an adult ticket to 25 pounds.

And then you can see that the student ticket and the over 60 ticket changes at the same time.

So we'll change that back to 20 so that we are consistent.

Okay, and then the other bit we wanted to look at was how the income from each seat is applied.

So first of all, we've got down here the total income for adults for premier.

So this is B13.

So cell B13 is the 12 seats sold multiplied by cell F6, which is back up here.

So F6 is the adult price.

So B13 multiplied by F6 gives us 240 pounds, which you can see here.

And then B14, F7 and then B15 multiplied by F8 gives us the income for the other type of seats.

So then in the student column we've got the same.

And over 60s we've got the same each time calling the correct price.

You'll notice that in these fields, you may see this in your spreadsheet, there's a little green triangle in the corner.

So this is saying that there's an inconsistent formula, so it doesn't necessarily mean it's wrong, but the spreadsheet's flagging that there could be something you want to have a look at.

So I'm just gonna click on help on this error and it says it's an inconsistent formula.

So it means that the formula in the cell doesn't match the patterns of formulas nearby.

So it's kind of expecting that it's gonna be related to the adult formula a bit more closely than it is.

Now in this case, it's correct.

So we're happy that it is the formula we want.

So it's not an error.

So you can actually click on ignore this error, and then the green triangle will disappear.

We'll do that for all three, which just makes the spreadsheet look a little bit neater.

And then the last thing we've got here on the right hand side.

So we've got some B20 to D20, adding all those totals up.

And then the same for B21 to D21 and B22 to D22.

And then finally a grand total at the bottom, which is the sum of the three totals in column E.

So that's sum E20 to E22.

So that's what your spreadsheet should look like and your formulas should look and how the complete spreadsheet should be.

Here's a summary of today's lesson.

Relative and absolute cell references are used in spreadsheet formulas to control how cell addresses change when copied.

A relative reference changes automatically when the formula is copied to another cell.

An absolute reference stays fixed on one cell even when the formula is replicated.

Using the correct type of reference helps ensure that formulas calculate correctly when using tools like the fill handle.

Thank you for joining me for this Oak Academy lesson.

I've really enjoyed learning with you today, and I hope to see you again in the near future.