A model you do not trust is just a toy
The Modeller does not end with a formula. It ends with trust: I change F2 → H, I, I10, the grid, Prehled — everything fits. Checking the model = small tests that look for broken links and hardcoded numbers.
A test checklist
-
F2 test: F2=1.5. Write I10. F2=1. Does I10 differ? If not, H or I does not depend on F2.
-
Grade test: B4 +1 grade worse. B8 average and Prehled!B1 the same movement?
-
#REF! test: Go through the sheets — no red errors in formulas. Especially after renaming a sheet.
-
Hardcoded test: Find cells with numbers without
=in K (scenarios), B1 Prehled, E survey summary. Scenarios can be written after a run — but you must know which. -
Sense test: Balance D8 positive on a normal week? Average between 1 and 5? Survey shares total 100%?
Write three test inputs on Poznamky: F2=1, F2=1.3, B4=3. Expected behaviour in one sentence. In a month again — a regression test.
Audit table
| Cell | Should be | Is it? |
|---|---|---|
| Znamky!B8 | =PRŮMĚR(B2:B6) | ☐ |
| Prehled!B1 | =Znamky!B8 | ☐ |
| Kapesne!H2 | =C2*$F$2 | ☐ |
| Pruzkum!E2 | COUNTIF | ☐ |
What often goes wrong
Circular references. D8 = D7+B8 and B8 = f(D8). The table shouts. Cut the chain.
Mixed C and H. Prehled sums H instead of C — it shows fiction as reality.
Forgotten reset of F2 after a test.
🏫 Grade average — one formula
Only B8. Column C KDYŽ does not affect the average — OK. If the average counted from C, the audit fails.
💰 Pocket money — F1 F2 H I I10
A chain of levers → model → result. One F2 test finds a hole.
📊 Class survey — COUNTIF alive
Add a row in A (a test). Does column E move? If not, range A2:A50 is too short.
🏆 Mini dashboard — Prehled audit
Prehled!B1:B4 all formulas. Ready for a copy onto Dashboard.
Now you 💪
First: fill in the audit table. Fix a fail.
Second: three F2/B4 tests write on Poznamky with the expectation.
Check: No #REF!/#DIV/0! without a reason. F2 test passes. Prehled alive.
What to take from this lesson
- Audit = a test of levers, links, sense.
- Hardcoded numbers only on purpose (a scenario after a run).
- Write three regression tests down.
© 2026 Ing. Martin Polak / AlgoRhino · コンテンツ利用規約