#NUM! and what Excel is refusing to work out

A formula that is asked for an impossible number returns #NUM!. The arithmetic is valid but the answer does not exist, or is too large to hold.

Why it happens

DATEDIF is the usual source: it counts forwards only, so a start date later than the end date returns #NUM! rather than a negative number.

The square root of a negative number does the same, as does a rate that a loan function cannot solve within its allowed number of tries.

A number larger than about 1E+308 also returns #NUM!, which is where a runaway multiplication tends to end up.

Questions

Why does DATEDIF fail on a past date?

It refuses to count backwards. Use a plain subtraction, which returns a negative number quite happily.

Is #NUM! the same as #VALUE!?

No. #VALUE! means the wrong sort of thing was passed in; #NUM! means the sort was right but the answer cannot be produced.

How do I stop it from spreading?

IFERROR around the formula keeps the rest of the sheet readable while you work out which row is causing it.

Walkthroughs that get this right

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.