arraysexcelexcel-formula

Excel array formula that references previously calculated values within the array

Another Excel question. So I know how to use functions like FILTER() and MAKEARRAY() (with LAMBDA()) to return arrays and am very comfortable using them. I was wondering if there is a way in Excel to have an array function that, for some cells in the array, references the values in other cells within the array. I think I can describe it best with a couple examples:

Suppose I want to obtain Pascal's Triangle in Excel (rotated so it starts at the top left of the sheet and proceeds down and to the right): enter image description here

How could I use an array formula to basically obtain the following: =makearray(5,5,lambda(r,c,iferror(*cellabove*+*celltoleft*,1))) I get that I could probably use a nested makearray() with index(), but I want something simpler (and easier to debug).

Another application concerns a project I've been working on for work, where Excel sheets exist for product recipes that can have anywhere from 2 to thirty-something ingredients. The sheets are structured so that there is a 40-row long hidden helper section, then a gap, then 40 rows that the user sees (the idea being that nothing will ever have more than 40 ingredients). The helper and visible sections contain slightly different formulas. Basically what I want is the following: =makearray(num_ingredients * 2 + gap_length,1,lambda(r,c,if(r < num_ingredients + 1, hidden_formula(), if(r < num_ingredients + gap_length + 1, "", visible_formula(result_of_corresponding_cell_of_hidden_formula)))))

Hopefully that makes sense. I'm thinking some combination of LET(), passing a MAKEARRAY() as a parameter, and INDEX() might be the golden solution but I'm not quite sure where to start. Maybe with iterative calculation (which I'm not familiar with at all)?

So, in short: I desire a formula that:

Thanks!

(btw, my question is similar to Excel Dynamic Array formula to create a running product of a column but more general.)

Solution

  • "I was wondering if there is a way in Excel to have an array function that, for some cells in the array, references the values in other cells within the array" - can be only possible with the REDUCE function in combination with the VSTACK or HSTACK function.

    The formula for Pascal's triangle is:

    =LET(N,5,REDUCE(SEQUENCE(,N,1,0),SEQUENCE(N-1),
    LAMBDA(y,z,VSTACK(y,SCAN(0,TAKE(y,-1),LAMBDA(a,x,a+x))))))

    The y accumulator provides you with access to elements already calculated how it's used in TAKE(y,-1).

    Another formula for Pascal's triangle which uses the recursive lambda is:

    =LET(lam,LAMBDA(i,v,fn,
    IF(i=0,v,fn(i-1,VSTACK(v,SCAN(0,TAKE(v,-1),LAMBDA(a,x,a+x))),fn))),
  lam(4,SEQUENCE(,5,1,0),lam))

    The 'lam(n,x,fn)' function builds the square of size 'n+1' using the start vector x and the fn function as itself.

    The next formula is (as far as I can understand):

    [E8]=LET(hidden_formula,A8:A17,num_ingredients,ROWS(hidden_formula),gap_length,1,
  visible_formula,hidden_formula&C8:C17,
  MAKEARRAY(num_ingredients * 2 + gap_length,1,
    LAMBDA(r,c,IF(r <= num_ingredients, INDEX(hidden_formula,r),
        IF(r <= num_ingredients + gap_length, "",
          INDEX(visible_formula,r-gap_length-num_ingredients)
        )
      )
    )
  )
)

    * visible_formula,hidden_formula&C8:C17 - just for sample.

    enter image description here