Your starting point
Prerequisites: Use the ideas and vocabulary from lesson 43; revisit its worked examples if needed.
Definition: A spreadsheet formula computes a value from references, constants and operations. Relative references change when copied to another location; absolute references remain fixed. A calculated field applies a defined operation across records, while an aggregate summarizes several records.
Understand the reference before copying
A formula such as =B2*C2 refers to values in one row. Copying it down typically changes the references to B3 and C3. A fixed parameter such as a conversion factor may need an absolute reference, for example $F$1, depending on the spreadsheet. The academy’s formula builder represents fixed parameters as explicit constants rather than evaluating arbitrary spreadsheet expressions.
Check precedence and units
Multiplication and division normally occur before addition and subtraction unless parentheses change the order. Translate the formula into plain language and verify a simple case. Revenue minus cost divided by revenue is not the same operation as profit divided by revenue unless the subtraction is grouped. A correct-looking result in one row can conceal a precedence error in the general rule.
Distinguish errors from meaningful zeros
A formula should have a stated policy for missing inputs, invalid types and zero denominators. Avoid wrapping every problem in a replacement of zero merely to remove an error display. That can silently convert unknown or undefined results into measured absence. Keep inputs visible and test a normal row, a boundary row and an invalid row before copying the rule across a dataset.
Two worked examples
Relative and fixed inputs
For several products, quantity varies by row while a common conversion factor stays at 1.2. A spreadsheet can multiply each row’s quantity by a fixed cell; the workbench can use the constant 1.2. If the fixed reference accidentally shifts while copying, the later rows may use unrelated cells instead of the intended parameter.
Parentheses change meaning
Revenue is 100 and cost is 60. Profit margin is (100−60)/100 = 0.4, or 40%. The expression 100−60/100 equals 99.4 under ordinary precedence, which is not the margin. Calculate the difference first in one field, then divide by revenue if using the workbench’s stepwise operations.
Where this becomes useful
A learner building a repeatable spreadsheet should be able to explain each reference and unit, not just copy a formula from an AI answer. A small test table can reveal mistakes before they propagate through a report.
Make an attempt
Use Formula Builder with Monthly budgets. Create actual minus planned, then divide that derived difference by planned and multiply by 100. Verify a row manually and explain why the two-step construction makes the intended grouping explicit.
Misconception check
Claim: Removing an error display by replacing it with zero fixes the calculation.
Inspect the correction
It can hide undefined or missing results. Diagnose the reason and preserve a meaningful distinction from observed zero.
Independent application
Design a small formula test table with normal values, a missing input and a zero denominator. Write the formula in words and symbolic form, verify each case, and document how fixed parameters and undefined results are handled.
Completion records practice and self-review. It is not certification of statistical competence.
