SOLVED

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

Brass Contributor

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

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

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

Perhaps

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

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

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

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

Perhaps

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