6.2 Core Formulas and Error Handling
Key Takeaways
- IF(AND(volume>=10000,price>50),0.12,0.10) returns 12% only when both tests are TRUE; volume 9,500 and price $80 still pays 10% because AND is not an average of the two hurdles.
- IFS (Excel 2016+) returns the result of the first TRUE test in order; a nested IF is the same logic with parentheses, and a misplaced first test (EBT<=200000 before EBT<=50000) mis-buckets $40,000 of EBT at 25% instead of 15%.
- IFERROR traps every error type; IFNA traps only #N/A — wrap a peer lookup in IFNA so a missing ticker can be labeled while a deleted column still shows #REF!.
- #DIV/0!, #REF!, #VALUE!, #N/A, and #NAME? are formula or structure bugs; a revolver-interest circular reference is a loop that iteration may be designed to solve, not a reason to IFERROR the model to zero.
- Boolean 1/0 switches multiply cleanly: =C12*(1+Growth*Case) is unchanged when Case is 0 and grows when Case is 1, which is the pattern data tables need later.
What Core Formulas Means on the FMVA Excel Domain
Excel is about 10% of the FMVA final, and the case studies are Excel files. The tested skill is not memorizing every function in the ribbon. It is writing formulas that (1) copy cleanly across a forecast, (2) react to assumption switches, and (3) fail loudly when a driver is missing instead of silently showing a plausible but wrong number.
This section is the logic layer: IF, AND, OR, IFS, IFERROR, IFNA, the error codes you will actually see in a three-statement file, 1/0 boolean switches, and the difference between a circular reference and a broken formula. Lookups, SUMIFS, and NPV/IRR live in the next chapter.
IF, AND, and OR
IF returns one value when a test is TRUE and another when it is FALSE:
=IF(test, value_if_true, value_if_false)
AND is TRUE only if every argument is TRUE. OR is TRUE if any argument is TRUE. Wrap them inside IF when you need a number or a label, not just TRUE/FALSE.
Worked example: volume-and-price commission
Apex pays a 12% commission only when unit volume is at least 10,000 and price is above $50. Otherwise commission is 10%. Revenue is $600,000.
| Driver | Cell | Value |
|---|---|---|
| Volume | B2 | 12,000 |
| Price | B3 | $55 |
| Revenue | B4 | $600,000 |
| Rate | B5 | =IF(AND(B2>=10000,B3>50),0.12,0.10) |
| Commission $ | B6 | =B4*B5 |
AND(12000>=10000, 55>50) is TRUE, so B5 = 0.12. Commission = $600,000 × 0.12 = $72,000.
If volume is 9,500 and price is $80, AND is FALSE (volume fails) even though price is high. Rate = 0.10. Commission = $60,000. That is the exam trap: AND is not "average the conditions." One FALSE kills the whole AND.
OR would fire the 12% rate if either volume or price clears the hurdle:
=IF(OR(B2>=10000,B3>50),0.12,0.10)
With volume 9,500 and price $80, OR is TRUE, rate 12%, commission $72,000. Do not confuse AND with OR on a case with two covenants (for example, leverage below 3.0x and interest coverage above 4.0x). A credit flag that uses OR will stay green when only one covenant holds.
Boolean tests inside AND/OR do not need IF. AND(B2>=10000,B3>50) already returns TRUE/FALSE. Nesting IF(B2>=10000,TRUE,FALSE) is noise and a place for parentheses to go wrong.
Nested IF Versus IFS
A nested IF chooses among more than two outcomes by putting another IF in the false branch.
Apex tax rate by EBT:
- EBT ≤ 0 → 0%
- EBT ≤ $50,000 → 15%
- EBT ≤ $200,000 → 25%
- otherwise → 35%
Nested:
=IF(B10<=0,0,IF(B10<=50000,0.15,IF(B10<=200000,0.25,0.35)))
If EBT is $80,000: first test FALSE, second FALSE, third TRUE → 25%. Tax = 0.25 × $80,000 = $20,000.
If EBT is $40,000, the second test is TRUE → 15%. Tax = $6,000.
IFS (Excel 2016+, which CFI requires) writes the same logic as a list of tests:
=IFS(B10<=0,0,B10<=50000,0.15,B10<=200000,0.25,TRUE,0.35)
The final TRUE,0.35 is the catch-all, the IFS equivalent of the last nested false-branch. Without that TRUE pair, an EBT of $500,000 returns #N/A because no test succeeded.
| Approach | When to use on FMVA | Failure mode |
|---|---|---|
| Single IF | Two-way flag (revolver on/off, mid-year vs year-end) | Over-nested when a third branch appears |
| Nested IF | Works in every Excel 2016+ file; no extra function to remember | Hard to read; easy to mismatch parentheses |
| IFS | Excel 2016+; three or more mutually exclusive buckets | No catch-all unless you add TRUE; tests must be in order |
Order matters. If you test B10<=200000 before B10<=50000, an EBT of $40,000 hits 25% and never sees 15%. Always go from the first-true bucket you actually want — usually the most restrictive cutoff first, in the same order you would read a tax table.
Exam trap: IFS does not mean "evaluate all tests and add them." It returns the result for the first TRUE test only. If you need a sum of conditions (revenue where region is East and product is A), use SUMIFS (next chapter), not IFS.
A second trap is mixing text thresholds with numbers: IFS(B10<="50000",0.15,...). The text "50000" can throw #VALUE! or sort in an unexpected order. Keep cutoffs as numbers, the same way you keep 0.25 not "25%" in a tax cell.
Error Values You Must Read, Not Hide
| Error | Typical cause in a model | First fix |
|---|---|---|
| #DIV/0! | Division by zero or a blank denominator: =B12/C12 when C12 is 0 | Guard with IF(C12=0,0,B12/C12) or fix the driver |
| #REF! | Formula points at a deleted cell, sheet, or a cut-paste that broke the link | Undo; restore the sheet; do not IFERROR it away |
| #VALUE! | Wrong type: "12%" * 1000000 if 12% is text; or a space in a number | Coerce with VALUE, or clean the input |
| #N/A | Lookup found no match (VLOOKUP/XLOOKUP/MATCH) | Check the key; use IFNA if a miss is expected |
| #NAME? | Misspelled function or named range: =IFERRORR(...) or =WACC when WACC was never defined | Fix the spelling; do not wrap in another IFERROR first |
| #NULL! | Space where a comma or colon belongs in a range | Rare; almost always a syntax typo |
| #NUM! | Invalid numeric (IRR with no sign change, too-large number) | Check cash-flow signs |
#REF! and #NAME? are structural. Blanketing them with IFERROR turns a deleted debt schedule into a quiet zero and your WACC becomes 0%. The case then values the firm with a 0% discount rate. That is a worse error than the original #REF!.
Worked example: margin and #DIV/0!
EBIT in B20 is $150,000. Revenue in B12 is $1,000,000. EBIT margin = =B20/B12 = 15%.
If you copy the row to a blank scenario column where revenue is 0, you get #DIV/0!. A clean guard:
=IF(B12=0,0,B20/B12)
That returns 0 when there is no revenue, which is a modeling choice you can explain. It is better than IFERROR here because IFERROR would also hide a #VALUE! from a texted revenue cell. If B12 is the text n.a., =IFERROR(B20/B12,0) prints 0% margin and a reviewer thinks EBIT is zero. The IF(B12=0,...) version still shows #VALUE!, which is the correct alarm.
IFERROR Versus IFNA
IFERROR(value, value_if_error) catches any error: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!.
IFNA(value, value_if_na) catches only #N/A. Every other error still surfaces.
Use IFNA on lookups where a missing ticker should become 0 or a label, but a #REF! should still scream. Use IFERROR only when you have already constrained the inner formula so the only plausible errors are the ones you intend to trap.
Worked example: peer multiple
=IFNA(XLOOKUP(A2,Peers[Ticker],Peers[EV_EBITDA]),"missing peer")
- Ticker not in the table → the label missing peer
- You accidentally deleted the EV/EBITDA column → #REF! still appears. Good.
=IFERROR(XLOOKUP(...),0) would turn that deleted column into 0, and the football-field chart would plot a zero multiple.
A second legitimate IFNA: a MATCH that is allowed to miss because that peer is not in this year's screen. A #NAME? from typing XLOKUP must not be caught by the same wrapper.
Rule: if you cannot name the error you are trapping, do not trap it. IF(B12=0,0,B20/B12) names #DIV/0!. IFNA names a failed lookup. IFERROR(everything,0) names nothing.
Boolean 1/0 Switches
Financial models use 1 and 0 as on/off switches because they multiply.
Let Growth in B5 be 6%. Let Case in B1 be 1 for the growth case and 0 for a no-growth case.
=C12*(1+B5*$B$1)
- Case = 1 → revenue grows 6%.
- Case = 0 →
1 + 0.06 * 0 = 1→ flat.
If opening revenue C12 is $1,000,000, Case 1 produces $1,060,000 and Case 0 produces $1,000,000. The same formula sits in every year. You do not rewrite the row when the case changes.
Excel treats TRUE as 1 and FALSE as 0 in arithmetic. Two common coercions:
--(B1="Upside")double-unary: TRUE → 1, FALSE → 0(B1="Upside")*1orN(B1="Upside")
Worked example: three-case switch
| Case | B1 value | Growth used |
|---|---|---|
| Downside | 1 | 2% in C5 |
| Base | 2 | 6% in C6 |
| Upside | 3 | 10% in C7 |
=C12*(1+INDEX($C$5:$C$7,$B$1))
If B1 = 2, INDEX returns 6%, $1,000,000 grows to $1,060,000. If someone types 4, INDEX returns #REF! — a loud failure, which is what you want. An IFERROR wrapper that returns 0% growth would hide the bad case number and present a flat forecast as if it were intended.
A CHOOSE version: =C12*(1+CHOOSE($B$1,0.02,0.06,0.10)). CHOOSE with B1 = 4 returns #VALUE!. Same principle: bad inputs should not look like base case.
Flags on a debt schedule: =IF($B$1=1,Revolver_draw,0) or =Revolver_draw*($B$1=1) once you are comfortable with TRUE/FALSE math. The 1/0 form copies well and stays numeric for data tables (Chapter 8). Text flags ("Yes"/"No") belong in the assumption block; convert them once to 1/0, then multiply.
Circularity Versus Real Errors
A circular reference means a formula depends, directly or indirectly, on its own cell. Excel's status bar shows Circular References and, with iteration off, the cell may compute as 0 or freeze.
Common intentional circularity in FMVA models: interest is a function of average debt, cash depends on interest, the revolver plugs cash, and the revolver is debt — so interest depends on the plug that depends on interest. CFI teaches that pattern in three-statement work (a later chapter covers circular references and iteration). The "error" is a design choice you turn on via File → Options → Formulas → Enable iterative calculation, with a max iteration count and a maximum change (for example 100 iterations, 0.001).
That is not the same as #REF!, #DIV/0!, or #NAME?.
| Symptom | Meaning | Response |
|---|---|---|
| Status bar: Circular References; model of revolver + interest | Intentional loop | Enable iteration; check the plug still ties |
| Status bar: Circular References; you did not mean to loop | Accidental (cell points at itself) | Find the cell; break the link |
| #DIV/0!, #VALUE!, #N/A, #NAME?, #REF! | Real formula error | Fix the cause; do not enable iteration |
| Values oscillate each recalc | Unstable loop or too-loose iteration | Tighten maximum change; inspect the plug |
Worked example: accidental versus intended
Accidental: in B12 you type =B12*(1+0.06). That is a self-reference. Excel flags a circular reference. There is no economic loop. Rewrite as =C12*(1+$B$5) or =B11*(1+$B$5).
Intended: Interest = average(opening revolver, closing revolver) × 8%. Closing revolver = the cash plug. Cash includes after-tax interest. Iteration converges: suppose the plug settles at $40,000, interest = 8% × $40,000 = $3,200 if you used closing balance only (average-balance math is similar once opening is known), and after tax the cash need is still $40,000 within $0.001.
If iteration is off, the intended revolver model may show 0 interest or a stale value and the balance sheet will not balance. That is a settings problem, not a #NAME? problem. If iteration is on and you still see #REF!, you deleted a sheet — iteration will not heal #REF!.
Exam rule of thumb: treat #DIV/0!, #VALUE!, #N/A, #NAME?, and #REF! as bugs in formulas or structure. Treat an announced circular reference as either (a) a revolver/interest loop you expected or (b) a cell that points at itself by mistake. Enabling iteration to silence a #DIV/0! is the wrong tool. Wrapping a circular plug in IFERROR(...,0) is how a $40,000 revolver becomes a quiet zero and the statements no longer articulate.
Apex pays 12% commission only if volume is at least 10,000 AND price is above $50; otherwise 10%. Volume is 9,500 and price is $80. What rate does =IF(AND(volume>=10000,price>50),0.12,0.10) return?
A peer-multiple lookup should show a label when the ticker is missing but still surface a broken range. Which wrapper is correct?
Interest depends on average revolver, cash depends on interest, and the revolver plugs cash. Iteration is off and the status bar says Circular References. Separately, a formula shows #REF!. What is the right diagnosis?