Excel formula library
Excel Tutorial
Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.
Formula list
Find any Excel formula
Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.
- Math & Arithmetic 12
- Statistical Analysis 15
- Logical & Conditional 6
- Lookup & Reference 13
- Date & Time 17
- Text & Strings 25
- Information & Arrays 12
- Dynamic Arrays (365) 6
- Enhanced Lookup (365) 2
- Advanced Calculation (365) 6
- Text & Reshaping (365) 13
No formulas match that search or filter.
Chapter 10
Advanced Calculation (365)
-
109 LET =LET(name1, name_value1, [name2, name_value2, ...], calculation) Allows declaring named variables within a formula. It avoids repeating duplicate expensive calculations, drastically accelerating workbook recalculation speed.
Syntax
=LET(name1, name_value1, [name2, name_value2, ...], calculation)Arguments
- name1
- A valid variable name (must start with a letter, cannot be a cell coordinate like C1).
- name_value1
- The value or intermediate calculation assigned to name1.
- calculation
- The final calculation that uses all defined variable names.
Sample
=LET(price, B2, gst, 0.18, price + (price * gst))Result 1180
Sample data
DownloadA B C D 2 SSD Drive 1TB 1,000.00 18% 1,180.00 3 Mechanical Keyboard 2,500.00 18% 2,950.00 4 USB-C Hub 800.00 18% 944.00 Pro tipYou can declare up to 126 pairs of names and values! Format complex LET formulas with Alt+Enter line breaks for pristine readability.
Common pitfallVariable names cannot conflict with existing Excel cell references (e.g. do not name a variable C1 or R1C1).
-
110 LAMBDA =LAMBDA([parameter1, parameter2, ...], calculation)(arguments) Enables users to write custom user-defined functions (UDFs) natively in Excel. Once named in the Name Manager, your LAMBDA functions can be reused like built-in functions.
Syntax
=LAMBDA([parameter1, parameter2, ...], calculation)(arguments)Arguments
- [parameter1, ...]
- Values you want to pass into the custom function.
- calculation
- The computation formula to execute on the parameters.
- (arguments)
- Test arguments passed at the end when testing in a cell directly.
Sample
=LAMBDA(p, r, t, (p*r*t)/100)(B2, 8.5, 3)Result 25500
Sample data
DownloadA B C D 2 1,00,000 8.5% 25,500 1,25,500 3 2,50,000 8.5% 63,750 3,13,750 4 5,00,000 8.5% 1,27,500 6,27,500 Pro tipSave your LAMBDA in Formulas > Name Manager with a name like SIMPLE_INTEREST. Then anywhere in the workbook, type =SIMPLE_INTEREST(A2, B2, 3)!
Common pitfallWhen testing a LAMBDA directly in a cell, you MUST supply the values at the end in parentheses: =LAMBDA(x, x*2)(10), otherwise #CALC! is returned.
-
111 MAP =MAP(array1, [array2, ...], lambda) Transforms every element of one or more arrays by applying a custom LAMBDA rule. Enables element-by-element iterative mapping across arrays.
Syntax
=MAP(array1, [array2, ...], lambda)Arguments
- array1, [array2]
- One or more arrays to iterate over.
- lambda
- A LAMBDA that takes as many parameters as arrays supplied and returns transformed values.
Sample
=MAP(B2:B5, C2:C5, LAMBDA(u, p, u*p))Result 1,500 (Spills)
Sample data
DownloadA B C D 2 Keyboard 10 150 1,500 3 Mouse 25 80 2,000 4 Headset 5 400 2,000 Pro tipMAP is ideal when standard Excel functions do not support array broadcasting natively over multiple parameters.
Common pitfallThe LAMBDA passed to MAP must take the exact same number of arguments as there are input arrays.
-
112 REDUCE =REDUCE([initial_value], array, lambda) Reduces an array to an accumulated single value by applying a LAMBDA function step-by-step across all elements (similar to fold/reduce in modern programming).
Syntax
=REDUCE([initial_value], array, lambda)Arguments
- [initial_value]
- The starting value for the accumulator.
- array
- The array of values to reduce.
- lambda
- A LAMBDA with 2 parameters: (accumulator, value).
Sample
=REDUCE(0, B2:B5, LAMBDA(acc, val, acc + val*1.1))Result 1100
Sample data
DownloadA B C D 2 Q1 200 +10% Inflation Budgeted 3 Q2 300 +10% Inflation Budgeted 4 Q3 250 +10% Inflation Budgeted Pro tipTo remove multiple special characters from text, pass an array of characters: =REDUCE(A1, {"-","/","."," "}, LAMBDA(str, ch, SUBSTITUTE(str, ch, ""))).
Common pitfallEnsure your lambda accepts exactly two arguments: accumulator and item.
-
113 SCAN =SCAN([initial_value], array, lambda) Scans an array by applying a custom LAMBDA to accumulate values, but unlike REDUCE, SCAN returns every intermediate accumulated step, generating instant running totals.
Syntax
=SCAN([initial_value], array, lambda)Arguments
- [initial_value]
- The starting value for the scan.
- array
- The sequence of values to process.
- lambda
- A custom LAMBDA with 2 parameters: (accumulator, value).
Sample
=SCAN(0, B2:B5, LAMBDA(acc, val, acc + val))Result 10,000 (Spills)
Sample data
DownloadA B C D 2 Mon 10,000 10,000 Verified 3 Tue 15,000 25,000 Verified 4 Wed 8,000 33,000 Verified Common pitfallPassing non-numeric values when performing arithmetic additions in the lambda will result in #VALUE! errors.
-
114 MAKEARRAY =MAKEARRAY(rows, cols, lambda)
Syntax
=MAKEARRAY(rows, cols, lambda)Arguments
- rows, cols
- Number of rows and columns in the array to create.
- lambda
- A custom LAMBDA taking 2 arguments: (row_index, col_index).
Sample
=MAKEARRAY(3, 3, LAMBDA(r, c, r * c * 10))Result 10 (Spills 3x3)
Sample data
DownloadA B C D 2 10 20 30 Row 1 scaled 3 20 40 60 Row 2 scaled 4 30 60 90 Row 3 scaled Pro tipCombine with CHAR: =MAKEARRAY(5, 5, LAMBDA(r, c, "ID-" & r & "-" & c)) creates a clean grid of unique identifiers.
Common pitfallrows and cols cannot be negative or zero. Doing so returns a #VALUE! error.