LINEST IGNORE BLANKS - Polynomial translation

[skavorn]

Hi All,

I'm trying to use formulas to recreate the polynomial trendlines on the line graphs.

As you can see in the screenshot below (and attachment), I've created a formula which correctly outputs the polynomial output, however it doesn't work when there's blanks.

Surely it's possible because Excel clearly does itself so I'm looking for the formula to spit out the same results as per the formula on the graph labelled "AL7"

I've tried finding results for this online but not managed to get anything that works for me.

I hope you can help.

 

[Eric W]

I worked on pretty much this exact question here:

Extracting values from equations shown a chart.

I created a graph from my data, hovered over my data and right clicked the mouse. A menu appeared with among other things "add a trend line" Clicking that, another menu "Trendline Options" appears, I selected the radio button for Polynomial and selected "Order 2" At the bottom of the menu I...

www.mrexcel.com

I first came up with the matrix formulas, but settled on the same formula you used. But as you've seen, empty cells cause problems. We never came up with an answer. What version of Excel do you have? If you have Excel 365, the FILTER function might work well here. If not, it may require some VBA.

 

[Domenic]

If you have a newer version of Excel that uses dynamic arrays, enter the following formula in cell J1 . . .

=LET(Rng, FILTER(B3:J10,(LEN(B3:B10)>0)*(LEN(J3:J10)>0)),LINEST(INDEX(Rng,0,9),INDEX(Rng,0,1)^{1,2,3,4,5,6}))

Otherwise, first select cells J1:P1, then enter the following formula, and then press CONTROL+SHIFT+ENTER....

=LINEST(INDEX(J:J,N(IF(1,MODE.MULT(IF(ISNUMBER(J3:J10),ROW(J3:J10)*{1,1}))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(B3:B10),ROW(B3:B10)*{1,1})))))^{1,2,3,4,5,6})

Adjust the ranges accordingly.

Hope this helps!

 

[skavorn]

If you have a newer version of Excel that uses dynamic arrays, enter the following formula in cell J1 . . .

=LET(Rng, FILTER(B3:J10,(LEN(B3:B10)>0)*(LEN(J3:J10)>0)),LINEST(INDEX(Rng,0,9),INDEX(Rng,0,1)^{1,2,3,4,5,6}))

Otherwise, first select cells J1:P1, then enter the following formula, and then press CONTROL+SHIFT+ENTER....

=LINEST(INDEX(J:J,N(IF(1,MODE.MULT(IF(ISNUMBER(J3:J10),ROW(J3:J10)*{1,1}))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(B3:B10),ROW(B3:B10)*{1,1})))))^{1,2,3,4,5,6})

Adjust the ranges accordingly.

Hope this helps!

For anyone reading this this one worked for me. I didn't need to even turn it into an array for it to work
=LET(Rng, FILTER(B3:J10,(LEN(B3:B10)>0)*(LEN(J3:J10)>0)),LINEST(INDEX(Rng,0,9),INDEX(Rng,0,1)^{1,2,3,4,5,6}))

@ Domenic you're a star!

@ Eric W - note Dominic's solution to the problem.

Thank you!