Training By Himanshu

Tag: Custom Functions in Excel

  • ISOMITTED Function in Excel 365 – Complete Guide

    The ISOMITTED function is a new and specialized function available in Excel 365 that works exclusively within LAMBDA functions. It’s designed to check if an argument has been omitted when the LAMBDA function is called.

    📘 What is ISOMITTED in Excel?

    ISOMITTED checks whether a specific parameter in a LAMBDA function was provided or left out when the function was called.

    🔧 Syntax:

    =ISOMITTED(argument)
    • argument — A parameter defined in a LAMBDA.
    • Returns TRUE if the argument is omitted, FALSE if provided.

    🧠 Why is it Useful?

    It allows you to:

    • Define optional parameters in your custom functions.
    • Create default values when a parameter is not supplied.
    • Add dynamic behavior depending on whether a user provided an input.

    ✅ Example 1: Optional Discount Argument

    Let’s define a function that calculates the total price after an optional discount.

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100)

    💡 Since discount is omitted, it returns 100 — the original price.

    But:

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100, 0.2)

    💡 Returns 80 after applying the 20% discount.

    ✅ Example 2: Creating a Named Function

    You can also create a reusable function:

    1. Go to Formulas > Name Manager > New
    2. Name: SmartDiscount
    3. Refers to:
    =LAMBDA(price, discount,
    IF(ISOMITTED(discount), price, price - price * discount)
)

    Now you can use:

    =SmartDiscount(200)        → returns 200  
