Lesson 6.
5: Show Bad Data
How to use validation to find mistakes in your data table
Missing Stock
In the last lesson, you calculated the closing stock for your online business. The
closing stock tells you how many items should be in the warehouse. Sometimes
the number of items in the warehouse does not match the closing stock number.
There are many reasons for this:
- The count during the last stock check was wrong.
- Workers have not recorded stock changes (stock in and out) correctly.
- Workers have put items back in the wrong place.
- Some items may have been broken or stolen.
Importance of Accurate Stock Data
It is bad for a business when stock is missing. Mistakes in recording stock data
can cost money and cause problems. The bigger the business and the more stock
it has, the more likely it is that there will be errors in the stock data.
Every business needs to have accurate data about its inventory. Good stock
records will help.
Validation
Validation is a way of checking data. Validation uses rules to check the data. There
are two types of data validation:
- Validation of data that is already in the data table: If the data breaks the rules, it is
highlighted so you can find and change it. You will use this method in this lesson.
- Validation of data entry: If the data breaks the rules, you cannot enter it in the
data table. You will use this method in the next lesson.
Data that does not obey validation rules is called 'invalid' data. You pronounce this
word with the stress on the second syllable: in-'VAL-id'.
Validation Rules
Validation can use rules like these to check that data is accurate:
- The type of data (text, number, etc.) – for example, text for product name,
number for opening stock, etc.
- The range of number values – for example, only values between 0.99 and 9.99
are allowed.
- A list of 'allowed' data – for example, a list of the brands stocked.
Validation Rules for Stock
In a business, the warehouse workers will input the number of items in stock and
the number of stock items that come in and go out. The validation rules will make
sure that they only enter valid data.
How do you decide which validation rules to use?
Validation Rules for Stock (Detailed)
In a business, the warehouse workers will input the number of items in stock and the number of
stock items that come in and go out. The validation rules will make sure that they only enter
valid data. Think of the online jam business. When a worker enters the number of jars of jam in
stock, the input must obey these rules:
- It must be a number – you cannot have '2' jars of jam.
- It must be a whole number – you cannot have 0.5 jars of jam.
- It must be a number greater than or equal to 0 – you cannot have -20 jars of jam.
Do these rules apply to the products in your business too?
Find Bad Data
Bad data is data that contains errors. It is important to check the data in the data
table for errors. In your business, there are only 10 records. But in a real business,
there might be thousands or even millions of records. Businesses can use
validation rules to find errors hidden in all their data.
Make a Deliberate Mistake
You can add deliberate mistakes in your data to check that your validation rules
work. When you add the validation rules, they should find your deliberate
mistakes.
Janna made two deliberate mistakes to check her validation rules. She changed
the 'Opening stock' for product P0003 to -70. She changed the 'Stock in' for
product P0004 to 2.33. Here is the table with the deliberate mistakes.
Step 1: Select Cells to Validate
1. Select all the cells where you want to apply the validation rules.
2. For this example, check the cells showing:
• Opening stock
• Stock in
• Stock out
3. Do not include column headers. Refer to the example spreadsheet.
Step 2: Start Validation
1. Open the 'Data' tool bar at the top of the screen.
2. Click the 'Data Validation' button to open a new window.
3. This window is where you will enter the validation rules.
1. Choose options from the drop-down menus to set your validation rules:
• Data values in selected columns must be whole numbers.
• Find 'Whole number' in the first drop-down menu.
• Set the minimum value to '0' (no negative numbers).
2. Use 'greater than or equal to' for setting rules.
Circle Errors
● Remember that in real life, your data table might have thousands or even millions of records.
● The computer can help you find all the records that break the validation rules.
1. Go back to the 'Data Validation' icon.
2. Click on the arrow to open a menu.
3. Select 'Circle Invalid Data'.
● The software draws a red circle around all the invalid data.
