🎯 Learning Objectives
- Analyse data using the skills from the last lesson.
- Use a spreadsheet to sort and filter data.
- Use the functions COUNTIF, AVERAGE, and IF in a spreadsheet.
💬 Key Vocabulary
- header
- filter
- average
- criterion/criteria
- condition
- conditional formatting
📖 Sorting & Filtering
Being able to sort and filter data is a really useful ability that spreadsheets have compared to data that is on a piece of paper.
Open the Excel file of the data you collected in Lesson 3, your teacher will show you how to sort the columns of data using the headers as well as using filters to only select data we want to see.
📖 More functions
AVERAGE
Superpower: calculate averages in an instant
=AVERAGE(B2:B15)
This means calculate the average (mean) of all the cells between B2 and B15.
COUNTIF
Superpower: count how many cells meet your condition
=COUNTIF(B2:B15, “<10”)
=COUNTIF(B2:B15, “Blue”)
This means show how many cells are less then 10 in the range B2:B15 or show how many cells contain “Blue” in the range B2:B15.
IF
Superpower: have a cell show different things depending on a condition
=IF(C2>10, “Hooray!”, “Too low”)
This means if cell C2 is more than 10 then display “Hooray!” else display “Too low”.
📝 Level 1 – Analyse your data even more!
Two lessons ago you created a survey using Microsoft Forms and collected data from your classmates. Last lesson we started to analyse that data, open the Excel spreadsheet with the work you did for Level 1 last lesson. We are going to use some new functions and formulas to analyse the data even more.
If you were absent for the last two lessons or did not manage to collect any useful data, then download the Excel file from the Level 1 task in Lesson 3 which includes data you can use.
Once you have your data you need to use the functions listed above as well as filters to analyse and visualise your data. Your teacher will show you some examples of what you could do.
You must use the three new functions shown to you today (COUNTIF, AVERAGE, IF) as well as sorting with headers and filtering.
📝 Level 2/3 – Analysing other people’s data
For this task you need to choose one of the three sets of data below to analyse. Once you have chosen one you should download the data file and the worksheet for that data.
You need to follow the instructions in the worksheet to complete Level 2 and 3. Once completed upload your Excel spreadsheet to Teams.
In this lesson, you…
- Analysed data
- Used a spreadsheet to sort and filter data.
- Used the functions COUNTIF, AVERAGE, and IF in a spreadsheet
Next lesson, you will…
- Use conditional formatting in a spreadsheet
- Apply all the spreadsheet skills covered in this unit
🏅 Level up
🥇 Level 1
- Use the three new functions shown to you today (COUNTIF, AVERAGE, IF) as well as sorting with headers and filtering on the data you collected in Lesson 3. Upload the Excel spreadsheet to Teams when completed.
🥈 Level 2
- Complete the Level 2 tasks on one of the sets of data listed above. Upload the completed Excel spreadsheet to Teams when finished.
🥉 Level 3
- Complete the Level 3 tasks on one of the sets of data listed above. Upload the completed Excel spreadsheet to Teams when finished.