Articles

There are a number of common problems, difficulties and confusions that befall the users of spreadsheets. Some of these issues require detailed discussion so we have provided articles that outline common problems and confusing situations and give suggestions of ways to solve them.

Spreadsheet functionality
Data validation
More about data validation
More about data validation #2 (lists) Validation example spreadsheet
(XLS 58 KB)
Evaluating spreadsheet formulas step by step Buggy “NZ Rounding” spreadsheet (XLS 34 KB)
Checking formulas
Data storage problems #1 (pivot tables) data layout (XLS 79 KB),
End User Database Management: Trade-offs when Storing Multi-Valued Data in Spreadsheets (PDF 126 KB)
Storing multi-valued data in spreadsheets (PDF 173 KB)
Dates and times
Working with dates and times Dates and Times example spreadsheet (XLS 56 KB)
Getting elapsed dates correct Dates and Times example spreadsheet (XLS 56 KB)
Getting elapsed times correct Durations example spreadsheet
(XLS 14 KB)
Calculating ages and using Excel's DateDif function Ages Calculations example spreasheet (XLS 38 KB)
Problems with sorting
Sorting problem #1 Bad Marks example spreadsheet
(XLS 49 KB)
Sorting problem #2  Bad Marks example spreadsheet
(XLS 49 KB)
Using Data Base functions
Using DFunctions in data tables DFnData Tables example spreadsheet
(XLS 65 KB)
Problems with rounding
Rounding problem #1 (bad lookups) Bad Marks example spreadsheet
(XLS 49 KB)
Rounding problem #2 (incorrect totals) Rounding errors example spreadsheet
(XLS 34 KB)
Rounding problem #3 (precision) Buggy “NZ Rounding” spreadsheet (XLS 34 KB)
Rounding problem #4 (inconsistent answers) Rounding function example spreadsheet (XLS 41 KB)
Range Names
Working with Range names
Problems with named cells (GST problem)

 


Page last updated on: 13/07/2010