arraysexcelexcel-formulafilterunique

Excel dynamic linest


I have a table (Table1) with 5 columns. Columns A, B, and C can be considered a series (three variables used to create combinations of tests); column D is the x value (pressure); column E is the y value (length). I would like to create a dynamic array calculating LINEST for each unique series.

K1 creates an array of the results I need (m2, m, b, R2) but is not dynamic; it needs to be copied down for every row in G1#. I need to replace G1, H1, I1 with G1#. This is where I'm stuck. Perhaps a BYROW or LAMBA is needed, but these are fairly new to me and I struggle to wrap my head around the logic. How do I create a dynamic array in K1?

G1=UNIQUE(Table1[A]:Table1[C])

K1 =LET(a,Table1[A],b,Table1[B],c,Table1[C],d,Table1[D], 
e,Table1[E],x,FILTER(d,(a=G1)*(b=H1)*(c=I1)),
y,FILTER(e,(a=G1)*(b=H1)*(c=I1)),
l,LINEST(y,x^{1,2},,TRUE),
HSTACK(INDEX(l,1,1),INDEX(l,1,2),INDEX(l,1,3),INDEX(l,3,1)))
A B C D E
a1 b1 c1 1500 0.04
a1 b1 c1 2400 0.032
a1 b1 c1 3750 0.024
a1 b1 c1 5000 0.016
a1 b1 c2 1500 0.032
a1 b1 c2 2400 0.024
a1 b1 c2 3750 0.02
a1 b1 c2 5000 0.012
a1 b1 c2 8000 0.004
a1 b2 c1 2400 0.04
a1 b2 c1 3750 0.032
a1 b2 c1 6000 0.024
a1 b2 c1 7500 0.016
a1 b2 c2 2400 0.032
a1 b2 c2 3750 0.024
a1 b2 c2 6000 0.016
a1 b2 c2 7500 0.008
a2 c3 1500 0.03
a2 c3 2400 0.025
a2 c3 3750 0.02
a2 c3 5000 0.01
a2 c4 1500 0.025
a2 c4 2400 0.02
a2 c4 3750 0.015
a2 c4 5000 0.005
a3 c5 0.014
a3 c6 0.02
a3 c7 0.024
a3 c8 0.032

Solution

  • The below formula is completely based on what you have already posted in the OP, I just applied the REDUCE() function in order to make it spill for the entire array, so try using the following:

    =LET(
         a,Table1[A],
         b,Table1[B],
         c,Table1[C],
         d,Table1[D],
         e,Table1[E],
         f, UNIQUE(Table1[A]:Table1[C]),
         DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(m,n,VSTACK(m,
         LET(x,FILTER(d,(a=INDEX(f,n,1))*(b=INDEX(f,n,2))*(c=INDEX(f,n,3))),
             y,FILTER(e,(a=INDEX(f,n,1))*(b=INDEX(f,n,2))*(c=INDEX(f,n,3))),
             l,LINEST(y,x^{1,2},,TRUE),
         HSTACK(INDEX(l,1,1),INDEX(l,1,2),INDEX(l,1,3),INDEX(l,3,1)))))),1))
    

    enter image description here