Making sure that people enter valid information!

Have you ever received a letter in the mail from your bank telling you that you must pay $0 before a certain date, otherwise you will be charged interest on the full amount? I have! Perhaps you've a better example of how automated procedures can go wrong! How might this mistake have been avoided?

It can certainly look pretty bad for you, if you create a customised application in Excel for a business and it spits out receipts or reports with stoopid information! Now, let's find out how we can stop this from happening in our own Excel applications! Work through this first introduction to data validation from Microsoft as well as the follow-on tutorial, before completing the following mini-project in class.

Recording student results

Being the excellent student that you are, a teacher has requested that you develop a simple spreadsheet to record student results for her class. There's been lots of problems (in other institutions) with students being given the wrong grades, and letters being sent to the wrong suburb, so we want to avoid these embarrassing situations!

Open a new spreadsheet and add the following column headings in the third row.

• Firstname
• Lastname
• Title
• D.O.B.
• Town/City
• PostCode
• Test Mark (30)
• Assign Mark (40)
• Total (as a %)
  
• Final Grade (F/P/C/D)
  

This spreadsheet needs to be prepared for input validation to avoid embarrasing data-entry errors. With this spreadsheet:

1. Add data validation to the DOB column so only dates between 1/1/1900 and today are valid.
2. Add a list of valid titles to the Title column (Mr, Mrs, Ms, Miss, Dr, etc.)
3. Add a list of valid suburbs to the spreadsheet (Katoomba, Leura, Wentworth Falls, Blackheath, Medlow Bath)
4. Add validation so the test mark must be between 0 and 30 inclusive.
5. Add validation so the assignment mark must be between 0 and 40 inclusive.
6. Calculate the total mark.
7. The grade should be entered as follows:
   • 00-50 : F
   • 50-70 : P
   • 71-85 : CR
   • 86-100: D
8. Save your spreadsheet as "Studentgrades.xls"

Could this little spreadsheet be improved at all? If so, give it a go!

When you get a chance, write a quick entry on your blog explaining what data validation is in Excel, and how hard/easy it is to use. How could your student data worksheet be made even more foolproof?