Lesson video

Hi, I'm Cache.

Your computer science teacher for the spreadsheets unit.

In this lesson, we're going to be using a LOOKUP function to retrieve data.

And an IF function to provide users with feedback.

For this lesson, you going to use a pen, some paper, and you're going to need to remove any destructions that are going to stop you from focusing.

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

In this lesson, you will implement a LOOKUP function to retrieve data, implement conditional formatting to highlight stock levels and implement an IF statement or IF function to give the user feedback about stock level.

Starter.

The name of products are sometimes too long to fit on the till receipt.

As you can see on the right hand side.

Some of the product names have been abbreviated.

Our database of RSC merchandise will also have shortened names for the items. Though you can see that some item names have been shortened, loose items get two lines and the one highlighted towards the bottom is Mueller Light Greek yoghourt and it's a four-pack.

Starter.

A barcode reader or barcode scanner is an optical scanner that can read printed barcodes, decode the data contained in the barcode and send the data to a computer.

So what happens after the beep? Pause the video to complete your task.

Task one, after the beep.

Using the worksheet on the spreadsheet, lesson five starter, complete task one.

Resume once you're finished.

Task one, after the beep solution.

So as you can see, we've got some advantages and disadvantages of using barcodes here.

So an advantage could have been, compared for items quickly, but a disadvantage is, it's unreadable if it's crumpled.

So the list of solutions is there.

And on the right hand side, we can see that they are the correct options.

How did you get on? Create the lookup sheet.

Using the RSC merchandise database, add sequential item numbers to the first two items, then use the fill handle to drag the sequence down.

What this does, is it gives the remaining items a sequential number as well.

Drag the formula in E2 with the fill handle.

So E2 contains the VAT for that particular item.

The formula there, D2, contains the price of the item, and we're multiplying it by dollar I dollar one.

In the cell I1, we have the VAT.

So the reason why we're using absolute cell referencing, why do you think? Yes, because if we were to just drag it down, it would then become I two I three I four, however, with absolute cell referencing, that doesn't change.

Let me show you a demo.

Here we have the RSC merchandise database spreadsheet.

What you're going to do first is click onto the lookup sheet.

Once you get onto the sheet, you can see item numbers, the full names, the shorter names, the price at the till and the VAT.

So first as we mentioned, we're going to drag down this item number.

If we were to just drag it down without putting a sequential number in the cell A3, what would happen? What do you think would happen? Right? Let's see.

So what happens is they've all got the same number.

So if we were scanning these items and the barcode was the same for each of them, it would cause a problem.

So instead, what we're going to do is, let's delete that.

We're going to put in a sequential number here.

So one double zero, one double zero two.

Now, if you highlight both these two cells and then we use the fill handle, they'll now give us sequential numbers all the way to item one zero zero, one zero, one six.

In cell E2, here we have the VAT.

What's happening here, is cell D2, which is 15 pounds is being multiplied by cell I1 which is 20%.

And here we're working out 20% of 15 pound, which is three pound.

We can drag this down using the fill handle, and it will give us a VAT for each of the items. The reason why we've got that conditional formatting, not conditional formatting, sorry.

The reason why we've got the absolute cell referencing is because we don't want this cell to change.

So if we didn't have this, and in this cell, if we got rid of the absolute cell referencing.

Got a little cell there.

Yeah, so if I clicked under here, now have dragged down.

As you can see, it's not worked.

Why? Because this cell is using the correct cells.

However, the next cell is now using D3, which is correct.

But it's now using this cell here.

So that's the reason why we use the absolute cell referencing.

So if I put that in, put the dollar signs in, and now if I drag it down, it should work right.

Okay.

That's all on that.

LOOKUP function.

The LOOKUP function searches for a value in one column or row and returns a matching value from the same position in another column or row.

Using the RSC merchandise database spreadsheet.

We want to enter an item number, so the barcode and have the LOOKUP function return data about the item, such as the short name for the product, the price, and the amount of VAT paid on the item.

Enter an item number in cell A8 of the RSC merchandise sheet.

You will see this formula in cell B8.

So the formula is equals VLOOKUP.

That's the function that we're using.

A8 is the cell that we're looking up.

So look up the cell.

Now, the, after that comma, you can see it says lookup sheet dollar, A, dollar two, all the way to dollar C, dollar 17.

So what that section there is telling us is the range of cells that we're looking in.

The three after the comma, tells us that we're returning the third column.

And FALSE at the end, tells us that we're searching for an exact match.

Drag the formula in B8 to the cells below.

Use the fill handle to drag the formula in C8 to the cells below.

And that's the formula for that cell.

And then use the fill handle to drag the formula in D8 to the cells below.

And there we have the formula for that as well.

So the column number sold, we're going to enter some numbers into that in the moment.

On the input check, so this is going to tell us some important information.

So if we input a incorrect number, it'll pop up and it'll say check input.

So just have a look at the dialogue box on the right hand side.

Number sold cannot be greater than in Stock.

So we can't sell, for example, 10 Taylor Swifts t-shirts, if we've only got, you know, eight in stock.

So this formula in E8 outputs a message.

If this happens, the formula is equals, IF, bracket open G8 minus E8 less than zero comma and then in the speech marks, we have the text that's going to be output, which is check input.

Use the fill handle to drag it down.

