Forum Discussion
LAMBDA WORKS IN EXCEL, NOT IN EXCEL LABS EDITOR
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
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)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'.
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!!
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.
