KS3 Year 1 – Spreadsheets Final Assessment

KS3 Year 1 – Spreadsheets Final Assessment

This assessment is the final task for you to complete at the end of your spreadsheet topic. This quiz will only be available once you have completed all five lessons in the spreadsheet topic.

This assessment is only available to complete once, so please make sure that you have plenty of time to complete the whole assessment.

This assessment uses a timer for an hour (after an hour, your answers will be automatically submitted).

Week 5 – Functions To Find Winners!

Week 5 – Functions To Find Winners!

Activity 1

Your final practical task for this topic is to use the COUNTIF() and IF() functions to check for a wining word. It’s up to you what you want to output when you find a correct word, but make sure that your player notices!

Workbook 1

  • A COUNTIF() function to count how many cells in each row contain the word “GREEN”
  • An IF() statement that outputs a winning statement if 5 of the cells are green

Extension Activity

As a fun extra this term, I’ve added a short Javascript exercise to the end of the topic to create a “next word” button.

Workbook 1

  • Go to Apps Script in the Extensions menu & create a new function
  • Create the code below to increase the counter on the main worksheet by 1
  • Create a button by inserting a drawing into the main worksheet
  • Use the three dots on the right of your drawing to assign your script to the button
Week 4 – Conditional Formatting

Week 4 – Conditional Formatting

Activity 1

Transpose your “colour words” from the testing area to the main game table.

Workbook 1

  • Use an = to show the value of the testing table (with the huge IF statements) in the main game table
  • Reduce the size of the font in the table so that the words show fully
  • Correct any borders that are changed by mistake (there’s an example of this in the video)

Activity 2

Set up conditional formatting to show the player only colours for each guessed letter.

Workbook 1

  • Set up a rule for cells with the word “GREEN” to have a green fill and green coloured text
  • Reduce the size of the font in the table so that the words show fully
  • Correct any borders that are changed by mistake (there’s an example of this in the video)
Week 3 – IF Statements in Spreadsheets

Week 3 – IF Statements in Spreadsheets

Activity 1 (before the lesson)

This week is using an IF statement. This uses the same logic as an IF statement in programming, but often looks more complicated as there is no ELSE.

Your task this week is to continue building your Wordle..ish spreadsheet by testing out your guesses and assign a colour to it. As this is quite complex, I would like you to try:

Workbook 1

  • An IF statement that sets a correct letter to the word “GREEN”, and otherwise “GREY”

Activity 2 (after the lesson)

Now that we’ve looked at your spreadsheet in the live lesson, update your own spreadsheet so that it also includes:

Workbook 1

  • An IF statement for every letter guess showing GREEN, ORANGE, and GREY
    • The flowchart below gives you the logic for each cell:
Week 2 – Using Functions in Spreadsheets

Week 2 – Using Functions in Spreadsheets

In this lesson we’ll be selecting a range of cells – when you select cells in a spreadsheet, they’ll be highlighted by a dotted line. Quite often, these are referred to as “ants” (when you copy the cell, they start marching!)

Activity 1 (before the lesson)

Your task this week is to continue building your Wordle..ish spreadsheet using the techniques from the video. Your sheet should contain:

Workbook 1

  • A number that will be used to access the word of the day
  • Try writing the VLOOKUP() function that shows the word from your list on the main workbook

Activity 2 (after the lesson)

Now that we’ve looked at your spreadsheet in the live lesson, update your own spreadsheet so that it also includes:

Workbook 1

  • A “substring” that splits the word into different cells
    • To do this, try the MID() function

Week 1 – Table Formatting in Spreadsheets

Week 1 – Table Formatting in Spreadsheets

Activity 1

Your task this week is to set up your own version of the Wordle..ish game using Google Sheets. Your sheet should contain:

Workbook 1

  • Three separate table areas with titles and borders as shown in the video (and image below)
  • A merged and Centred heading for the game area
  • The titles should be formatted with readable fonts and colours

Workbook 2

Once you have set up your sheet, share your work with your tutor by using the green “Share” button:

Next, type in your tutor’s email address, add a message, and press “send”.

Holly: holly@teachallaboutit.uk

Jay: jay@teachallaboutit.uk

Half Term 5 Study Plan (Spreadsheets)

This lesson will introduce you to what you should be completing each week, your live lessons, and how your tutor will assess you.

This half term, we will be looking at spreadsheets and creating your very own version of a famous word game to help with spelling and literacy!

The table below is a suggestion of how to access the lessons and organise completing the tasks each week.

This half term, you’ll be creating your spreadsheet over 5 weeks and there is a final online quiz on week 6.

WeekBefore your lessonLive Lesson!After your Lesson
11. Watch the video in the lesson “Creating & Designing A Spreadsheet”

2. Try the practice task on formatting cells.
Join in with Holly & set up your spreadhseet!Complete task 1 to complete your word list and the Introduction to spreadsheets Quiz
21. Watch the video in the lesson “Using Functions”

3. Try the practice task on looking up data in your word list.
Join in with Holly as we look at how to use spreadsheet functions to organise data.Complete the tasks set for:
– Putting your words in alphabetical order
– Using VLOOKUP to get your word list
3 1. Watch the video in the lesson “IF Statements”

3. Try the practice task on testing our your game guesses.
Join in with Holly as we look at how to use an IF statement to check if a letter is in another word Complete the tasks set for checking all guesses and the Cells & Logic Quiz
4 1. Watch the video in the lesson “Conditional Formatting”

3. Try the practice task on setting colours for your guesses.
Join in with Holly as we look at how to use conditional formatting to highlight correct & nearly correct guesses Complete the tasks set for green, orange, & grey cells and the Conditional Formatting Quiz
51. Watch the video in the lesson “Functions To Find Winners”

3. Try the practice task on outputting a win.
Join in with Holly as we look at how to use another type of IF to check if our player has won! Complete the tasks set for checking for a winner and try out the final button extension
6Assessment (Online Quiz)No Live Lesson