=SmartDiscount(200, 0.1)   → returns 180

    🛑 Limitations

    • Can only be used inside a LAMBDA function
    • Not available outside that context
    • Only works in Excel 365 and Excel for the Web

    🎯 Use Cases

    • Building reusable Excel mini-apps
    • Creating optional inputs in custom functions
    • Creating smarter calculators with defaults

    🎓 Want to Learn More Excel 365 Advanced Features?

    If you want to explore LAMBDA, ISOMITTED, LET, and more dynamic Excel tools, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • Excel 365-exclusive functions
    • LET, LAMBDA, XLOOKUP, FILTER
    • Real-life examples and automations
    • Projects and templates for professionals

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 LAMBDA Function Explained: Make Your Own Formulas Without VBA

    The LAMBDA function in Excel 365 is a powerful and advanced feature that allows you to create custom functions without VBA or macros. It’s like writing your own Excel formula and saving it as a function you can reuse across the workbook.

    Let’s walk through it step by step with simple explanations and examples.

    🧠 What is the LAMBDA Function in Excel?

    The LAMBDA function allows you to:

    • Define custom functions using Excel formulas
    • Reuse logic without copying complex formulas
    • Replace repetitive expressions
    • Avoid writing VBA or using Add-ins

    🔧 Syntax:

    =LAMBDA(parameter1, parameter2, ..., calculation)

    You define inputs (parameters) and use them in the calculation.

    ✅ How to Use the LAMBDA Function

    🔸 Step 1: Create a Simple LAMBDA Formula

    Example: Create a LAMBDA to square a number.

    =LAMBDA(x, x^2)(5)
    • Here, x is the input.
    • x^2 is the formula.
    • (5) is the value passed to the function.

    ✅ Output: 25

    🔸 Step 2: Create a LAMBDA Function for Reuse

    You can also name your custom LAMBDA function for repeated use.

    🧭 Steps:

    1. Go to Formulas > Name Manager
    2. Click New
    3. In Name, type: SquareNum
    4. In Refers to, enter: =LAMBDA(x, x^2)
    5. Click OK.

    Now you can use your new function like any built-in Excel function:

    =SquareNum(6)

    ✅ Output: 36

    📘 Real-Life Examples of LAMBDA

    🔸 Example 1: Calculate Profit Margin

    =LAMBDA(cost, price, (price - cost)/price)

    Use it as:

    =LAMBDA(cost, price, (price - cost)/price)(100, 150)

    ✅ Output: 0.333 (or 33.3%)

    🔸 Example 2: Fahrenheit to Celsius Converter

    =LAMBDA(f, (f - 32) * 5/9)(98.6)

    ✅ Output: 37°C

    🔸 Example 3: Named Reusable LAMBDA for Area of a Circle

    1. Go to Name Manager > New
    2. Name: CircleArea
    3. Refers to:
    =LAMBDA(r, PI()*r^2)

    Use it in a cell:

    =CircleArea(5)

    ✅ Output: 78.54

    🛑 Important Notes

    • LAMBDA must end with a calculation using defined parameters.
    • You can nest LAMBDAs for advanced logic.
    • Works only in Excel 365 (and Excel for the web).
    • Doesn’t run without input — i.e., you must “call” it at least once for testing.

    🎓 Want to Master More Advanced Excel Tools?

    If you’re ready to build powerful logic, reusable formulas, dashboards, and even Excel apps without coding, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • LET, LAMBDA, FILTER, XLOOKUP
    • Real-world dashboards
    • Excel automation & templates
    • For beginners to advanced users

    🎯 Click to Enroll Now

    Best selling products

  • How to Create a User Defined Function in Excel to Identify Triangle Types

    🧠 Storytime: Why Rohan and Meera Needed a Triangle Function in Excel

    Rohan and Meera are engineering students in Pune. While working on a school project about geometry and architecture, they had to classify different types of triangles based on side lengths.

    They had a long list of side measurements in Excel. Typing the triangle types manually was slow and error-prone.

    Meera asked, “Can’t we just create a formula in Excel that tells us if the triangle is Equilateral, Isosceles, or Scalene?”

    Rohan replied, “Excel has IF and nested conditions, but it’ll get messy. Let’s write a User Defined Function using VBA!”

    Thus began their journey into VBA.

    🔧 What is a User Defined Function (UDF) in Excel?

    A User Defined Function is a custom function written in VBA (Visual Basic for Applications) that works like a built-in Excel formula.

    With a UDF, you can extend Excel’s capabilities beyond standard formulas.

    🧮 Goal: Create a Function to Determine Triangle Type

    Based on the lengths of the three sides, the function should return:

    • Equilateral – All three sides are equal.
    • Isosceles – Any two sides are equal.
    • Scalene – All sides are different.
    • Not a Triangle – The side lengths don’t form a valid triangle.

    🛠 Step-by-Step: Creating the UDF in Excel

    ✅ Step 1: Open the VBA Editor

    1. Press Alt + F11 in Excel.
    2. In the VBA Editor, go to Insert > Module.
    3. A new module window opens.

    ✅ Step 2: Paste the VBA Code

    Function TriangleType(a As Double, b As Double, c As Double) As String
    ' Check if the sides can form a triangle
    If a + b <= c Or a + c <= b Or b + c <= a Then
        TriangleType = "Not a Triangle"
    ElseIf a = b And b = c Then
        TriangleType = "Equilateral"
    ElseIf a = b Or b = c Or a = c Then
        TriangleType = "Isosceles"
    Else
        TriangleType = "Scalene"
    End If
End Function

    ✅ Step 3: Save and Return to Excel

    • Press Ctrl + S and close the VBA Editor.
    • Make sure your file is saved as .xlsm (Macro-enabled workbook).

    📊 Step 4: Use the Function in Excel

    In your worksheet, enter side lengths in three cells (say A2, B2, and C2), and in D2 write:

    =TriangleType(A2, B2, C2)

    ✅ It will return one of:

    • “Equilateral”
    • “Isosceles”
    • “Scalene”
    • “Not a Triangle”

    💡 Example:

    ABCType
    555Equilateral
    668Isosceles
    754Scalene
    123Not a Triangle

    📘 Bonus: Learn More with a Complete Excel Course!

    Just like Rohan and Meera used Excel creatively, you can too!

    📌 If you want to learn Excel from basic to advanced, including formulas, charts, data tools, and VBA, check out:

    🎓 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Learn practical Excel skills
    ✔️ Master formulas, charts, PivotTables, VBA & more
    ✔️ Ideal for students, professionals, entrepreneurs

    👉 Enroll Now and level up your career with Excel mastery.

    On sale products