Question

Validation and verification of data is always important with any spreadsheet. It's crucial that the data, functions and formulas are accurate. In this week's reading, various techniques are covered to help improve the accuracy of a spreadsheet. Choose two of those techniques, explain what they do and share an example of each.

Answer 1

Data validation is an essential part of any data handling task whether you’re in the field collecting information, analyzing data, or preparing to present your data to stakeholders. If your data isn’t accurate from the start, your results definitely won’t be accurate either. That’s why it’s necessary to verify and validate your data before it is used.

Use Range Numbers :

Range means the cells where you want the formula to check. Usually, they will be your headings, but there are other possible applications.

Example; a value in a report is labeled Total Sales, and suppose the formula for this result is:=SUM($D$5:$D$28) How can you tell if that formula is pointing to the correct data? It’s impossible to know, of without going to the cell address and examining its data.

Instead, suppose the formula is: =SUM(SalesService). If you assign understandable names to key ranges in your spreadsheet, and then use those names in your formulas, your formulas will be much easier to understand. And the errors in your formulas will be much more obvious.

Keep Formulas Simple :
Keep your formulas simple instead of stringing lots of things together – you can make things as complex as you like, but breaking the calculation into easily recognisable steps helps you to identify any errors. Work with several meaningful components, across a few more cells with labels so that the logic of your workings is obvious.

For Example; Total Sales: = (Price*Quantity) - (Price*Quantity)*Discount + (Price*Quantity)*Tax. Formulas are a powerful way to capture and save a working solution. They let you repeat the solution again whenever you like, always with perfect accuracy. They are far better than your faulty memory.

Your understanding of formulas helps you you design better spreadsheets. You can organize data in a way that takes advantage of formulas, which drastically reduces errors and troubleshooting. (To be fair, Pivot Tables also help you do the same thing).