Hi All,
Ever since the new Dynamic Arrays were released, I've been trying to make my calculations use the spill functionality as much as I can. I've been stuck on this one for a while now and thought I'd reach out to see if anyone has a solution.
I'm trying to fit a trend line using the SLOPE and INTERCEPT functions. They both accept two arguments like this : SLOPE(C2:C52,B2:B52) / INTERCEPT(C2:C52,B2:B52) where column C contains the Y values and column D contains the X values. Works great.
I have multiple X value ranges (in columns D, E, F, ...). The number of columns can vary so I want to make it dynamic. SLOPE(C2:F52,B2:B52) will fail since SLOPE is expecting a one dimensional range. I got around this by using the CHOOSE function like this:
CHOOSE({1,2,3},SLOPE(C2:C52,B2:B52),SLOPE(D2:D52,B2:B52),SLOPE(F2:F52,B2:B52))
This does 3 columns perfectly. How do I make it so that I can send a variable number of items (could be a lot of columns, so typing them in is not a great option) into the CHOOSE function. The first part is easy with the SEQUENCE function. I'm stuck on the second part. Any help would be appreciated.
Thanks,
George.
can you upload an example worksheet so we dont have to try and recreate the data
Here's a sample workbook. Thanks for helping.
Probably not what you are after, but.....
I used the offset function to get the next column, but it does need a number to offset by. I couldnt get th sequence function to work within the offset (guessing it doesnt support arrays, but not had a chance to check). So i took the number from the header row
You still have to drag the formula over but it seems to work
Once again probably not quite what you want, but two options.
1) if you have the LET function
=LET(Rng,B2:G47,CHOOSE(SEQUENCE(,COUNT(B2:G2)),SLOPE(INDEX(Rng,,1),A2:A47),SLOPE(INDEX(Rng,,2),A2:A47),SLOPE(INDEX(Rng,,3),A2:A47),SLOPE(INDEX(Rng,,4),A2:A47),SLOPE(INDEX(Rng,,5),A2:A47),SLOPE(INDEX(Rng,,6),A2:A47)))
2) A formula that doesn't spill
=SLOPE(FILTER($B$2:$G$47,$B$1:$G$1=J6),$A$2:$A$47)
Thank you Purfleet and Fluff. I'll try your solutions.
