Lesson video

Hi, I'm Charu.

This is Lesson 3 of 6 in Data and Information Unit.

In this lesson, we will begin to use formulas to produce calculated data.

For today's lesson, you will need a pen and paper.

You will also need access to a spreadsheet application.

You can use Google Sheets or alternative software such as Microsoft Excel or Purple Mash to calculate.

Please take a moment now to clear away any distractions and find a comfortable place to work.

Please pause the video and get comfortable.

And when you're ready, press Play.

In this lesson you will explain that formulas can be used to produce calculated data.

Explain the relevance of a cell's data type Construct a formula in a spreadsheet.

And finally, identify that changing inputs, changes outputs.

Computers deal with different types of data in different ways.

What types of data items can you think of? We talked about these in Lesson 2.

They are words, numbers, dates, images and sounds.

Some data types can be used in calculations but the others cannot.

Can you multiply a number by a number to answer a calculation? Yes, you can.

Can you multiply a piece of text by a number to answer a calculation? No, we cannot.

Sometimes people might say, "Jump three times." As humans, we would understand this, but computers would not.

Computers cannot multiply a word by a number.

Can you multiply an image by a number to answer a calculation? No, we cannot.

When you use spreadsheets, you can only use numerical data items to form the basis of calculations.

And the data needs to be entered in a suitable format that the computer understands.

Spreadsheets are made up of columns and rows.

You can remember this by thinking of architectural columns that runs top to bottom and rows in the cinema that runs from side to side.

Each of the boxes that makes up a spreadsheet is called a cell.

Cells have a unique reference.

This allows us to say where a particular piece of data is stored.

You can find out a cell reference by using the letters along the top of the column, and numbers down the left-hand side of the rows.

The word Name is in cell A1, can be referred to as cell we always put the letter before number and say, A1 and not, 1A.

What cell is the number 13 in? Take a moment and think about it.

Number 13, is in column B and row 3.

So the number 13 is in cell B3.

Let's see if we can answer some questions on cell referencing.

Which cells have a tick in them? Let's look at this first tick.

This tick is in column B and row 2.

So the cell reference for this tick is B2.

This cell is in column C and row 4.

So the cell reference for this tick is C4.

Can you tell me the cell reference for this tick here? That's right, it's E7.

In a spreadsheet you can create something called a formula.

A formula can tell a computer which mathematical operation to use for a calculation; add, multiply, divide, or subtract.

It also tells the computer which pieces of data to use within the calculation.

We will be using the spreadsheet from the last lesson to do some calculations and we will be using multiplication.

The spreadsheets don't understand the cross sign for multiply.

Instead, they use an asterisk.

Do you know how to type an asterisk on the keyboard? We use the shift key and the eight key together to type an asterisk on the keyboard.

This is the spreadsheet that we multiplied in our last lesson.

The data headings that we used were attraction, location travel duration, distance in miles and cost per mile.

Last week, you added the cost of petrol as 12p per mile.

And we will use this data to work out how much it would cost you to travel to each of these attractions from school.

How would you do that? Have a little think.

You will need to look at distance in miles and then multiply that by cost per mile to give you the journey cost to each of this location.

When you want to do a calculation in a spreadsheet you can use the cell reference.

You will look at the cost of petrol to Alton Tower.

You need to look at the cell reference of the calculations that you want to do.

So, distance in miles for Alton Towers is 86.

1 and cost per mile is 12p.

So we are looking at the cells D2 and E2.

I am now going to give you a demonstration on how to input formula in a spreadsheet.

To construct a formula, type equal to in the cell where you want the answer to appear.

Now, click to select the cell holding the first number of your calculation.

Now write the asterisk sign for multiplication using Shift+8 key.

Now click on the second cell.

Notice how the cell reference appear in the box here.

Now, press Enter, and your answer will appear in the cell.

You can continue to enter your formulas like this.

Equal to sign, click on the first box for calculation.

Asterisk sign, second box of your calculation, press Enter.

We could carry on doing this for all the attractions or you could click on the cell containing the formula, click on the cell, hover over the bottom-right corner of the cell until the arrow runs into a cross.

Click and drag down to the bottom of the column here just to place the answers into each of the cells.

Now this brings us to Task 1 on Inputting Formula.

Can you follow this link, input the formula and use them to calculate the cost of each trip.

You can now pause the video, do the task and resume once you've finished.

Welcome back.

Did you wonder why did we use the formula? Why did we not use the numbers? Why did we not say 86.

1 times 12p? We are going to look at the two ways of doing calculations.

One, which uses numbers and others that uses cell references.

I am now going to demonstrate whether to use numbers or cell reference for your calculation.

You could calculate the journey cost by typing in the numbers rather than the cell reference.

Let's try and do that here.

Equal to sign.

Use numbers, 86.

1 times 0.

12, press Enter, you get your answer.

Let's do that for another cell, equal to 207 times 0.

12, press Enter, we still get the right answer.

Now let's try using formula equal to click on the cell you need for calculation, times, click on the second cell you need for calculation, press Enter, you get the answer.

Let's do that for another cell using cell reference equal to click on the cell you need, times it by click on the second cell you need, press Enter.

You get answers, right answers by both these methods.

Selecting data using cell reference can be quicker than typing in each number.

Now imagine the petrol cost went up to 14p per mile.

Let's change it here.

So let's say, 14p and drag to make the changes for all the cells.

Now you would notice what happens to the answers in the journey cost.

The calculations using cell references changed, the calculations that use the numbers stayed the same.

So what do you think is better, to use numbers or cell reference for your calculations? In the demonstration did you notice why do the formulas with cell references updated, but the formulas using just the numbers did not? Within a calculation the cell references refer to the cell holding the data.

If the data input into the cell changes then the output of the calculation changes too.

Petrol prices change frequently.

This will change the cost per mine.

Imagine that the petrol prices has gone up to 2 pence, can you change the data in your spreadsheet to reflect this? This brings us to Task 2.

I would like you to go back to your spreadsheet change the data into your spreadsheet to reflect the change in petrol prices.

You can pause the video now, complete your task and resume, once you've finished.

Welcome back.

This is how your spreadsheet should look like once you have made the changes to the petrol cost.

Obviously, my numbers would be different from your numbers, because the distance from where I started would be different to distance from where you started, hence we would have different journey cost.

That brings us to the end of today's lesson.

Give yourselves a pat on your back for using formulas and spreadsheets.

Well done, on all the awesome learning you have achieved today.

I'd like you to look back at your notes today and identify the most important thing that you have learned in this lesson.

Also, if you're able to, please take a picture of your work and ask your parent or carer to share it with your teacher.

And if you'd like, also share a picture of your work with Oak National on Twitter so I can see your awesome spreadsheets too.

Take care and enjoy the rest of your learning for today.

Bye for now.