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?

 

  • _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 )
    )

Resources