Forum Discussion

LBROWN7's avatar
LBROWN7
Brass Contributor
Sep 20, 2023
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 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)

 

 

  • 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

10 Replies

  • 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

    • LBROWN7's avatar
      LBROWN7
      Brass Contributor
      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.

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

  • LBROWN7's avatar
    LBROWN7
    Brass Contributor
    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).
  • JKPieterse's avatar
    JKPieterse
    Silver 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.

Resources