Search code examples
excelexcel-formulaexcel-365

How do I HSTACK a variable number of columns in Excel?

I want to do some calculations on each of the columns of an array and afterwards I want to reassemble the columns into an array.

BYCOL doesn't work for this because it only allows returning a single value for each column and not a column vector.

I tried varies other approaches, like using HSTACK inside of REDUCE like in the following but none of those worked:

=LET(array,$A$6#,
REDUCE(CHOOSECOLS(array,1),SEQUENCE(1,COLUMNS(array),2),LAMBDA(a,v,HSTACK(a,CHOOSECOLS(array,v)))))

EDIT: People asked for more clarity in the question and some sample data.

Given sample data like the following: Sample data

and for example something like a CUMSUM (Cumulative SUM) function like the following: CUMSUM definition

How could I produce the following output?

Sample output

Or alternatively, say I had a BOLLINGER function defined as:

=LAMBDA(arr,
    VSTACK(
        AVERAGE(arr)-STDEV.P(arr),
        AVERAGE(arr),
        AVERAGE(arr)+STDEV.P(arr)
    )
)

producing the following output:

BOLLINGER output

Solution

  • The answer by @Can.U made me realise the mistake in my original REDUCE formula which wasn't actually that far off. The following definition of APPLYCOL works for me now: APPLYCOL definition

    With APPLYCOL I can now simply apply CUMSUM to each column as follows:

    CUMSUM Solution

    or BOLLINGER:

    BOLLINGER Solution

    APPLYCOL definition

    =LAMBDA(array, colfunc,
    REDUCE(
        colfunc(CHOOSECOLS(array,1)),
        SEQUENCE(1,COLUMNS(array) - 1,2),
        LAMBDA(accumulator,col,HSTACK(accumulator,colfunc(CHOOSECOLS(array, col))))
    )
)