Excel LAMBDA Lesson 7: Optional Arguments and Error Handling

📎 This article includes 1 downloadable practice file ↓

📘 Excel LAMBDA Library Course · Lesson 7 of 8

A function other people use should have sensible defaults and fail politely. Two tools make that possible.

Optional parameters

Put square brackets around a parameter to make it optional, then test it with ISOMITTED:

GST =LAMBDA(amt, [rate], amt * IF(ISOMITTED(rate), 18%, rate))

=GST(1000)        → 180   (default 18%)
=GST(1000, 5%)    → 50

Optional parameters must come after the required ones.

Guard the inputs

SAFEDIV  =LAMBDA(a, b, IF(b = 0, 0, a / b))
DOUBLEIT =LAMBDA(a, IF(ISNUMBER(a), a * 2, "Bad input"))

Return a clear text message, or NA() if the result feeds other formulas and you want the error to stay visible.

Document it

In Name Manager, fill in the Comment: “GST(amount, [rate=18%]) returns the GST amount, rounded to 2”. The comment appears as a tooltip when someone types =GST(.

Practice

Download the workbook below. The tasks call each function inline, like =LAMBDA(x,x*2)(A2), so the file works on any Microsoft 365 PC; in your own files, save them by name in Name Manager. The Check column turns green when you’re right.