68 questions
Best practices
3
votes
12
replies
185
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
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
votes
1
answer
313
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
3
votes
2
answers
443
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
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
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
1
vote
1
answer
827
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
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
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
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
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
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
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
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
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 ...
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'...
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
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
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(...
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
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
0
votes
0
answers
509
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
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
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)
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,770