DAO1704 Chap.1 Spreadsheet Logic and Auditable Formulas
Spreadsheet Logic and Auditable Formulas
Formula structure before output
A spreadsheet formula is a reusable decision rule. Relative references move with the formula, absolute references remain anchored, and mixed references lock only one dimension. That movement should be chosen deliberately before the first result is trusted.
The chapter puts shared assumptions in visible control cells, checks copied patterns and treats range endpoints as part of the model.
Conditional logic needs boundary tests
IF expresses one decision boundary, while IFS evaluates several boundaries in order and stops at the first true condition. COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS add criteria ranges that must remain aligned.
A formula can look plausible while referring to the wrong row, using a broad condition too early or averaging a range that does not match its filters.
Lookups are joins in miniature
XLOOKUP connects a lookup value to a return array. Its reliability depends on key consistency, uniqueness and explicit treatment of missing matches.
The chapter closes with an audit route that names the intended decision, classifies references, checks ranges, tests boundaries and perturbs one shared assumption before accepting the workbook.
Why plausible results deserve more scrutiny
A reference error often returns a number instead of an error message. The value may sit inside the expected range and survive a quick visual review.
Reliable checking therefore follows structure rather than appearance. Trace one formula to its inputs, compare the copied formula with the original, inspect the exact threshold values and change the shared assumption. A control total or independent hand calculation gives a second route to the same result.
When the two routes disagree, pause at the first differing step instead of changing formulas until the displayed answer looks familiar.
What this chapter covers
- 01
Relative, mixed and absolute cell references
- 02
Visible assumptions in control cells
- 03
IF and ordered IFS boundaries
- 04
Aligned criteria ranges in conditional functions
- 05
XLOOKUP keys and missing matches
- 06
Copy-down and perturbation testing
Worked example · free
Building and checking a copied formula
- 2Calculate contribution from the two row values.
- 2Convert contribution into a revenue rate.
- 2Apply the uplift using a locked control cell.
- 2Explain which references move after copying.
Key terms
- Relative Reference
- A cell address that changes by the copied row or column offset.
- Absolute Reference
- A cell address whose locked row and column stay fixed when copied.
- Mixed Reference
- A cell address that locks either its row or its column while the other dimension moves.
- Criteria Range
- The cells evaluated against a condition inside a conditional function.
- Catch-All Condition
- The final IFS test that assigns every value not matched by earlier boundaries.
- Lookup Key
- The value used to locate a corresponding result in another range or table.
Spreadsheet Logic and Auditable Formulas FAQ
Why can a copied formula be wrong when the first result is right?
The first row can use the intended assumption by accident. Once copied, an unlocked global reference moves to a different cell and the error begins. Inspect the reference pattern and test the next row rather than validating only the original value.
Why does condition order matter in IFS?
IFS stops at the first true condition. A broad threshold placed first captures values intended for later, narrower bands. Arrange boundaries from narrow to broad, finish with an explicit catch-all, and test the exact cut points.
How should a lookup failure be handled?
Use an explicit not-found result, then investigate the key. Check spaces, data types, spelling and duplicate keys before replacing the missing value. Hiding the error with a blank can make an incomplete join appear successful.
Exam move
Create a small audit workbook with one control cell, several row values, an IF or IFS classification and an XLOOKUP table. Copy the formulas, then deliberately remove a dollar sign, reverse two thresholds and alter a key. Predict each failure before observing it.
Finish by writing the audit order from memory: state the decision, classify references, inspect range endpoints, test boundaries, review unmatched lookups and perturb the shared assumption. Add a control total calculated by a second route and reconcile any disagreement at the first differing step.
Practise explaining each dollar sign and criteria range in plain language, because a formula you can narrate is easier to repair under time pressure than one learned as a finished string. End by swapping the dataset while keeping the rule. If the formula still works and the explanation still fits, you have learned the method rather than the original cell addresses.
Working through Spreadsheet Logic and Auditable Formulas in DAO1704? Sia is AskSia’s AI Statistics tutor — ask any DAO1704 Spreadsheet Logic and Auditable Formulas question and get a clear, step-by-step explanation grounded in how DAO1704 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
