Forum Discussion

cmukesh19's avatar
cmukesh19
Copper Contributor
Sep 17, 2022

Can result of function BYROW be a range/ sequence?

 
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?

 

    • cmukesh19's avatar
      cmukesh19
      Copper Contributor
      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)?

Resources