Search code examples
excelexcel-formulalambdaoffice365

BYCOL with SEQUENCE function gives #CALC

is there a way where in I can use BYCOL with SEQUENCE function in Excel, like in Google Sheets it is working however it is not working in Excel and returns #CALC! What has gone wrong, can someone highlight ? Or is it I am doing wrong. Should i look into other functions? Apologize if it has been asked earlier could not find question

The screen print shows in excel

enter image description here

This one in google sheets

enter image description here

This did not work in excel ⬇

=BYCOL(A1:E1,LAMBDA(w,SEQUENCE(w,,1,0)))

Worked perfectly in google sheets ⬇

=BYCOL(A1:F1,LAMBDA(w,SEQUENCE(w,1,1,0)))

Thanks

Solution

  • While BYCOL is a spill-function, Excel only lets it spill in one dimension (just like the function BYROW and the results of MAP). That it works in Google sheets is how we would've liked it to behave in Excel, but it simply does not.

    Even though we can't use BYCOL to get to this 2D spill result, we have a couple of options to accomplish the same result as intended:

    To use a regular function and have it spilled down or sideways, we can use REDUCE in combination with VSTACK/HSTACK: =DROP(REDUCE(0,A1:F1,LAMBDA(a,b,IFERROR(HSTACK(a,SEQUENCE(b,,,0)),""))),,1) or =DROP(REDUCE(0,A1:F1,LAMBDA(a,b,IFERROR(HSTACK(a,EXPAND(1,b,,1)),""))),,1)

    Another way is using MAKEARRAY where you can make use of the row and column as an index inside a formula: =LET(a,A1:E1,MAKEARRAY(MAX(a),COLUMNS(a),LAMBDA(r,c,IF(r>INDEX(a,,c),"",1))))

    We could even sometimes bypass a LAMBDA using TEXTSPLIT, where we can have a column and row delimiter make a range spill two ways:

    In this case: =DROP(TRANSPOSE(IFERROR(TEXTSPLIT(CONCAT(REPT(1&",",A1:F1)&";"),",",";"),"")),-1,-1)

    This is limited by having the range to split to be concatenated into one (virtual) cell prior to splitting and therefore this method may run into the cell character limitation.