In B4, calculate the bonus without a circular reference: bonus = rate × profit / (1 + rate).
Lesson theory
A circular reference is a formula that refers to itself, directly or through other cells. For example, a bonus equals 10% of the profit after the bonus: B4 = 10% × (B1 − B4). To find B4 you would already need to know B4. Excel shows a warning, and the status bar says "Circular References: B4". You can find them under Formulas › Error Checking › Circular References.
If you want the cycle on purpose, Excel can calculate it step by step: File › Options › Formulas › Enable iterative calculation.
- Maximum Iterations (100 by default) is how many times to go around the loop;
- Maximum Change (0.001) means the calculation stops when the values barely change any more.
The downsides: the setting is saved in the workbook and applies to all formulas, so accidental cycles stop being noticed; the result depends on the number of steps, and if the model has an error, the values "run away".
The better way is to solve the equation and avoid the cycle. Bonus: B = p × (P − B) ⇒ B = p × P / (1 + p). Interest on the average balance: I = r × (S + (S + I)) / 2 ⇒ I = r × S / (1 − r/2). You end up with ordinary formulas that always calculate the same way.
The trainer has no iteration setting, so a circular formula will not finish calculating here. Solve both problems algebraically.
Test yourself
Enter — commit · F2 — edit · F4 — $ in a reference · Ctrl+C/Ctrl+V — copy and paste · Ctrl+Z — undo
Esc or F6 — leave the sheet for the «Check» button · Shift+Tab in the first cell — back to the page