Hello
The equation of the polynomial trendline which is produced on a graph updates when you filter data which is what I want.
I want to extract these coeffients using the equations below (i.e. 4th order polynomial)
b4=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1)
b3=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,2)
b2=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,3)
b1=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,4)
a=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,5)
They work fine on the complete data set, but not on a filtered table where some datapoints are filtered (hidden).
I have seen some examples of using a helper column based on the subtotal command: subtotal(3,I9) al the way to subtotal(3,I18)
I then modified my equation to be:
b4=INDEX(LINEST(H9:H18,IF(I9:I18,G9:G18^{1,2,3,4})),1)
but this gives me a #VALUE! error. I have tried putting parenthesis around them but it is not working.
I have attached a small spreadsheet for clarity
Anyone can help?
Thanks
Bookmarks