Forum Discussion

_Elwetana's avatar
_Elwetana
Copper Contributor
Apr 12, 2024

Converting 1x1 array to number

I have following formula:

 

=let(
    src_range, A2:AA21,
    cur_row, A22:B22,
    myFunc, LAMBDA(range, "do something with range and return a number"),
    start_col, INDEX(cur_row, , 1),
    end_col, INDEX(cur_row, , 2),
    src, TAKE(DROP(src_range, , start_col), , end_col - start_col - 1),
    myFunc(src)
)

 

and the problem is that src is #VALUE! and it turns out that this is because parameters to TAKE and DROP have to be numbers, not 1x1 arrays (that are returned by INDEX function). I created a simple array to number function like this:

 

aton, LAMBDA(a, VALUE(TEXTJOIN("", TRUE, a))),

 

But it surely feels like this should be a native function. Or functions that expect numbers should do this cast automatically when supplied 1x1 array. Am I missing something?

 

Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 13, 2024

    _Elwetana 

    For the single row range INDEX returns single value, not an array. You may check with

    = TYPE( INDEX(22:22,,2) )

    which gives 1.

    However, for multiple rows, e.g.

    = TYPE( INDEX(22:23,,2) )

     it gives 64 which means an array.

    In general, with your formula perhaps you mean something like

    =LET(
    src_range, A2:AA21,
    cur_row, A22:B22,
    myFunc, LAMBDA(range,parRow,
        LET(
            start, INDEX(parRow, , 1),
            end, INDEX(parRow, , 2),
            IF( start = 0, "start column shall not be blank",
            IF( end = 0, "end column shall not be blank",
            IF( end < start, "start column shall be more than end one",
                CHOOSECOLS( src_range, SEQUENCE(, end - start + 1, start ) )
            )))
        )
    ),
    myFunc(src_range, cur_row )
)

Share

Resources