This tutorial introduces Excel's new LET function, enhancing formula readability and calculation speed. For users of lengthy Excel formulas, named ranges improve readability. The LET function expands on this by assigning names to calculations and values directly within a formula. If a formula uses the same expression repeatedly, LET calculates it once, stores the result, and reuses it.

Excel LET Function: Syntax and Basic Usage

The LET function assigns names to calculation results and defines variables within a formula, improving clarity and speed. This mirrors the Name Manager but with names scoped only to the formula.

Syntax:

LET(name1, name_value1, [name2], [name_value2], ..., calculation)

Where:

• name1 (required): The first name assigned (must start with a letter).
• name_value1 (required): The value or calculation assigned to name1.
• name2/name_value2 (optional): Subsequent name-value pairs.
• calculation (required): The calculation using the assigned names.

Up to 126 name/value pairs are supported. Microsoft's syntax notation may differ slightly, but the functionality remains the same.

Availability:

LET is available in Excel 365 (Windows and Mac), Excel 2021 (Windows and Mac), and Excel for the web.

Basic LET Formula:

A simple example clarifies the concept. Consider x y. Assigning x = 2 and y = 5, and multiplying them:

=LET(x, 2, y, 5, x * y)

This returns 10. Variables can also be cell references:

=LET(x, B2, y, B3, x * y)

The assigned names appear in the IntelliSense dropdown, similar to the Name Manager.

While =B2 * B3 is simpler in this case, LET's power becomes apparent in more complex formulas.

Advantages:

• Simplified Formulas: Descriptive names improve understanding.
• Faster Calculations: Repeated expressions are calculated only once. This is especially beneficial with long formulas and large datasets.
• Easy Maintenance: Updating repeated calculations requires only a single change.

Usage Notes:

• A LET formula requires an odd number of arguments: name/value pairs and a final calculation.
• Follow Excel's naming conventions (no spaces or punctuation).
• Avoid name conflicts with the Name Manager. LET's names are local to the formula.

Formula Examples:

Example 1: Reducing Repeated Calculations

Consider student exam scores (columns B, C, D) and grading based on average:

The formula =IF(AVERAGE(B2:D2)>249, "Excellent", IF(AVERAGE(B2:D2)>=200, "Good", IF(AVERAGE(B2:D2)>150, "Satisfactory", "Poor"))) repeats AVERAGE(B2:D2). Using LET:

=LET(avg, AVERAGE(B2:D2), IF(avg>249, "Excellent", IF(avg>=200, "Good", IF(avg>150, "Satisfactory", "Poor"))))

This calculates the average only once. Adding another exam column requires updating only the AVERAGE function within LET.

Example 2: Multiple Names

Extracting middle names from a full name (column A) using:

=MID(A2, SEARCH(" ", A2) 1, SEARCH(" ", A2, SEARCH(" ", A2) 1) - SEARCH(" ", A2) - 1)

Can be improved with LET:

=LET(full_name, A2, space, SEARCH(" ", full_name), MID(full_name, space 1, SEARCH(" ", full_name, space 1) - space - 1))

This enhances readability.

Example 3: Dynamic Arrays

LET can handle arrays. To get exams within the next n days (excluding today), using FILTER:

=LET(data, A2:C19, dates, C2:C19, today, TODAY(), n, F3, FILTER(data, (dates>today) * (dates

Troubleshooting:

• LET not found: Ensure you have a compatible Excel version.
• #NAME? error: Check for spelling errors in names.
• Invalid names: Names must be unique and follow Excel's naming rules.

The LET function significantly improves formula efficiency and readability in Excel. A practice workbook is available for download.

(Note: The image URLs are placeholders and need to be replaced with actual image URLs.)

The above is the detailed content of Excel LET function with formula examples. For more information, please follow other related articles on the PHP Chinese website!