Excel’s ISOMITTED function opens new possibilities for LAMBDA functions by letting you handle optional parameters. It returns TRUE when an argument is missing and FALSE when it’s present. This simple yet powerful feature helps you build more adaptable formulas for your spreadsheets.

Create Your First ISOMITTED Function in Excel

Let’s start with a basic example that shows how ISOMITTED works in practice.

Step 1: Set Up Your First LAMBDA Function

=LAMBDA(required_param, optional_param,

IF(ISOMITTED(optional_param),

required_param,

required_param & ” ” & optional_param))

Step 2: Name Your Function

1. Open the Name Manager (Formulas tab > Name Manager)
2. Click “New“

1. Enter a name (e.g., “CombineText”)
2. Paste your LAMBDA function
3. Click “OK“

Step 3: Test the Function

Try these variations:

Input	Result
=CombineText(“Hello”)	Hello
=CombineText(“Hello”, “World”)	Hello World

Building a Simple Optional Parameter System

Let’s create a more practical function that calculates discounted prices.

Step 1: Create the LAMBDA Function

=LAMBDA(price, discount_rate,

IF(ISOMITTED(discount_rate),

price,

price * (1 – discount_rate)))

Step 2: Apply Default Values

1. Name your function (e.g., “CalculateDiscount“)
2. Test with single and multiple parameters:

Input	Result
=CalculateDiscount(100)	100
=CalculateDiscount(100, 0.2)	80

Adding Multiple Optional Parameters to LAMBDA Functions

Now let’s build a function that handles several optional inputs.

Create a Multi-Parameter Function

=LAMBDA(base_price, discount_rate, tax_rate,

LET(

price, IF(ISOMITTED(discount_rate), base_price, base_price * (1-discount_rate)),

final_price, IF(ISOMITTED(tax_rate), price, price * (1+tax_rate))

final_price

))

Test different parameter combinations:

Input	Output
=PriceCalc(100)	100
=PriceCalc(100, 0.2)	80
=PriceCalc(100, 0.2, 0.1)	88

Creating Flexible Calculation Functions

Let’s build a versatile averaging function that accepts optional weights.

=LAMBDA(values, weights,

IF(ISOMITTED(weights),

AVERAGE(values),

SUMPRODUCT(values, weights)/SUM(weights)))

This function:

• Takes a range of values
• Accepts optional weights
• Returns simple average if weights are missing
• Calculates weighted average if weights are provided

Note: The formula was used to define a name and the name was used to validate the formula (Formula > Name Manager > New).

What Happens When Arguments Are Missing?

ISOMITTED helps handle missing arguments gracefully:

1. Returns TRUE when parameter is missing
2. Enables default value implementation
3. Allows parameter validation

Example error handling:

=LAMBDA(value, minimum,

IF(ISOMITTED(minimum),

value,

IF(value < minimum, minimum, value)))

Note: The formula was used to define a name and the name was used to validate the formula (Formula > Name Manager > New).

Combining ISOMITTED with Other Excel Functions

Create powerful combinations with:

• IF statements for conditional logic
• LET functions for variable assignment
• MAP for array operations

Example:

=LAMBDA(range, filter_value,

LET(

filtered, IF(ISOMITTED(filter_value),

range,

FILTER(range, range>filter_value)),

AVERAGE(filtered)

))

Note: The formula was used to define a name and the name was used to validate the formula (Formula > Name Manager > New).

Real-World Applications

Practical uses include:

• Financial models with optional scenarios
• Report generators with configurable outputs
• Data analysis with flexible parameters

Next Steps with ISOMITTED

ISOMITTED makes your LAMBDA functions more flexible and user-friendly. Start small, experiment with different combinations, and gradually build more complex functions.

Ready to take your spreadsheet capabilities further? Try Coefficient to connect your Excel sheets with live data from 50+ business systems.