[SOLVED] Excel dynamic linest

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))