Concept 1 of 61 min
References, rounding, dates and text numbers
Copying a mixed reference
| Formula in E5 | =B$2*$C4 |
|---|---|
| Copied to | F7 |
- Offset: 1 column right, 2 rows down
- B$2: column moves, B to C
- $C4: row moves, 4 to 6
- Result: =C$2*$C6
Copying moves every part not frozen by a $.
- 31 January 2027Start date
- 28 February 2027Plus 1 month: no 31st, so the last day
- 31 March 2027Plus 2 months
ROUND sends halves away from 0. Dates are serial numbers, so subtracting them gives days, and EDATE moves a date by whole months.
Numbers stored as text are ignored by SUM, and IF tests need care at the boundary. Copying moves every unfrozen part by the same offset as the cell.
Rounding halves away from 0 differs from Python's rounding to even, so the same model can differ by a cent between tools. Subtracting 2 dates counts the days between them, not both ends.
EDATE keeps the day of the month, or takes the month's last day when it does not exist; EOMONTH returns month ends.
Imported numbers often arrive as text: they look right and are silently left out of totals until converted with VALUE() or Text to Columns.
A test like A1>0 sends 0 to the 'else' branch, Loss rather than Gain, which is where formula logic usually breaks. =ROUND(-2.5, 0) gives -3 in Excel, where Python's round(-2.5) gives -2. =EDATE of 31 January 2027 plus 1 month gives 28 February 2027, and plus 2 months 31 March 2027.
- Anchoring the row when the column should be fixed.
- Counting both ends when subtracting dates.
- Missing a number stored as text in a total.
You inherit a model and a total looks too low. Walk me through how you would find the cause.
A complete answer covers
- Check whether some inputs are numbers stored as text.
- Trace precedents to see which cells the total really reads.
- Look for hard-coded overrides inside formula rows.
- Rebuild the total another way, for example with SUMPRODUCT, and compare.
Also in our technical interviews, on camera, with a score.
Concept 2 of 61 min
Lookups that do not break
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 3 of 61 min
SUMIFS, COUNTIFS and SUMPRODUCT
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 4 of 61 min
NPV, IRR, CAGR and loan payments
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 5 of 61 min
Modelling habits: circularity, scenarios and checks
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 6 of 61 min
AdvancedBreaking the circular reference with algebra
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inRecap
- $ freezes what follows it: A$1 keeps the row, $A1 the column; F4 cycles through them.
- Exact-match lookups only: VLOOKUP needs FALSE, and INDEX-MATCH or XLOOKUP survive inserted columns.
- SUMIFS and COUNTIFS take several conditions in range-criterion pairs; SUMPRODUCT multiplies then adds.
- NPV() discounts its first value one period: add today's cash flow outside it; XIRR for real dates; several sign changes can give several IRRs.
- Blue inputs, black formulas, green links; no volatile functions, no IFERROR that hides errors, no plugs.
25 questions on Excel for finance
Timed, like the real one, drawn from a bank of 33. Every answer explained.