Can result of function BYROW be a range/ sequence?

Copper Contributor
 
A
B
C
D
1
5
'=SEQUENCE(10,1,A1,1)
'=SEQUENCE(10,1,A2,1)
'=BYROW(B1:C10,LAMBDA(x,SUM(x)))
2
10
   

 

Suppose we have a range. Each range yields a range. Eventually we need to take all these ranges and do some calculations. 

 

In the example, we have a range A1:A2 with values 5 and 10. Each row of this range is going to produce a sequence of 10 elements. Eventually all these ranges will be summed to a final sequence of 10 elements. I could do that by manually generating the sequences in cells B1, and C1. What if the the range is large and we want the generate these sequences using a `BYROW`. Essentially can we combine formula in B1, C1, and D1 todether? Formula in B1 will be applied to each row in column and all the resulting ranges will be summed to produce final output.

 

If I write someting like =BYROW(A1:A2, LAMBDA(x,SEQUENCE(10, 1, x, 1))), it throws #CALC errror (nested arrays are not supported). How can BYROW produce a range for each row as a result? Are there any alternatives?

 

2 Replies

@cmukesh19 

 

=BYROW(HSTACK(B2#,C2#),LAMBDA(x,SUM(x)))

DexterG_III_0-1663394491363.png

 

Hope this helps,

Dexter

This was my approach to the problem too.

The only problem is, I need HSTACK(B2#, C2#) to be replaced by a generic formula (another BYROW maybe), which will generate sequences for each element in A2:A3. We pick one element from A2:A3, generate sequence, HSTACK it, and finally SUM them all.

In this case we have just two elements, what if we need to do this for 100 elements (A2:A101)?