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