Lesson video

Hello, my name is Charu, and welcome to this lesson four of six in data and information unit.

In this lesson, we will learn that data can be calculated using different mathematical operations.

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

You will also need access to spreadsheet applications.

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, get comfortable and when you're ready, press play.

In this lesson, you will apply formulas to data, including duplicating, recognise that data can be calculated using different operations, create a formula which includes a range of cells.

Also, you would apply a formula to multiply cells by duplicating it.

Spreadsheets can be used to perform calculations, including these operations, multiplication, subtraction, division and addition.

An asterisk is used as the multiplication operator in spreadsheets.

Because the asterisk is higher character on the key, you would need to hold the shift key down first to type it.

Same with the add sign.

As the add symbol is the higher character on the key, you would need to hold the shift key down first to type it.

As the minus sign is the lower character on the key, you do not need to hold down the shift key first to type it.

If you noticed here, the higher character on the skew also looks like a minus sign.

This is called an underscore.

This is longer than the minus sign and when you type it, it sits lower than the minus sign.

Forward slash is used as the division operator in spreadsheets.

As the forward slash is low character on the key, you do not need to hold the shift key first to type it.

Equals is the lower character on the key.

You don't need to hold the Shift key first to type it.

You will be practising using the four operations within formulas.

The spreadsheet you will be using has a number of questions you need to answer using cell references and mathematical operations.

Look at this spreadsheet here, the tabs on the bottom of the page show which operation you need to use.

You can click on different tabs to change the sheet and complete the different calculations.

To answer this question, what formula would you type into cell C1 here to answer this question? Have a think.

Yes, you will add the value in cell A1 and B1 to get the answer.

I will now demonstrate how to use mathematical operations to solve some questions.

There are four tabs along the bottom of the page, each referring to one of the different operations.

You can see we've got add tab, subtract, multiply and divide.

Let's start with the first tab, the add tab.

What formula would you type into cell C1? To answer 1246, add 352.

We want our answer in cell C1, so click on C1.

Remember, the formula starts with equal sign.

Now locate cell A1 for the first piece of data.

Now type the add sign by holding the Shift key and the add key together, as add sign is a higher key.

Now locate and click cell B1, for the second piece of data.

Press enter to get your answer in C1.

You can now go on other tabs and complete the calculations for each operations.

You now know how to do task one.

Can you follow this link? Click on the tabs along the bottom of the page and complete calculation for each operation.

Pause video now to complete your task and resume once you've finished.

welcome back, when you work with large amounts of data, it can be overwhelming.

There are functions and tools within spreadsheet applications that can support people in performing calculations using large amounts of data.

Functions in spreadsheet are represented by this sign.

It looks a bit like capital E, it is called Sigma.

Let's look at this spreadsheet here.

During this activity, you're going to practise creating formulas using the Sigma function button with a small amount of data.

Let's look at this data, a group of children's schools have been recorded over the six week half term.

The teacher awards a prize to the pupil who has the highest total over six weeks.

They also work out the average scores so that they can compare the scores with the children in other classes.

The teacher makes a spreadsheet to record the data.

Look here, the names of children and that's their weekly score.

Creating formula using a range of cells.

Within the Sigma button, there is a function called sum.

This enables you to calculate the sum or total of range of cells.

This is helpful when you have large amounts of data to add together.

I am now going to demonstrate how to use sum function and then duplicate the formula.

Click the cell where you want the answer to appear.

Now choose insert from the top menu, and move down and click function.

Move across to the menu on the right and select sum.

This will place the basis of the formula into the cell.

Now select the first cell to be used in the calculation.

Drag across the cells and select the cell holding the final number in the calculation.

The formula will be shown here.

Press enter to calculate the answer.

Now click on the cell containing the answer.

Hover over the bottom right corner of the cell, until the cursor changes from an arrow to a cross.

Click and drag down to the bottom of the data.

The formula will be duplicated and calculate the remaining answers.

Within the Sigma button, there is also function called average.

This will calculate the mean or average, of range of cells.

To work out the average, you add the numbers together to find the total, then divide that number by how many numbers there are.

I'm now going to demonstrate how to use average function and then duplicate the formula.

To find the average, please remember you should only select the weekly scores and not the total column, we are not selecting the total column.

Now click to send where you want the answer to appear.

Choose insert from the top menu, move down to function, move across to the menu on the right and select average.

This will place the basis of the formula into the cell, select the first cell to be used in the calculations.

Drag across the cells and select the cell holding the final number in the calculation.

The formula will be shown here, press enter to calculate the answer, click on the cell containing the answer, hover over the bottom right of the cell until the cursor changes from an arrow to a cross.

Click and drag down to the bottom of the data.

The formula is now duplicated and it has calculated the remaining answers.

During the previous activity, you used a small amount of data to calculate the sum and average of range of data.

And you also learnt how to duplicate formulas.

During this activity, you're going to use a larger data set to compete calculations.

You will need to work out which calculations to compete and how to do this.

This is a screenshot showing part of shopping spreadsheet that you will use as you will be dealing with more data.

And you might need to scroll down your page when you come to do your activity.

These are different data headings we have, item, weights in grammes, price, stock at the start of the week, stock at the end of the week, total stock sold, income sub-total.

How would you work out the missing values? Have a little think, I will give you some hints.

Total stock sold is going to be equal to stock at the start of the week.

Takeaway stock at the end of the week.

Income subtotal is going to be equal to price times stock sold, and total income here, that's going to be equal to sum of income subtotal column.

You might remember how to use this.

Use your newly learned skills.

Highlight a range of cells if you need to, use function and duplicate formulas if possible.

This brings you to your task three, which is last data set shopping spreadsheet.

You can follow the link here, fill in the missing values by duplicating formulas and also apply formula to a range of cells.

Enjoy, this brings us to the end of today's lesson.

Well done on your hard work during the lesson using functions and duplicating formulas.

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 would like, also share a picture of your work with Oak National on Twitter, so I can see your awesome spreadsheets too.

Bye for now, see you soon for lesson five.