SOLVED

Using Array Formula C(olumns) and R(ows) times in excel formula

Brass Contributor

pbarbosa_0-1714323345472.png

pbarbosa_1-1714323374543.png

 

 

I'm looking to expand a formula that can accommodate building scenarios over a span of 5 years (columns) and 10 cases (rows).

I can effectively handle the setup for one row and generate the outcomes across various years.

I can then drag the formula down to address the rest of the cases.

 

However, the cases might change dynamically, and I need an adaptable and general solution. Can anyone provide some guidance on this?

 

I've attached a spreadsheet where I try to explain the concept in more detail.

2 Replies
best response confirmed by pbarbosa (Brass Contributor)
Solution

@pbarbosa 

Perhaps

 

=SIN(PI()*0.25*SEQUENCE(C2,1,1,1)*(MOD(SEQUENCE(C2,C3,0),C3)+1))

@pbarbosa 

This is the same as @Hans Vogelaar 's formula except that I have allowed the row and column indices, k₁ and k₂, to broadcast to build the 2D array of products.

= LET(
    π,  PI(),
    k₁, SEQUENCE(rows, 1),
    k₂, SEQUENCE(1, cols),
    SIN(k₁*k₂ * π/4)
  )
1 best response

Accepted Solutions
best response confirmed by pbarbosa (Brass Contributor)
Solution

@pbarbosa 

Perhaps

 

=SIN(PI()*0.25*SEQUENCE(C2,1,1,1)*(MOD(SEQUENCE(C2,C3,0),C3)+1))

View solution in original post