68 questions
Best practices
1
vote
5
replies
100
views
SpellNumber Simplified Using LAMBDA() function
Here is the custom function to spell number to words using LAMBDA(). I would appreciate any improvement of the function or simplification if possible. Also suggest best practice for the function.
=...
- 38k
0
votes
1
answer
205
views
Error with Excel Lambda function in Name manager
I have set up a Lambda formula that calculates interpolated values from a given set of coordinates
similar to existing trend/linest/forecast functions
handles non consecutive inputs as well as errors ...
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,760
-1
votes
1
answer
114
views
Create custom cell reference/array in formula without enclosing string parameters in quotation marks
For work I created a workbook that references data from another tab, does some filtering and/or calculations with the data, then plots it. The workbook also has form controls for the user to change ...
- 7
-1
votes
1
answer
312
views
VSTACK and REDUCE(?) to expand column [duplicate]
I'm trying to
expand a column with MAKEARRAY, using instances to determine number of rows that should be populated with a value and then
using REDUCE to VSTACK multiple of these arrays together
I ...
- 156
1
vote
3
answers
430
views
REDUCE with conditional VSTACK inside LAMBDA. Is it possible?
I'm trying to build a VSTACK from a dynamic range using REDUCE.
What I'm getting:
What I want (note ignored XX value)
Essentially, the question is how to use conditional HSTACK inside LAMBDA inside ...
- 156
3
votes
2
answers
440
views
K-means Clustering Excel Lambda Function
I mused about how hard it would be to create an Excel Lambda function to deliver K-means clusters. I built a one-dimensional version of it with only a small investment of time.
One dimensional ...
- 3,392
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
3
votes
3
answers
367
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
2
votes
2
answers
340
views
Excel, Expand multiple ranges in one array formula
I have a workbook where I want to check for gaps in allocated ranges and I am struggling to build the ranges so that I can stack together the arrays. Here is a simplified statement of what I want to ...
0
votes
2
answers
1k
views
Lambda function calling another lambda function in Excel?
I am trying to create an Excel lambda function that calls another lambda function. But Excel returned #VALUE! when I ran it. Does Excel support this kind of operation?
- 689
0
votes
1
answer
146
views
Microsoft documents LAMBDA attached to a defined name. Is this possible to create with Openpyxl?
I have a complicated workbook that I am generating with Openpyxl. I've decided to revise the formulas to use Excel's so-called future functions and array functions. Some of the formulas are getting ...
- 31
1
vote
1
answer
825
views
In Office365 Excel, how to define a named lambda function without using the Name Manager "Refers to" field?
Preferably the lambda function defined in the worksheet. Google Bard said to use the name() function, but there is no such function in Excel. Also tried the "Create from Selection" ...
user2847853
0
votes
1
answer
1k
views
What prevents a lambda function from evaluating on an array?
I thought that a lambda function defined for scalar input values would automatically iterate through an array when faced with an array input, but it seems that it's not universally true. I want to ...
- 1
1
vote
2
answers
1k
views
Using Office365 Excel array formulas, how to vertically stack a variable number of arrays from a single sheet?
Each array is listed horizontally, has n columns and a variable number of rows. Every array has the same n number of columns. There is a blank column between each array.
They can be combined using ...
user2847853
2
votes
3
answers
904
views
Understanding Excel lambda
I am trying to understand better how does the recursivity works in Excel using the Lambda function. I didn't manage to wrap my head around how to increment the "value" the function is at.
...
- 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
432
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
0
answers
107
views
Adding a LAMBDA formula forces other users to reopen spreadsheet
Issue: As the title says, any time a LAMBDA formula is used in my spreadsheet it forces all other users who have the shared spreadsheet open to reopen the spreadsheet.
With my brain's limited capacity,...
- 432
6
votes
2
answers
2k
views
TEXTSPLIT combined with BYROW returns an unexpected result when using an array of strings as input
I am testing the following simple case:
=LET(input, {"a,b;c,d;" ; "e,d;f,g;"},
BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE))...
- 6,808
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
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
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
0
votes
1
answer
3k
views
How to use Lambda in Excel to return array of results for an array
I am becoming more and more familiar with Excel's new array functionality and absolutely love the direction it is all headed.
I've been trying to find a way to have a single formula which will spill ...
- 93
1
vote
1
answer
2k
views
Excel: #CALC! error (Nested Array) when using MAP functions for counting interval overlaps
I am struggling with the following formula, it works for some scenarios but not in all of them. The name input has the data set that is failing, getting an #CALC! error with the description "...
- 6,808
1
vote
1
answer
443
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
2
answers
353
views
Repeat values n times where n could also be 0.5
I saw the following question on here regarding repeating values:
List number of lessons including half lessons based on Number of lessons and lesson name
This question needed an older Excel version, I ...
- 12k
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
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
1
answer
1k
views
Excel function UNIQUE is not working on MAKEARRAY and on RANDARRAY (multi-dimension array)
I have a question about UNIQUE function for random number generation in multi-dimension array.
As you can see, I try to generate an array with random and unique numbers of 1->100 inside 5X5 array.
...
- 423
0
votes
1
answer
63
views
Adding Tolerances to Excel LAMBDA functions
I created a LAMBDA function called LOANAMT to calculate loan amounts recursively for a situation in which you need to borrow to fund the loan payment (yes, I know this can be solved algebraically - I'...
2
votes
2
answers
814
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
0
votes
1
answer
419
views
Calculate production capacity per product/day up to goal
I have the following data.
Available resources data per day:
A
B
C
D
E
F
G
H
I
J
K
L
M
N
2
resources
day
3
1
2
3
4
5
6
7
8
9
10
11
12
4
empl.1
8
8
4
2
2
4
4
8
8
5
empl.2
8
4
4
8
4
8
6
empl.3
And ...
- 12k
1
vote
0
answers
484
views
Performance problem with INDEX function and names introduced inside LET/LAMBDA
Excel's INDEX function shows strange behaviour when the object being indexed is a name introduced in a LET construct or a parameter of a LAMBDA. The behaviour is consistent on Windows and Mac.
Suppose ...
- 13.8k
0
votes
0
answers
246
views
Excel not recognizing this LET and LAMBDA combo formula as a formula (receiving 'there's a problem with this formula' error)
Excel is not recognizing this LAMBDA/LET combo formula as a formula (receiving the 'there's a problem with this formula' error). I thought it might have been something to do with the variable names I ...
- 21
0
votes
1
answer
727
views
Formula to automatically subtract and multiply two rows over a variable number of columns
I have the following sum in G4 =SUM((G2:G3)*$D$2) and in G6 I have =SUM(G4-G5), I then drag copy them for the duration denoted in C1. I'd like to change this so I have a single formula in H4 and H6 ...
1
vote
1
answer
262
views
Including additional table columns into Let function constructed table with the ability to sort based on column value Excel
I have the following function giving a list of available staff after a specified date set in T2
=LET(uniqueEmployees,UNIQUE(AllStaffProjectAllocationTbl[Employee]), maxDatePerEmployee,BYROW(...
0
votes
1
answer
964
views
Limit the number of results when using Excel array functions
I am trying to use the SortBy function to return the top 3 results from a table that I have. If I put the function somewhere else in the sheet, I can just reference the first three cells, and that ...
- 22
1
vote
1
answer
789
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
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
votes
1
answer
155
views
Create an array of the dates of the last X times something happened, including duplicates [closed]
I'm a very advanced Excel user and have used dynamic arrays and complex lambdas to solve some pretty tough problems, but this one has me stumped. I have a column (or array) of invoice dates, that are ...
- 858
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
0
votes
0
answers
508
views
Excel Lambda function produces a VALUE! error
I am using Excel version 2205 (Build 15225.20356)
I expect that each of these functions produces the identical result $A$1
// no lambda or let
=ADDRESS(ROW(A1),COLUMN(A1))
// lambda alone
=LAMBDA(...
- 7,903
3
votes
2
answers
1k
views
How to write a LAMBDA function in Excel for this recursive calculation
I'm trying to come up with a LAMBDA formula that captures the following recursive calculation:
Column A has 40 rows with integers between 1 and 40. Column B divides each integer in column A by 6 and ...
- 31
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
1
answer
689
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
0
votes
0
answers
326
views
Why is this returning a #VALUE! error in Excel when I am using a simple LAMBDA function?
I have attached a pic of what I am trying to do. The goal is to get LAMBDA to subtract the previous cell and then average that. (AVERAGE RATE OF CHANGE)
1
vote
1
answer
107
views
Conditional branches of a function raise false circle reference errors
I have a big function with lots of branches of IFS; each branch uses different references of the worksheet:
MYFUN = LAMBDA(i,
IFS(
i = 1, // a formula uses Row 1 for instance,
i = ...
- 5,760
2
votes
1
answer
292
views
INDIRECT and OFFSET in the function body make a name special
I would like to write a user-defined function CHOOSERANGE that returns a range from 4 coordinates. The first version is as follows:
CHOOSERANGE = LAMBDA(row_min, col_min, row_max, col_max,
...
- 5,760
4
votes
4
answers
3k
views
Define a lambda function with infinite number of arguments
Some Excel native functions like VSTACK permit of infinite number of arguments, and they have an intellisense as follows:
I would like to know how to define such a function by LAMBDA.
I tried try = ...
- 5,760