Table of Contents 4. Nested LET for Engineering Constants |
Introduction
The LET function in Excel allows you to assign names to calculation
results, making complex formulas easier to read, faster to calculate, and
simpler to manage. It helps by defining variables inside a formula and then reusing
them without recalculating the same expression multiple times.
The syntax of the LET function is:
LET(name1, value1, [name2, value2], …, calculation)
Where:
- name1, name2 are the
variable names you assign.
- value1, value2 are their
values or expressions.
- The calculation is the final expression
using those variables.
Below are 20 examples showing how to use the LET function effectively in Excel.
1. Basic Arithmetic with LET
Imagine you are adding two numbers repeatedly for testing
calculations. Instead of writing a long formula every time, you can use LET to
assign names and perform the operation cleanly.
Formula:
=LET(x, A2, y, B2, x + y)
Formula Breakdown:
- x = A2 (10)
- y = B2 (5)
- Final calculation = x + y → 10 + 5 = 15
Explanation:
This formula adds two numbers by naming them as variables x and y, making the
formula short and easy to understand.
2. Sales Average Calculation
You are tracking monthly sales for a small store and want to
calculate the average sales of the months recorded.
Formula:
=LET(total, SUM(B2:B4), count, COUNTA(B2:B4), total/count)
Formula Breakdown:
- total = total sales from all months → SUM(B2:B4)
- count = number of months → COUNTA(B2:B4)
- total/count = 4000 ÷ 3 = 1333.33
Explanation:
This calculates the average sales by defining total and count separately,
reducing formula repetition.
3. Student Grading
A teacher wants to automatically assign grades to students based on
their scores.
Formula:
=LET(score, B2, grade,
IF(score>90,"A",IF(score>80,"B","C")),
grade)
Formula Breakdown:
- score = student’s score (88)
- grade = IF condition assigns
"A", "B", or "C"
- Result = "B"
Explanation:
The formula checks the student’s score and assigns the grade efficiently using
named variables.
4. Nested LET for Engineering
Constants
An engineer wants to calculate the gas equation PV = nRT, where R =
8.314.
Formula:
=LET(P, A2, V, B2, LET(R, 8.314, R*P*V))
Formula Breakdown:
- P = pressure
- V = volume
- R = gas constant
- Result = 8.314 × 500 × 0.04 = 166.28
Explanation:
This nested LET assigns constants and values to calculate efficiently.
5. Dynamic Range Sum
You are tracking daily expenses that vary in number each day. You
want Excel to sum only the filled cells.
Formula:
=LET(last, COUNTA(A2:A4),
SUM(A2:INDEX(A2:A4,last)))
Formula Breakdown:
- last = count of non-empty cells
- INDEX finds the last value in range
- SUM adds up from first to last filled cell
Explanation:
This formula automatically sums only the filled rows without updating the range
manually.
6. Text Greeting
You want to generate a personalized greeting message.
Formula:
=LET(n, A2, msg, "Hello " & n & "!",
msg)
Formula Breakdown:
- n = the person’s name
- msg = greeting text with name
- Output = "Hello Waqas!"
Explanation:
This creates an automatic greeting using a name from the cell.
7. Product Categorization
A store manager wants to label products as “High” or “Low” stock
based on quantity.
Formula:
=LET(qty, B2, status,
IF(qty>100,"High","Low"), status)
Formula Breakdown:
- qty = stock quantity
- status = IF condition
- Result = “Low”
Explanation:
The formula labels inventory based on stock level for easy tracking.
8. Double the Values
You want to double each value in a list of items for forecasting.
Formula:
=LET(arr, A2:A4, doubled, arr*2, doubled)
Formula Breakdown:
- arr = range of units
- doubled = each value multiplied by 2
- Output = {10; 16; 24}
Explanation:
Quickly doubles multiple values without additional columns.
9. Simplify Calculations
You are performing a formula repeatedly involving multiple
variables.
Formula:
=LET(x, A2*2, y, B2+5, result, x*y, result)
Formula Breakdown:
- x = base multiplied by 2
- y = multiplier plus 5
- result = x × y → 30 × 8 = 240
Explanation:
Reuses variables to simplify complex math steps.
10. Filter High Salaries
You have a list of employees and their salaries and want to see
only high earners.
Formula:
=LET(data, B2:B4, FILTER(data, data>50000))
Formula Breakdown:
- data = salary list
- FILTER returns values above 50000
- Output = {55000; 60000}
Explanation:
Displays only the salaries greater than 50,000 dynamically.
11. Full Name Creation
You have first and last names and want to combine them into one
full name.
Formula:
=LET(f, A2, l, B2, name, f & " " & l, name)
Formula Breakdown:
- f = first name
- l = last name
- Combined = "Ahmed Riaz"
Explanation:
Quickly merges first and last name into one cell.
12. Tax Calculation
You are calculating a 10% tax on a product price.
Formula:
=LET(p, A2, taxRate, 0.1, tax, p*taxRate, tax)
Formula Breakdown:
- p = product price
- taxRate = 10%
- tax = p × taxRate → 250
Explanation:
Computes the tax easily using reusable variables.
13. Circle Area
You want to calculate the area of a circle using its radius.
Formula:
=LET(pi, 3.1416, r, A2, area, pi*r^2, area)
Formula Breakdown:
- pi = 3.1416
- r = radius (7)
- area = π × r² → 153.94
Explanation:
Calculates circle area efficiently using named values.
14. Compare Two Values
You are comparing two numbers to see which is greater.
Formula:
=LET(x, A2, y, B2, result, IF(x>y,"X larger","Y
larger"), result)
Formula Breakdown:
- x = first value
- y = second value
- IF checks which is greater
Explanation:
Shows which number is larger clearly with descriptive output.
15. Positive or Negative
You need to determine if a number is positive or negative.
Formula:
=LET(num, A2, status,
IF(num>0,"Positive","Negative"), status)
Formula Breakdown:
- num = the given number
- IF test returns text result
Explanation:
Quickly identifies number sign in a readable way.
16. Square Numbers
You want to square multiple values in a list.
Formula:
=LET(arr, A2:A4, squared, arr^2, squared)
Formula Breakdown:
- arr = list of numbers
- Each squared → {4; 16; 36}
Explanation:
Squares several values using a single array formula.
17. Compound Interest
A banker calculates compound interest using principal, rate,
compounding per year, and time.
Formula:
=LET(P,A2,r,B2,n,C2,t,D2,Amt,P*(1+r/n)^(n*t),Amt)
Formula Breakdown:
- P = Principal amount
- r = Rate
- n = Compounds per year
- t = Years
- Amt = Final value after compounding
Explanation:
Accurately calculates interest growth using defined financial variables.
18. Dynamic Column Selection
You want to choose data from a column dynamically based on number
input.
Formula:
=LET(col, A2, INDEX(B2:D2,,col))
Formula Breakdown:
- col = user input column number
- INDEX = selects that column value
- Output = "Feb"
Explanation:
Fetches a column value dynamically depending on column number given.
19. Simplified Lookup
You want to find a city name based on its code using XLOOKUP.
Formula:
=LET(id, A2, result, XLOOKUP(id, B2:B4, C2:C4), result)
Formula Breakdown:
- id = value to find
- XLOOKUP = searches in column B and returns
from C
- Output = "Multan"
Explanation:
Simplifies XLOOKUP by defining lookup value first.
20. Custom Function Example
You want to create a reusable formula that calculates the square of
any number.
Formula:
=LAMBDA(x, LET(square, x^2, square))(A2)
Formula Breakdown:
- x = input number
- LET defines square = x²
- Returns the squared value
Explanation:
Combines LET and LAMBDA to make a simple custom function in Excel.
Conclusion
The LET function in Excel simplifies complex formulas by assigning
names to calculations, improving clarity and performance. It’s ideal for
advanced users who want cleaner, faster, and more manageable formulas in large
datasets.
0 Comments