Forum Discussion
LBROWN7
Sep 20, 2023Copper Contributor
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...
- Sep 20, 2023
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
JKPieterse
Sep 20, 2023Silver Contributor
I 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.