Forum Discussion

pbarbosa's avatar
pbarbosa
Brass Contributor
Apr 28, 2024
Solved

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.

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 28, 2024

    pbarbosa 

    This is the same as HansVogelaar '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)
  )

Resources