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 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
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)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.
