Lesson video

Hi everybody my name is Halima.

I am your computing teacher, and I will be teaching you about Spreadsheets.

I hope you enjoy learning about spreadsheets as much as I enjoy teaching this as this is my favourite module.

In order to begin you, first of all, need to get permission from your parent, carer or teacher, if you are 13 years of age or below.

So please make sure you do this.

As you will need to create a Gmail account.

We are going to be using Google Sheets and that is what's going to act as our Spreadsheet.

So please make sure you have this prior to starting anything.

You will also require a pen paper to make notes with and possibly a calculator.

If you want to double check anything.

So let's get started.

Lesson five spreadsheets.

In this lesson, you will analyse data, use spreadsheets to sort and filter data.

And use the functions: Average, CountIf and If.

So, let's begin.

So what is Sort, Filter, Average, CountIf and If? Sort allows you to sort information, either ascending or descending, so in alphabetical order.

Filter allows you to find specific data.

Average is a function and is written as a formula, allowing you to find an average of a group of cells or data.

CountIf counts how many of a certain value there are, and If is where is written in a formula, allowing you to find a possible answer to a if question and I will be giving you examples of each.

Notice there is a screenshot from the previous lesson in lesson four.

This data that we created formulas for or functions from a previous lesson can also be used again in order to Sort, Filter, average, use CountIf and if.

Sorting data.

So data can be sorted in a particular order.

So as mentioned, you can have ascending A to Z, descending Z to A, and this can be done by the data that you want to sort.

And you can arrange this into a particular order that you would like to use.

Filtering, the symbol for the filtering is a Funnel.

So it looks like a Funnel.

It helps you to find specific data or information.

So if you have titles and it is advisable to have labelled titles at the top of your columns or rows for your data, so that you can identify and know what that row or column is about.

You can use a Filter in order to find specific information in that column or row.

So let's have a closer look.

So here is a demonstration of using Sort and Filter.

As you can see, I have two lows of data here.

I have numbers, I'll call it numbers one and numbers two, or in fact, instead of one and two, I can call it A and B.

This is the beauty of spreadsheet.

You can always go back and change things very easily.

So looking at lesson four, you can see I've got my total here that we learned about.

Okay, so I'm going to put numbers A column into alphabetical order A to Z, which is ascending.

So I highlight the data that I want ascending and I can right click and I can sort the range.

And this is one way that you can sort your data.

There is also a menu in here, which you could use to sort your data, but the easiest way is to right click.

Okay, so I wanted A to Z and I would like it to Sort.

Notice the column is column B and that is correct.

I can check that against my own spreadsheet.

It now has numbers in ascending order one, two, three, and four.

Let's do the same for number B column.

So I right click, I go to Sot Range, column C and sot.

I can always go back and change this to make it A to Z, or I can use the Undo button to go back to how it was originally.

I can also Redo to go back to how I had it.

So don't forget those tools.

Now I have sorted my data, let's filter our data.

So I'm going to highlight the column headings as can see.

And this is why it's important to have headings.

And there is a Funnel here.

I'm going to click on the Funnel.

It will now allow me to filter the data.

So in A I'm going to untick all these numbers because they're selecting the data I have.

And I'm only going to look for number two, click, Okay.

Notice only number two comes up and it shows you in that row.

What are the numbers are associated with that.

Do not forget when you use filters and you are searching for data, and then you want to search data again, you undo what you have just done.

Otherwise you will put two Filters in, you will filter to find the first information and you'll end up filtering that first information to your second and it can become confusing.

So make sure you undo the Filter you've done in order to do your next question.

So let's have a look at number B, I'm going to filter this, and I'm only going to have number three, there you go, just as an example, if you ended up filtering and filtering again, this is what it will look like.

So make sure you undo to start from the beginning.

And that is how you sought and filter your data.

Let's have a look at using our functions now.

So here I have another spreadsheet.

I'm going to do an Average formula, which is a function.

Now, this starts with an equal sign as all the formulas, and it has automatically detected as a computer, what it is I want to create an average on.

I'm going to type average, open brackets do not forget your open brackets.

This is something we have learned also in our last lesson, which was less than four.

Close bracket and I'm going to do a CountIf formula.

Just before I do the CountIf formula, just to remind you that in average, you have to make sure that you type the word in first, you have your brackets, and then you select your range of cells that you would like for the computer to do an average on.

So let's have a look at the CountIf formula now.

So to start with a CountIf formula, I type equals as it is a formula.

I type the word CountIf followed by an open bracket, as we have been with all the other functions and formulas.

I select the range that I am looking into.

And here it is D four to D nine.

I then use a comma.

So the comma separates the range that I'm looking into according, and then what I am looking for.

So here, I'm going to look for number two, but just to repeat equals CountIf open bracket ranges D four to D nine and in there, which is the comma to separate, I am looking for the number two, press Enter.

The computer tells me or the function here tells me there are four number twos.

