Data and spreadsheets
Build checks around a spreadsheet summary
Pair every summary figure with a check that reaches the same number a different way, such as a separate total, a row count or a relationship that must hold. A check that reuses the same formula can repeat the same mistake.
A text to start with
My spreadsheet summarizes [what it summarizes] from [source data]. The summary shows [key figures] and the detail rows are on [sheet or range]. Suggest checks that reach each figure by a different route, such as row counts, subtotals that must add to a grand total, or values that must stay within a range. Describe each check in words and say what a failure would mean.
Example request. Change the details to fit your situation.Summary check sheet
Summary figure being checked: [ ] Source data location: [ ] Expected relationship: [ ] Independent method used for the check: [ ] Independent total: [ ] Actual result in the summary: [ ] Discrepancy: [ ] Acceptable tolerance and reason: [ ] Rows counted in source: [ ] Rows counted in summary: [ ] Hidden or filtered rows present (yes/no): [ ] Numbers stored as text found (yes/no): [ ] Explanation of any difference: [ ] Person who investigates: [ ] Status (passed, failed, resolved): [ ]
Checks that prove nothing
A check built from the same range and the same formula will agree even when both are wrong. Look for filtered or hidden rows left out of a sum, ranges that stop before the last row, and numbers stored as text that a total skips. Totals that include a subtotal row can count items twice. Rounding can leave small differences that should be explained, not ignored. If a check can never fail, it tests nothing. Include at least one check against a source outside the workbook, such as a figure from the original report.
Ask another agent to check the result
Take a sample of five or six rows from this data and recalculate the summary by hand or with a method different from the formula under review. Do not reuse that formula. Compare your result with the workbook and describe any discrepancy and its likely cause.
Try to break the checks
On a copy, change one input, remove a row and add a duplicate, then confirm the right check fails each time. Write down the tolerance you will accept for rounding. Put the checks beside the summary where readers can see them. Name who investigates a discrepancy and what happens to the summary until it is resolved.