68 questions
15
votes
8
answers
11k
views
Generate All Permutations in Excel using LAMBDA
This is a frequently asked and answered question: how can I generate all permutations in Excel:
2011
2016
2017
2017 superuser
2018
2021
and now in 2022 which did not get an answer before it was closed ...
- 3,392
9
votes
1
answer
1k
views
Excel's LAMBDA with a "kind of" composite function
Ever since I learnt that Excel is now Turing-complete, I understood that I can now "program" Excel using exclusively formulas, therefore excluding any use of VBA whatsoever.
I do not know if ...
- 1,193
4
votes
2
answers
926
views
Recursive LAMBDA to replace characters by specific substitutes from a lookup table
The goal is to iterate through rows of the character table and replace each character with it's substitute.
The character table in this example is ={"&","&";"<&...
- 184
4
votes
1
answer
2k
views
How to share generic LAMBDA-functions over different projects?
Given a generic LAMBDA-UNION-function like this: https://stackoverflow.com/a/69861437/16578424:
What would be the best method to reuse it in several projects?
- 13.4k
3
votes
3
answers
257
views
Capitalize only the first letter with LAMBDA in Excel
In order to capitalize only the first letter of a text I used
=UPPER(LEFT(A1,1)) & LOWER(RIGHT(A1,LEN(A1)-1))
With the introduction of LAMBDA function in Excel, how can I create a custom lambda ...
- 24.8k
3
votes
3
answers
368
views
How can I get this LAMBDA function to accept an array as input?
I'm using this Lambda function to calculate the Levenshtein distance between two strings. In Excel it's called LEV.
=LAMBDA(a,b,[ii],[jj],[arr],
LET(
i,IF(ISOMITTED(ii),1,ii),
...
- 284
3
votes
2
answers
957
views
Add Together a Set of Numbers using LAMBDA Recursion in Excel
I have a continuous set of numbers in cell I32 (123456789) of my worksheet. I want to use the new LAMBDA function to add each number with nine iterations (using recursion). The final value in cell L32 ...
- 478
2
votes
2
answers
815
views
Makearray function in Office Excel unable to generate proper amount of columns for the array
I am using Office 365 currently and I want to make a visualization tools using MAKEARRAY functions.
For example, if I want to display sequential of 32 items, I would display it in this way:
I use the ...
- 423
2
votes
2
answers
435
views
Excel - Working with incremental numbers, series (summing, finding values, etc)
Context / Example
I am working with some datasets that have incrementing numbers¹. For example, I mean where the cost of an upgrade gets more and more expensive for each level you go up.
Level
Cost
4
...
- 424
2
votes
3
answers
1k
views
Is it possible to step through a row of data and sum every n-th cell using a Lambda function?
I have a model that produces an array of values that can be several hundred columns wide. Every 25th column contains a number that I need to add to the total.
I thought the cleanest solution would be ...
- 23
2
votes
0
answers
137
views
Calling Excel Add-In functions from within MAP or MAKEARRAY
Using Office365 desktop I'm trying to call add-in functions from within either MAP or MAKEARRAY so it can spill alongside an existing spill. I don't know how many rows will be in the spill, so being ...
- 97
2
votes
2
answers
229
views
Calculate orders with all lines available after prior orders
I have the following data:
order
part
cat
qty available
qty ordered
priority
aa
a
1
4
2
2
aa
a
2
2
3
2
bb
b
1
3
3
3
bb
a
1
4
3
3
cc
a
2
2
1
1
cc
a
1
4
2
1
With this data I'd like to calculate the ...
- 12k
1
vote
2
answers
145
views
Automatically add a number of row cells and subtract a 3rd row over a variable number of columns in Excel
I have the following function that automatically sums 3 rows together for a specified number of cells determined by D2:
=BYCOL((INDEX($1:$11,{4;5;10},SEQUENCE(1,D2,COLUMN(I:I)))),LAMBDA(x,SUM(x)))
The ...
1
vote
2
answers
130
views
Advanced problem of finding minimum sum of N consecutive numbers with a twist
Yesterday I have asked a similar question, yet this one has a rather advanced addition:
Let us say we have a dataset that consists of Hotel and Airline prices (From 1st to 31st of Jan). I would like ...
- 59
1
vote
1
answer
693
views
Create an array from hard-coded values for use in a LAMBDA function
I have recently created a LAMBDA function for recursive substitutions of substrings which works when using a range:
LAMBDA named ReplaceArray
=LAMBDA(str, list, sub, IF(ROWS(list)=1, SUBSTITUTE(str,...
- 3,689
1
vote
1
answer
445
views
ByCol conditional Lambda
I am using the following function to calculate the sum of specific rows and I'd like to modify it to sum different rows if there's a value in the cell of a specific row as it's traversing the sequence....
1
vote
1
answer
80
views
Excel formula to remove the empty rows at the bottom from a range
In Excel, I have a range B4:E100 in a worksheet, assume the content is like the follows. I'm looking for a formula (as simple as possible, with new Excel functions probably) that returns B4:E8, ...
- 5,770
1
vote
1
answer
483
views
Extended: How to adjust this LAMBDA Excel formula to result multiple columns of price data?
In relation to my last question at How can I fix this Excel LAMBDA formula to result all SKUs and prices, type of Unpivot formula
@Ike created this LAMBDA which worked perfectly for the 3 columns of ...
- 93
1
vote
3
answers
2k
views
Increment numbers when value changes in another column
I currently have a column of letters in B2:B11 alongside numbers that increment by 1 when letters appear consecutively (C2:C11). When a new letter appears, the sequence resets and starts from 1 again.
...
- 478
1
vote
1
answer
100
views
How can I fix this Excel LAMBDA formula to result all SKUs and prices, type of Unpivot formula
I'm trying to make a formula which will unpivot some price data, there would several extra columns of data later, including some SKU data, and then 3 columns of price, 3 columns of net price, and the ...
- 93
1
vote
1
answer
790
views
Loop through an array in a single worksheet cell
I am trying to calculate an amount based on a formula that has a different number of arguments for each calculation. And, each formula is expressed as a string that is based on the column names at ...
- 31
1
vote
1
answer
203
views
Excel dynamically calculate cost of products based on bill of materials (BOM) and purchase price history
EDIT 1 → 2024-03-30:
Included parameters in the lambda functions so the table names and columns may be injected by the user
Updated lambda functions with such paramteres
Even though I found a ...
- 5,726
1
vote
0
answers
265
views
Recursive Lambda Confusion
I'm trying to build a Schedule Generator using recursive LABMDA based on RANDBETWEEN function.
Basically, what it needs to do:
Generate a random date using RANDBETWEEN based on 2 other cells which ...
- 11
1
vote
1
answer
292
views
Can "Match Mode" functionality be created within a LAMBDA function in Excel?
I'm just starting to play around with LAMBDA, and was wondering if it was possible to replicate the mode selection that a few formulas have. For instance when using XLOOKUP:
"Match Mode" ...
- 307
0
votes
3
answers
7k
views
Repeat a range a number of times
I have a simple problem, but I am not quite able to figure out the answer
Given a range (in blue), and a number of times to be repeated (in orange), I need to create 2 spill arrays in vertical:
the ...
- 1,193