0

Is it possible to chain array formulae?

i.e.)

Assume a column of values such as "Max", "Michael", "Kyle", "Michael" starting in A1

Then a formula such as =UNIQUE(A1:A4) in A2 ("Max", "Michael", "Kyle")

Is there a way to put a second formula in A3 such as =LEN(A2) that will return an array {3, 7, 4} and spill to the below rows?

I want to create effectively an automatically-updating table that expands based on the output of UNIQUE, that keeps certain intermediate results between the initial array and the final column of calculated values.

I do not want to have to use something like MAP(UNIQUE(A1:A4), LAMBDA(x, LEN(x))) as many intermediate steps use long, complex formulae and the resulting lambdas and inputs would rapidly become very cumbersome and difficult to edit.

Improve this question
1
  • 1
    Welcome to Superuser! I am assuming you mean B2 and B3 as A2 and A3 already contain values. S0, in B2, why can't you use =LEN(UNIQUE(A1:A4))?
    – Reddy Lutonadio
    Commented Apr 12 at 21:01

1 Answer 1

Reset to default
1

You can refer to the results of a dynamic formula in the worksheet using the spilled range operator # e.g.:
dynamic formulas

Improve this answer
2
  • Thank you so much! I read through the excel docs but failed to spot this one.
    – lo9ud
    Commented Apr 13 at 9:39
  • At least UNIQUE() is only available in O365/Excel...
    – Hannu
    Commented Apr 13 at 11:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .