Hello, and welcome to Computing.

My name is Mrs. Holbrite.

I'm so pleased you've decided to join me for the lesson today.

In today's lesson, we are going to use spreadsheets.

We are going to use sort and filtering tools to organise data and use some functions to analyse data.

Welcome to today's lesson from the unit Data Modelling.

This lesson is called Sorting and Searching Data.

And by the end of today's lesson, you'll be able to use spreadsheet tools to sort and search data to find and analyse information.

You'll need to have access to spreadsheet software for this lesson.

Shall we make a start? We will be exploring these keywords during today's lesson.

Sort.

Sort: arranging data into a meaningful order.

Filter.

Filter: given criteria used to show a subset of a data set.

Function.

Function: a predefined formula that performs a calculation.

There are two main parts to today's lesson.

We'll start by using tools to filter and sort data in a spreadsheet, and then we'll move on to use functions to analyse data based on a condition.

Let's start by using tools to filter and sort data in a spreadsheet.

Applying a sort to data makes it easier to identify key pieces of information.

Here I have some data about the Great Britain Summer Olympic medals between the years 1992 and 2021.

I've then applied a sort to this information, and I've sorted it by the total medals from highest to lowest.

And this means it's now really easy for me to see that in 2016, Great Britain won the most medals, followed closely by 2012 and 2021.

By sorting the data, it's easier to identify these key pieces of information.

With a sorted data set, you can easily see the year in which the most medals were won.

Here is a demonstration of how a sort can be applied to a set of data.

We highlight the column headings and go to Data.

We then go to Sort range and Advanced range sorting options.

We tick "Data has a head a row" because we've got some headings at the top of the columns.

We are then sorting by the column view from Z to A, which means that it will sort from highest to lowest.

A filter can be applied to data to remove selected data from a data set.

A filter helps you to only see what you want to see by hiding everything else.

The filter doesn't permanently remove the data, and when the filter is removed, the original data will still be there.

Here is a demonstration of how filter can be applied to a set of data.

We select the data, go to Data, and say Create filter.

Each column will then have the filter icon next to it, and we can decide what we want to filter the column for.

In this example, we are filtering for the grades A+.

So when we apply the filter, only the grades with A+ remain in our table.

All of the rest of the data is temporarily hidden.

If a filter is applied to a cell, it must be removed before another filter is applied.

Time to check your understanding.

This list is displaying all the items in another order than they were originally displayed.

What tool was used to do this? Was it A, sort, B, filter, or C, function? Pause the video here whilst you have a think.

Did you select A? Well done.

A sort has been applied to this data so that the items are displayed in a different order than they were originally.

Which icon would you select to apply a filter to a range of cells? Is it A, B, or C? Pause the video here whilst you have a think.

That's right.

The correct answer was C.

The filter icon in most spreadsheet software looks a little bit like a funnel.

We are now moving on to our first set of tasks for today's lesson.

For Task A, Part 1, download the Olympic medal spreadsheet.

The data is currently sorted by rank.

For Part 2, select all of the columns and sort the data alphabetically by country name instead.

For Part 3, try sorting the data by each medal category.

So gold, silver, bronze, and total.

Can you work out which category the rank number is based on? Pause the video here whilst you complete the activities.

How did you get on? Did you manage to use the sort tool? Great work.

So the data was currently sorted by rank.

You were asked to select all of the columns and sort the data alphabetically by country name instead.

So you can see here, you should have Algeria at the top of your list, followed by Argentina, and then Armenia and so on.

If you applied the sort correctly, the other data in the columns should have shifted at the same time.

So for example, Algeria's rank, 63, should still be next to it in its row.

You were then asked to try sorting the data by each medal category, gold, silver, bronze, and total.

And you were asked to work out which category the rank number is based on.

The rank number is based on the total number of gold medals.

The country with the most gold medals is ranked in first place.

Did you get that correct? Remember, you can always pause the video here and make any corrections if you need to.

You're doing a great job.

So, well done.

For Part 4, I'd like you to use filters to display A, the countries that won no gold medals, B, countries that won a total of one medal, and C, the top 10 ranked countries.

And there's a hint here.

Your filter condition should look for a rank of less than or equal to 10.

Remember to remove a previous filter before applying the next.

Pause your video here whilst you complete the activity.

How did you get on? Did manage to apply some filters to your data? Well done.

Let's have a look at what your data would've looked like after your filters.

Okay, for Part A, you were asked to filter by countries that had won no gold medals.

So here the filter has been applied to the column containing gold medals.

And you can see all of these countries have zero gold medals.

So that's Malaysia, Mexico, Venezuela, Algeria, Ireland, Lithuania, Bulgaria, and India.

For Part B, you are asked for countries that won a total of one medal.

So you can see this time, the filter has been applied to the total column, and the medals in that total column are equal to one.

So all of those countries had a total of one medal.

For Part C, you were asked to apply a filter to look for a rank of less than or equal to 10.

So you can see here that the countries remaining all have the rank between one and 10.

So we've got United States, Great Britain, China, Russia, Germany, Japan, France, South Korea, Italy, and Australia.

We are now moving on to the second part of today's lesson where we're going to use functions to analyse data based on a condition.

Do you remember these functions? What do they do? Maybe pause the video here and have a think.

The SUM function returns the total value of a range of cells.

The MIN function returns the lowest value in a range of cells.

The MAX function returns the highest value in a range of cells.

And the COUNTA function returns the total number of non-empty cells in a range of cells.

Sam's got a question.

"But, what if I want to do some more analysis like find out the average of a range of cells?" Jim says, "Or count the number of cells which contain a certain piece of data?" These are really good questions.

Let's introduce some more functions that can be used to analyse data.

The AVERAGE function.

The AVERAGE function returns the average value in a range of cells.

This is the mean of the range of cells.

Similar to how we've done functions before, we start with the equals.

We then write the name of the function, which is in this case average.

We open our brackets.

We select the range of cells that we want to apply the function to, and then we close the bracket and hit Enter.

So this function will return the average value held in the cells A1 to A10.

Here's another function, the COUNTIF function.

The COUNTIF function returns the total number of cells that meet a set criteria.

So here we have COUNTIF the range B2 to B15, and then we have less than 10.

And then we have another example, which is COUNTIF range B2 to B15, which is Blue.

So the COUNTIF function can be used to check criteria containing numbers and text.

You'll note that this function, unlike the ones we've seen previously, has two arguments.

We have the cell range, like we have normally, followed by a comma, and then the condition.

So the condition is what we are checking for.

So this example will return the total number of cells that contain a value less than 10.

The IF function.

The IF function will return different things depending on criteria.

So here's an example.

We have = IF.

We have the condition.

So we're saying is C2 or the value held in C2 greater than 10? We have a comma, and then we have the text "Hooray!" And then we have another comma, and then we have the text "Too low." And then we've got our closed brackets.

So this IF function will display "Hooray!" if the condition C2 greater than 10 is True, or it will display "Too low," if the condition C2 greater than 10 is False, time to check your understanding.

Which function would you use to display "Pass" in a spreadsheet if the cell holding a test score is greater than 40 or "Fail," if it's not? Is it A, AVERAGE.

B, COUNTIF, or C, IF? Pause the video here whilst you have think.

That's right.

C, is correct.

The IF function could be used in this example.

And that's because we are checking a condition, and we want two different things to happen.

If the test score is greater than 40, we want it to display "Pass." If it's not, we want it to display "Fail." This time I'd like you to put the blocks into the correct order to build the IF function.

So you have all the blocks, but they're currently in the incorrect order.

Pause the video here whilst you have a think.

How did you get on? Here's the correct order.

We start with the equal symbol, and then we have the name of the function, which is, IF.

We have open brackets, the condition, comma, the value if True.

So what's gonna happen if the condition is True? Another comma, the value if False, and then our close brackets.

Did you get that in the correct order? Maybe pause the video here and make a correction if you need to.

You're doing a great job so far, and we've learned lots and lots of new functions.

So, well done.

We are now moving on to the final set of tasks for today's lesson.

For part one, open the Olympic medal data spreadsheet that you used for Task A, but this time go to the tab, "analysing the data." For Part 2, use the COUNTIF function to count how many countries won: A, fewer than 10 medals in each category, B, exactly 10 medals in each category, and C, more than 20 medals in each category.

Pause the video here whilst you complete the activity.

For Part 3 use the AVERAGE function to find the average number of medals won in each category.

For Part 4, use your knowledge of other functions to find: A, the most medals won in each category, B, the fewest medals won in each category, and C, the total medals won in each category.

For Part 5, in the Result column, use the IF function to display the text "Hooray!" if the total number of medals is greater than 10, or "Too low" otherwise.

Pause the video here whilst you complete the activity.

How did you get on? Did you manage to use lots of different functions? Great work.

So for Part 2, you were asked to use the COUNTIF function to count how many countries won A, exactly 10 medals in each category, B, fewer than 10 medals in each category, and C, more than 20 medals in each category.

For A, you should have the following results.

So the number of countries that won exactly 10 medals was one for gold, one for silver, and three for bronze.

For Part B, the number of countries with fewer than 10 medals was 79 for gold, 78 for silver, and 75 for bronze.

And then for C, the number of countries with more than 20 medals was three for gold, two for silver, and three for bronze.

If your totals don't quite match these, maybe go back and have a look at your COUNTIF function.

For Part 3, you are asked to use the AVERAGE function to find out the average number of medals won in each category.

You should have had these results.

So for gold it's four, for silver it's four, and for bronze it's four.

And your total should be 11.

You were then asked to use your knowledge of other functions to find A, the most medals one in each category.

So hopefully you use the MAX function for this, and you should have the following results.

46 for gold, 37 for silver, 38 for bronze, and the total 121.

For the fewest medals for each category, you should have used the MIN function, and you should have had zero for gold, zero for silver, and zero for bronze.

And for the total it should have been one.

And then for the total medals, Part C, you should have used the SUM function, and you should have hopefully had 307 for gold, 307 for silver, and 359 for bronze.

And the should have been 973.

For Part 5, you were asked to use the IF function to display the text "Hooray!" if the total number of medals is greater than 10 or "Too low" otherwise.

So you can see we've got the function = IF, open brackets, F2 greater than 10, and then we've got a comma, and then the text "Hooray!" in speech marks, and then another comma, and then the text "Too low," and then we've got our closed bracket.

You've done a fantastic job in today's lesson, and you've learned lots of new tools and functions in spreadsheets.

So well done.

Let's summarise what we have learned.

A sort or filter can be applied to a dataset to make it easier to analyse and visualise information.

A function is a predefined formula in a spreadsheet that can help with calculations.

The AVERAGE function returns the average value in a range.

The COUNTIF function returns the total number of cells that meet your criteria.

The IF function will show different things, depending on a criterion.

I hope you'll join me again soon.

Bye.