Forum Discussion
LAMBDA WORKS IN EXCEL, NOT IN EXCEL LABS EDITOR
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)
In 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
10 Replies
In 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
- LBROWN7Brass ContributorHi Peter and Sergi:
Thanks for your response, and the solution!
Obviously, Msft need to align the behaviour of Excel and the AFE (for all versions), but its early days yet.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.
- PeterBartholomew1Silver Contributor
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.
- LBROWN7Brass ContributorThanks for the answer, but i did try all that In the "GRID" part of Excel LABS you have to have the arguments -- it does not work in the "NAMES " part of Excel LABS I do not have arguments -- it will not save. The LAMBDA works fine in Excel ( and in Google Sheets).
- JKPieterseSilver ContributorI suspect you must get rid of the "({1,2,3}, 10, 1)" at the end, this is only needed when entering the lambda in a cell.