Step-by-step example
To illustrate how ISOMITTED works, imagine a simple LAMBDA formula that adds 10 any given value. With the value 100 in cell A1, this formula will return 110: Next, we alter the formula to make both a and b variables: With 100 in A1, if we supply 10 for b, the formula returns 110. If we supply 20 for b, the formula returns 120. So far so good. Now let’s say we want to make b optional, and we want b to default to 10 if not provided. To accomplish this, we can use ISOMITTED to check for b. We perform this check inside the IF function, then we run one calculation if b is provided and a another calculation if b is not provided: Finally, we place the snippet above into the LAMBDA function as before. We also enclose b in square brackets [b] to follow the convention of optional arguments in Excel: Now if we don’t provide a value for b, the formula returns a + 10. If we do provider a value for b, the formula returns a + b. Note: the formulas above are using the special syntax for LAMBDA functions, where argument values are provided after the function in parentheses. Read more about the LAMBDA function here.
Worksheet Example
In the worksheet shown above, we are using the LAMBDA function to check password length. The LAMBDA takes two arguments, a and b. A is the password to check, and b is the required length. B is an optional argument and defaults to 8 when not provided. The formula in D5, copied down, is: Since b is not supplied, the passwords in column B are checked for a minimum length of 8 characters. The formula returns TRUE if a password is at least 8 characters long and FALSE if not. To check for a minimum length of 10 characters, simply provide a value for b: In this version, b has been provided as 10. The formula returns TRUE if a password is at least 10 characters long and FALSE if not. The screen below shows how the formula works after we have created a named LAMBDA function called “PasswordCheck” with the formula above:
The formula in D5 checks for a length of 8 characters by default. The formula in F5 checks for a length of 10 characters:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
