Lesson video

Hi, I'm Kash your computer science teacher, for the spreadsheets unit.

Lesson two is all about using conditional formatting and data validation.

For this lesson, you going to need a pen, some paper, and you going to need to remove any distractions that are going to get in the way of you focusing.

Once you've done this, let's begin.

In this lesson you will, use common formulae to model sales and income from an event, use formatting to make the spreadsheet readable and to highlight different areas, for example, seats available or income, and use data validation when entering data, in order to reduce user error.

So as a starter here always think about, do you know what spreadsheets are used for in the workplace? Now, spreadsheet skills are sought after by employers.

So job adverts often specify the applicants must be able to demonstrate competency in using spreadsheets.

On on the next slide here, we've got some examples of the criteria that they're looking for.

So sound IT skills in Microsoft Office or Excel.

And on the right hand side, we've got ideal candidate will be computer literate in Excel, Word, and so on.

So these are all from reed.

co.

uk.

So your first task, pause the video to complete your task.

Who uses spreadsheets? Using the worksheet and the spreadsheet, starter activity, complete task one.

Resume once you're finished.

Starter solutions.

So here I've got the solutions I've come up with.

Now there were various options here.

So there was more than one correct answer.

So for example, for the small business, they can use spreadsheets for estimates for jobs, but also from this list, they could potentially use it for calendar as well.

Put it in there, it's also correct.

However, the small business might not use it for test scores.

Right, it's not appeared.

So that was a really useful task to get you thinking about how businesses, how people use spreadsheets, and the use of spreadsheets as well.

Modelling the Rock Star Challenge Live event.

We're going to be using the spreadsheet Live event seating.

Let's take a look at the sections.

So on the right hand side, we've got the stage which is broken down into premier, first class, and standard seating.

Below that we've got the seating codes, so adults, students or over 60s could take a seat there.

They've got their individual code and the amount of discount that they receive.

On the right hand side, we've got the prices that they're paying, and below that we've got the income from the seats as well.

Your first task is to add seating prices.

By only having the seat prices in one place, you can easily change them if needed.

So students pay 25% of adult price.

Enter a formula to calculate this.

And over 60s pay 75% of the adult price.

Enter a formula to calculate this.

Pause the video to complete your task.

Task two, add seating prices Using the worksheet and the spreadsheet, RSC Live event seating, complete task two.

Resume once you're finished.

Task 2, add seating prices-solution.

So as you can see there, on the right hand side, they're the prices that you should have got.

Conditional formatting.

Conditional formatting is a feature in many spreadsheet applications, that allows you to apply specific formatting to cells that meet certain criteria.

We're going to use it for color-based formatting, to highlight seats, that have been taken in the RSC spreadsheet.

Pause the video to complete your task.

Task three, conditional formatting.

Using the worksheet and the spreadsheet, RSC Live event seating, complete task three.

Resume once you're finished.

Task three, conditional formatting-solution.

So what you should have got here, was each of the sections, premier, first class and standard, they've all been assigned a colour.

And as soon as the cell is not empty, that colour changes.

So that's the condition there, not empty.

Data Validation.

User error is a real possibility in this spreadsheet.

It will be very easy to enter the wrong seating code, so you need to do as much as possible, to prevent user error.

You're going to use validation to create a drop-down list using the seating codes, to indicate which seats are sold.

Pause the video to complete your task.

Task four, data validation.

Using the worksheet and the spreadsheet, RSC Live event seating, complete task four.

Resume once you're finished.

Task four, data validation-solution.

So as you can see now, the cells have got a little symbol on there.

And once you click on them, it provides you with a drop-down.

So the options are the adult, the student, and the over 60s.

Counting sold seats and seats remaining.

Counting sold seats.

In the seat sold section of the spreadsheet, we're going to be using a COUNTIF function, to count the seats sold.

There's going to be a lot of repetition, so make sure you working methodically.

Pause the video to complete your task.

Task five, counting seats.

Using the worksheet and the spreadsheet, RSC Live event seating, complete task five.

Resume once you're finished.

Task five, counting seats-solution.

So now on our model, we can count the seats that have been taken.

So I've done some testing here.

So as you can see in the premier section, an adult seat has been taken.

So we can see there, a premier seat in the adult section has been taken, and now we've got 18 seats remaining, because one student seat has also been taken as well.

So we had a total of 20 seats available, two of them have been sold, 18 seats remaining.

Calculate income.

The final stage in completing this spreadsheet model, is to calculate the income for each of the three seating sections, and to create a final total.

Pause the video to complete your task.

Task six, calculate income.

Using the worksheet and the spreadsheet, RSC Live event seating, complete task six.

Resume once you're finished.

Task six, calculate income solution.

So as we can see here, for the income what we did, so for example, for an adult in premier, we multiplied the price that the adults' paying, by the number of seats that have been taken.

So here we've got two adults, if we were to change it to three, we then know that this has changed to 60 now, we've got three adults, and that's because the three has been multiplied by the 20, to give us a 60 there.

For the total, we use the sum function, and we do the sum of cells X20 to Z20.

So XYZ, we're adding all these together.

And then for the grand total, we're doing a sum of the cells here.

So it is AA20 all the way up to AA22.

Fill those seats.

So you should have been testing the spreadsheet model, while each each section was being completed.

Now it's time to test it further.

Sell all the seats in all the seating sections.

Check that the seats sold and the seats remaining formulae are working correctly.

Check that the income section is working correctly.

And you could experiment with changing the seat prices, to see what effect it has on the financial model.

Have a go at this.

Thank you very much for joining me on lesson two.

I hope you got a better understanding of conditional formatting, data validation and using the COUNTIF function.

Share your work with us at Oak National.

If you'd like to, ask a parent or carer to share your work on Instagram, Facebook or Twitter tagging @OakNational and #LearnwithOak.

Thank you.