Forum Discussion
Solved
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 E...
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
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
- Hi 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.
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)
