Sep 19 2023 11:48 PM - edited Sep 20 2023 12:12 AM
HI ALL:
I have a Lambda that works in Excel -- but if I try to edit it in Excel Labs Lambda Editor it generates an Error.
The result is that I cannot name the LAMBDA for general use (
The Editor gives the following error : "Save failed: The argument is invalid or missing or has an incorrect format."
Thanks in advance
Below is the code
BTW -- it works, but its not 100% tested .
=LAMBDA(ARRAY, target_buckets, match_mode,
LET(
XMATCH3, LAMBDA(vector, target, match_mode,
LET(
_PURPOSE, " XMATCH with different match_mode
match_mode,
=-1 =>index of # lesthan target,
= 0 =>closest to target,
= > 1 # greater than target ",
index_LT, XMATCH(target, vector, 1),
index_closest, XMATCH(MIN(vector - target), vector - target, 0),
index_GT, XMATCH(target, vector, -1),
INDEX(HSTACK(index_LT, index_closest, index_GT), match_mode + 2)
)
),
_c1, " ",
_PURPOSE, "
Given a random array of numbers,
returns 4 valued vector
with HUMAN READABLE AXES
MIN_VALUE, MAX_VALUE, DELTA, #_BUCKETS
-- great for HISTOGRAMS & GRAPHS
",
_INPUTS, "ARRAY : an array of values
target_buckts : minimum # of buckets",
_OUTPUTS, "HUMAN READABLE
MIN_VALUE, MAX_VALUE, DELTA",
_COMPATABILITY, "WORKS IN EXCEL AND GOOGLE SHEETS",
_c2, " ",
imin, MIN(ARRAY),
imax, MAX(ARRAY),
idiff, imax - imin,
idelta, idiff / target_buckets,
scale, 10 ^ (FLOOR(LOG10(idelta), 1)),
scaled_delta, idelta / scale,
standard_deltas, {1, 2, 2.5, 5, 10},
scaled_delta_index, XMATCH3(standard_deltas, scaled_delta, match_mode),
optimal_delta, INDEX(standard_deltas, scaled_delta_index) * scale,
optimal_diff, CEILING(idiff / optimal_delta, 1) * optimal_delta,
optimal_start, IF(
imin > 0,
CEILING(imin / optimal_delta, 1) * optimal_delta,
FLOOR(imin / optimal_delta, 1) * optimal_delta
),
temp_end, optimal_start + optimal_diff,
optimal_end, IF(temp_end <= imax, temp_end, temp_end + optimal_delta),
optimal_buckets, (optimal_end - optimal_start) / optimal_delta,
result, HSTACK(optimal_start, optimal_end, optimal_delta, optimal_buckets),
result
)
)({1,2,3}, 10, 1)
Sep 20 2023 01:19 AM
Sep 20 2023 01:42 AM
Sep 20 2023 02:03 AM
I think the problem is that some of your comments exceed the maximum character count for a string in a formula. When using the AFE you could move the comments to the AFE but that might not meet your need for platform independence.
Sep 20 2023 04:32 AM
SolutionIn my case it doesn't work in both cases since _PURPOSE is more than 256 characters. If use concatenation as
abc=LAMBDA(ARRAY, target_buckets, match_mode,
LET(
XMATCH3, LAMBDA(vector, target, match_mode,
LET(
_PURPOSE, " XMATCH with different match_mode
match_mode,
=-1 =>index of # lesthan target,
= 0 =>closest to target,
= > 1 # greater than target ",
index_LT, XMATCH(target, vector, 1),
index_closest, XMATCH(MIN(vector - target), vector - target, 0),
index_GT, XMATCH(target, vector, -1),
INDEX(HSTACK(index_LT, index_closest, index_GT), match_mode + 2)
)
),
_c1, " ",
_PURPOSE, "
Given a random array of numbers,
returns 4 valued vector" & char(10) &
"with HUMAN READABLE AXES
MIN_VALUE, MAX_VALUE, DELTA, #_BUCKETS" & char(10) &
"-- great for HISTOGRAMS & GRAPHS
"
,
_INPUTS, "ARRAY : an array of values
target_buckts : minimum # of buckets",
_OUTPUTS, "HUMAN READABLE
MIN_VALUE, MAX_VALUE, DELTA",
_COMPATABILITY, "WORKS IN EXCEL AND GOOGLE SHEETS",
_c2, " ",
imin, MIN(ARRAY),
imax, MAX(ARRAY),
idiff, imax - imin,
idelta, idiff / target_buckets,
scale, 10 ^ (FLOOR(LOG10(idelta), 1)),
scaled_delta, idelta / scale,
standard_deltas, {1, 2, 2.5, 5, 10},
scaled_delta_index, XMATCH3(standard_deltas, scaled_delta, match_mode),
optimal_delta, INDEX(standard_deltas, scaled_delta_index) * scale,
optimal_diff, CEILING(idiff / optimal_delta, 1) * optimal_delta,
optimal_start, IF(
imin > 0,
CEILING(imin / optimal_delta, 1) * optimal_delta,
FLOOR(imin / optimal_delta, 1) * optimal_delta
),
temp_end, optimal_start + optimal_diff,
optimal_end, IF(temp_end <= imax, temp_end, temp_end + optimal_delta),
optimal_buckets, (optimal_end - optimal_start) / optimal_delta,
result, HSTACK(optimal_start, optimal_end, optimal_delta, optimal_buckets),
result
)
);
it works
Sep 20 2023 11:57 AM
Sep 21 2023 06:07 AM
@LBROWN7 , you are welcome.
IMHO, that's not AFE. AFE is just editor which forwards your formula into Excel as it is. Excels on different platforms could have different limits, calc engine could handle calculations slightly different, etc. Same for OfficeScript - it could work on desktop but not on web, or visa versa. But that happens quite seldom and in majority cases is considered as bug.
Sep 21 2023 10:24 PM
@SergeiBaklan thanks. I considered it a bug, and one motivation for raising it here was to give it some visibility so possibly it could be addressed.
Also, the code I posted above was not fully debugged -- so I thought I would post the debugged ( and tested) version below.
=LAMBDA(ARRAY, target_buckets, mode,
LET(
_c1, " ============================================== ",
_PURPOSE, "PURPOSE",
_C1_1, " Generate a nice, Humand Readable ",
_C1_2, " graph axes given a (1) an array of data and ",
_C1_A, " (2) a target number of buckets",
_C1_3, " ",
_INPUTS, "INPUTS",
_C2_1, " [1] ARRAY --, an array of values, can be {min,max} ",
_C2_2, " [2] target_buckets ",
_C2_3, " [3] match_mode ",
_C2_4, " ",
_C2_5, " mode =-1, returns # BUCKETS <= target_buckets+1 ",
_C2_6, " mode = 0, returns # BUCKETS closest to target_buckets ",
_C2_7, " mode = 1, returns # BUCKETS >= target_buckets ",
_C2_8, " ",
_OUTPUTS, "OUTPUTS",
_C3_1, " HR ==> HUMAN READABLE ",
_C3_2, " [1] HR_MIN_VALUE ",
_C3_3, " [2] HR_MAX_VALUE ",
_C3_4, " [3] HR_DELTA ",
_C3_5, " [4] BUCKETS ",
_c3_a, " ",
_COMPATABILITY, "WORKS IN EXCEL AND GOOGLE SHEETS",
_c2, " ================================================ ",
_c3, " ",
XMATCH_c, LAMBDA(vector, target, match_mode,
LET(
_PURPOSE, " XMATCH, except match_mode =0 means closest. ",
_c1, " ",
index_LT, XMATCH(target, vector, -1),
index_GT, XMATCH(target, vector, 1),
indx2target, LAMBDA(index, (ABS(INDEX(vector, index) - target))),
index_closest, IF(indx2target(index_LT) < indx2target(index_GT), index_LT, index_GT),
result, INDEX(HSTACK(index_LT, index_closest, index_GT), SIGN(match_mode) + 2),
result
)
),
imin, MIN(ARRAY),
imax, MAX(ARRAY),
_10, " constrain mode to {-1,0,1}, and change sign",
match_mode, -1 * SIGN(mode),
sanitized_target_buckets, IF(ABS(target_buckets) < 1, 10, ABS(target_buckets)),
idiff, imax - imin,
idelta, idiff / sanitized_target_buckets,
scale, 10 ^ (FLOOR(LOG10(idelta), 1)),
normalized_delta, idelta / scale,
_100, "normalized delta will be in range 1 .. 10 ",
_101, " normalized delta * scale = idelta ",
standard_deltas, {1, 2, 2.5, 5, 10},
standardized_delta_index, XMATCH_c(standard_deltas, normalized_delta, match_mode),
HR_delta, INDEX(standard_deltas, standardized_delta_index) * scale,
HR_diff, CEILING(idiff / HR_delta, 1) * HR_delta,
HR_start, IF(
imin > 0,
CEILING(imin / HR_delta, 1) * HR_delta,
FLOOR(imin / HR_delta, 1) * HR_delta
),
temp_end, HR_start + HR_diff,
HR_end, IF(temp_end > imax, temp_end, temp_end + HR_delta),
HR_buckets, (HR_end - HR_start) / HR_delta,
result, HSTACK(HR_start, HR_end, HR_delta, HR_buckets),
result
)
)({-10, 9}, 9, -1)
Sep 22 2023 02:10 AM
I confess, I am not as meticulous as you when it comes to commenting code within a function.
However, when I used local names to hold comments, a character I found useful to prevent C1 being interpreted as a cell or column reference was the backslash "\". It has the advantage of being available from a normal English language keyboard and is readily interpreted as if it were an escape character.
That said, I am not in a position to know whether it would cause compatibility issues with non-English locales in Excel or with Sheets.
I also use non-ASCII characters within Excel names and have autocorrect settings to generate them
λ \lambda
ϑ \theta
₁ _1
₂ _2
etc.
That is most likely a step too far for most, but it does allow me to use c₁ c₂ etc without having the names confused with the A1 or R1C1 notations for cell or column references (notations that I have avoided for the past 8 years with the exception of the refers to box of a defined name where it can't be avoided).
Footnote: Mike Girvin found my workbooks highly amusing. The use defined names is unusual (though known practice) but to take it to the point where the grid could be hidden along with sheet headers because nothing is ever directly referenced by its sheet location he viewed as 'hard-core'.
Sep 22 2023 02:35 AM
If you consider that as AFE bug that's much better to open an issue directly on GitHub Issues · microsoft/advanced-formula-environment (github.com) for the team which develops it.
Sep 22 2023 11:03 AM
Thanks for the info, I now know how you embed Greek letters in your LAMBDAs.
.
For me, it would create compatibility issues.
Quite cool approach!!
Sep 20 2023 04:32 AM
SolutionIn my case it doesn't work in both cases since _PURPOSE is more than 256 characters. If use concatenation as
abc=LAMBDA(ARRAY, target_buckets, match_mode,
LET(
XMATCH3, LAMBDA(vector, target, match_mode,
LET(
_PURPOSE, " XMATCH with different match_mode
match_mode,
=-1 =>index of # lesthan target,
= 0 =>closest to target,
= > 1 # greater than target ",
index_LT, XMATCH(target, vector, 1),
index_closest, XMATCH(MIN(vector - target), vector - target, 0),
index_GT, XMATCH(target, vector, -1),
INDEX(HSTACK(index_LT, index_closest, index_GT), match_mode + 2)
)
),
_c1, " ",
_PURPOSE, "
Given a random array of numbers,
returns 4 valued vector" & char(10) &
"with HUMAN READABLE AXES
MIN_VALUE, MAX_VALUE, DELTA, #_BUCKETS" & char(10) &
"-- great for HISTOGRAMS & GRAPHS
"
,
_INPUTS, "ARRAY : an array of values
target_buckts : minimum # of buckets",
_OUTPUTS, "HUMAN READABLE
MIN_VALUE, MAX_VALUE, DELTA",
_COMPATABILITY, "WORKS IN EXCEL AND GOOGLE SHEETS",
_c2, " ",
imin, MIN(ARRAY),
imax, MAX(ARRAY),
idiff, imax - imin,
idelta, idiff / target_buckets,
scale, 10 ^ (FLOOR(LOG10(idelta), 1)),
scaled_delta, idelta / scale,
standard_deltas, {1, 2, 2.5, 5, 10},
scaled_delta_index, XMATCH3(standard_deltas, scaled_delta, match_mode),
optimal_delta, INDEX(standard_deltas, scaled_delta_index) * scale,
optimal_diff, CEILING(idiff / optimal_delta, 1) * optimal_delta,
optimal_start, IF(
imin > 0,
CEILING(imin / optimal_delta, 1) * optimal_delta,
FLOOR(imin / optimal_delta, 1) * optimal_delta
),
temp_end, optimal_start + optimal_diff,
optimal_end, IF(temp_end <= imax, temp_end, temp_end + optimal_delta),
optimal_buckets, (optimal_end - optimal_start) / optimal_delta,
result, HSTACK(optimal_start, optimal_end, optimal_delta, optimal_buckets),
result
)
);
it works