One, two, three, four.

That's correct, and yeah there's a three.

Okay, I'm now going to add my If formula.

So equals If open bracket and I'm going to select my first cell that I want the computer to look up, which is D four.

If formulas are very good, when you're looking at two possible answers, such as true and false.

Here I have got some exam marks.

And what I'm doing to do is create an if formula.

So if a student has a certain mark, they will get good.

Or they will be asked to try that exam again.

So if my D four value, which the student received, is equal to three, and sometimes you can use other symbols such as more than, and we know this as a crocodile symbol going to the right or less than, which is a crocodile symbol to the left, but I'm going to use an equal sign.

So if D four is equal to three, then so the word then is noted as a comma to separate.

I want the computer to say, well done, comma again to separate, else I want it to say, try again, but just to repeat what my formula does here.

The If formula is when you have two possible outcomes and it's very good at checking information.

So here I want the computer to check what my student's marks are.

If they receive a grade three or three marks, I would like the computers to say, well done.

But if it is less than three, it will say try again or any other number, which is not three.

So try again, let me use the auto filter.

As you see it works.

Now I'm going to get rid of what I've just filtered to show you something.

At the moment my formula is looking at only at number three.

So if I get number three, it will say, well done.

What if I get four marks, will it still say well done? Or will it asked me to try again.

I'll give you some time to think.

It would in fact, say, try again.

If you did get that correct, well done.

It's because I'm using an equal symbol here.

If I say equal and more more than, so I can use two symbols in this instance, because I would like the answer to be three or more.

Then I would like it to say well done and try again.

This way if my student achieves a four, five, six, seven, eight, nine, 10, any number above three, or three, they will say, the computer was generated and say, well done.

And that's what this formula will do.

So let's have a look, there was a bit of an error, but it's more than or equal to three, there we go.

So I had my symbols, the wrong way around there.

I did equal or more than they should actually be more than and equal.

That's the beauty of spreadsheet.

If things go wrong, you can always go back and change it.

So let's filter this down and it works.

Okay, let me change this student's mark two four.

And it says, well done.

Let me change this student's mark to one.

And it says, try again.

You could do the same here.

So where I have the letters, A, B, C, D, E, F.

It just tells you what A, B, C, D, E, F is so this is student A, student B, student C, students D, student E, student U.

If you were using not numbers, but you were using letters, as your exam marks so say I was using A or B as an exam mark.

I could use a formula which looks at letters rather than numbers.

And you can use If formulas for these as well.

But when using, If formulas with words, which are not numbers, notice here I have used quote marks very important because a computer does not understand anything apart from after what a number is.

So it knows that a number is a number.

It's two, it's three, it's four, but the word well done it doesn't know what that is.

And therefore you have to put quote marks in front of that word.

And after that work to end it, otherwise your formula will not work.

So I hope that gives you a good explanation of average CountIf and If.

And we can try and use this in our task later on.

Let's pause the video here to complete our task in the worksheet.

This is task one, practising to use Average, CountIf and If functions as formulas.

So let's have a look at the answers.

This is what you should have as an answer for your task one.

Well done if you got this correct.

Let's have a closer look.

So here I have a formula, which says D four is more than or equal to three, well done, else, try again.

I've used the auto fill to copy my answer down.

Average is equals average open bracket, select the cell range here it to D four to D nine and close bracket.

And CountIf is equals CountIf open bracket D four to D nine.

And I'm looking at the value number two.

So how many number twos are there? Count and that's what this formula does.

Well done again, if you did get this correct.

Let's pause this video to have a good go at task two in our worksheet.

Sorting and filtering.

So let's have a look at the answers for sorting and filtering.

In this task, you were asked to sort and filter the data that you had just completed for task one.

Now, you may notice that my data is actually already sorted, starting from number two to number three.

It's actually more about practising.

If your data was the same, you could do the same to practise.

For the task, I've asked you to put it from A to Z.

My data is already in A to Z, so I'm going to sort it the other way from Z to A, there we go.

I'm now going to try and sort it back, to A to Z.

So it's more about practising.

Filtering out data.

Do you remember what symbol we have to use in order to filter our data? Let me give you some time to think.

Yes, it is the Funnel, if you remember.

And when I click the Funnel, I am able to select data.

So I'm going to select all the data, which has a number two.

There we go.

I can see student A, B, C, D have a filter for number two.

I'm going to undo my filter, and then you can try doing a filter for another one.

So let's see if I can do a filter for this range.

I want to see all the students who did very well, and it shows me there's two students did very well.

And that's how you could use sorting and filtering.

Remember for this task, you were asked to decide on your own sort and filter.

So well done if you were able to use sort and filter in your task.

why not share the good work that you've been doing with Oak National? You can find us on Facebook and Twitter and Instagram.

Just make sure you get permission before you post anything.

We really look forward to see your work.

Thank you.

Bye bye.

And see you again soon.