Objectives

Students will be able to:

  • understand that it is human to make mistakes
  • create data validation rules to limit invalid data entry into spreadsheet model
  • understand that it is tricky to create validation rules for all types of data entered into a digital document
A B C D E
1 Code   Item Price Quantity Total cost
2 Orange juice $6.99 $6.99
3 Chocolate bar $2.99 $2.99
4 Orange juice $6.99 $6.99
5 Honey Tea $4.50 $4.50
6 Coffee $8.25 $8.25
7 Orange juice $6.99 $6.99
8 Earl Gray Tea $8.75 $8.25
9 Orange juice $6.99 $6.99
10
11 Total $51.95

Check out the smart spreadsheet! You can enter an item code and change the quantity and the spreadsheet updates itself!

But what happens if you enter an invalid code eg #$%@? Try it. Everything gets messed up. The data being entered is invalid - it doesn't relate to the purpose of the cell or the spreadsheet in general.

Software specialists who design spreadsheet models have to be aware that normal users - eg office workers - are human and are likely to make mistakes.

We need to force the user to enter valid data only.


Software specialists who design spreadsheet models are familiar with some powerful data validation techniques. One of them is in the spreadsheet above. Can you find it?

We can create drop-down boxes to force users to select only valid data as shown above.

We can also create rules that will force the spreadsheet software to alert a user if invalid data entry is attempted.

For example, how old are you?

The above example uses a data validation rule: if the number entered is outside the range 14:18, force the user to re-enter.

   Task

If you are using Google Sheets, use this sheet.

If you require a csv file to get started, download this one.

Follow these instructions to develop the spreadsheet model:

Insert a new column between columns B and C. Give it a heading Pay Rate Per Hour in row 4.
In the Pay Rate Per Hour column, use the RANDBETWEEN function to generate a random number between 20 and 50. Copy down the function for all of the other rows in the sheet.
In the Total Pay Before Tax column, create a formula to calculate the total pay: Pay Rate Per Hour * Number of Hours Worked
Copy down the formula for all other rows in the spreadsheet.
In the Total Pay After Tax column, create a formula to calculate pay after tax: Total Pay Before Tax - (Total Pay Before Tax * Tax).Use cell G2 as the tax value.
Copy down the formula for all other rows in the spreadsheet. There is a problem. What is it? Can you fix it?
Note: there are different ways to solve this problem but only 1 way is super efficient!
The staff can only work a minimum of 0 hours per week and a maximum of 50 hours per week. Select all of the cells in the Number of Hours worked column and create a validation rule which will reject data entered outside of this range and display a warning message. You may need to research this.
Test your solution. Does it work? 0 should be valid. 50 should be valid. Does -30 work? How about 101?
Format the tax value in cell G2 as a percentage to 0 decimal places.
Create a summary data section. It should look like this:
Note the average is formatted to 2 decimal places.
Create the following bar chart. It should look like this:
Format all currency data as Japanese Yen to 2 decimal places.
Make sure all data in your spreadsheet model is visible.
Extension Write a short email to the managing director of the company, Mr. Shintaro Hashimoto. Attach a copy of the bar chart. Include your signature image if you have one. Don't forget to translate your email into Japanese. Use your school email and send it to your teacher.

Food For Thought

Data Validation techniques are a fundamental part of Spreadsheet Model Design. In today's lesson, you designed a model without caring about the exact information that the spreadsheet needs to store. You are the designer, not the user. Hopefully the user will be happy with your design and will be able to use the model effectively.

Homework

Tags

spreadsheet data csv model merge insert row format currency column range decimal places cell reference

SUBSCRIBE

Join my mailing list to receive updates on the latest blog posts and other things.