LAMBDA WORKS IN EXCEL, NOT IN EXCEL LABS EDITOR

[LBROWN7]

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)

[#2]

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.

[#3]

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

[#4]

@LBROWN7 

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.

[#5 · Accepted solution]

@LBROWN7 

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

[#6]

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.

[#7]

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

[#8]

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

[#9]

@LBROWN7 

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

[#10]

@LBROWN7 

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.

[#11]

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!!