The remaining columns, we've got in stock.

So that's the stock level that we're starting off with.

And remaining stock.

So that will be calculated by the formula equals G8 minus E8.

To show you a demonstration of this, let's have a look at the spreadsheet.

So we've just added the numbers, the sequential item numbers, and we've just sorted out the VAT column as well.

So now, if we go back to the RSC merchandise database sheet, if we enter one of the numbers.

So one of the barcodes was for example, one zero zero, one zero zero one.

When I click off of the cell, I can tell that this has all been populated for me.

However, if I enter a number to this cell, so one zero zero, one zero zero three for example.

Nothing appears.

That's because the formulas that we've just discussed or formulae should we say, because it's a plural.

Here do not or are not applied to cell B9.

So what we're going to do is we're going to click on that cell and we're going to drag down.

So we can do that for cell B8, cell C8, and cell D8 as well.

And now, let's have a look at this, so.

That's been done, that's been done, that's been done.

Now, if I was to enter another.

So for example, one zero zero, one zero zero seven, Z.

And that's been populated.

Don't worry too much about this if it says N/A.

It will only be populated once we put a item number into here.

So we also mentioned about this cell here.

So this is the number of.

That's been sold, or the number of baseball caps that's been sold.

This is the input check.

Now the input check will only appear if the input's incorrect.

So if we've got a 1000 sold and in stock we've got a 1000, that's fine.

But if we sold 1001, you can see that this error message appears here.

We can always adjust the spreadsheet.

So I format it.

So that we can see the error message a little clearly.

And remaining stock is also in a minus now, because remember the formula for that? We use equals G8 minus E8.

Oh, we shouldn't really have a negative here.

So let's fix this issue.

And that seems fine now.

Okay.

Pause the video to complete your task.

Task two, LOOKUP function.

Using the worksheet and the spreadsheet RSC merchandise database, complete task two.

Resume once you're finished.

Add a status output.

In the final column, I8 to I28, we will flag up when an item needs to be reordered.

In cell I8, there's a formula for this.

Use the fill handle to drag it down.

All the way to I28.

The formula is equals IF, bracket open, H8 is less than a 100, comma, speech marks, reorder.

So what that means is, if the remaining stock is less than a 100, I'll put the text else I'll put nothing.

Let's have a look at this on the spreadsheet.

Here on the spreadsheet model, in cell I8, we can see that formula being used.

So H8 is the remaining stock.

If it's less than a 100, we're going to display the text reorder.

At the moment, the remaining stock is 500.

Thus a cell which gets calculated by G8 minus E8.

Which is the number that we've sold and the number that we've got in stock.

So we subtract sold by in stock.

And that gives us 500.

So let's imagine we sold 950.

What do you think would happen? Yes.

That text now appears reorder.

However, if we sold, it can read, we going to see that text.

That's really useful for a business, for them to understand how much stock they have and when they need to reorder.

We can always manipulate this.

So instead of it being less than a 100.

When it's less than, for example, 50.

We want to reorder.

So more expensive items such as the guitar hoodie.

We might use that for this one.

Pause the video to complete your task.

Task three, add a status output.

Using the worksheet and the spreadsheet RSC merchandise database, complete task three.

Resume once you're finished.

Conditional formatting.

In the final column, I8 to I28.

The cells will either be blank or will show the text reorder.

A conditional format could be added to these cells to add a stronger visual reminder for the user.

Let's have a look at a demo.

So first, we're going to highlight cells I8 to I28.

We're then going to click on format.

Conditional formatting.

And then the options will appear on the right hand side here.

So the cells have been highlighted.

Under where it says format rules, we're going to select text is exactly, yes.

And then we're going to put in the text that appears there.

So as you can see at the top here, we've got the word reorder.

So just copy that, so I know I'm not making any errors.

I'm going to paste it into here.

And then I'm going to choose a fill colour of red.

Once that's done, I'm going to click Okay.

And now let's test this model.

So if the total number sold now is 950.

In stock, less than, 50.

All right, it's going less than 50.

All right.

So let's make it 951.

49 left on reorder is in red.

Pause the video to complete your task.

Task four, conditional formatting.

Using the worksheet and the spreadsheet RSC merchandise database, complete task four.

Resume once you're finished.

Testing the database.

Let's explore further ideas for exploring, changing, and testing the database, as you can see below.

So we can make changes to the VAT rate, which makes changes to each items VAT.

Pause the video to complete your task.

Task five, testing the database.

Using the worksheet and the spreadsheet RSC merchandise database, complete task five.

Resume once you're finished.

Why have we used conditional formatting in this spreadsheet? So here we have a spreadsheet of students studying computer science and the level in programming and homework that's been done or is missing.

Why do you think conditional formatting has been used? Let's have a look.

Conditional formatting has been used to give a visual indication when criteria are met.

So it's quite easily, quite easy for me to have a look at the spreadsheet and see which students got level three.

And it's quite easy for me to see which students have not done particular pieces of homework.

What's left to say is thank you very much for joining me on this spreadsheets unit.

I hope you learned a lot more about VLOOKUPS, about conditional formatting and about using a functions within spreadsheets.

Share your work with us at Oak National.

You can use Instagram, Facebook, or Twitter.

But be sure to ask a parent or carers permission.

Thank you.