Polynomial interpolation not working

[Johnny C]

Hi

I'm trying to do a 6th order polynomial giving error.

This is the formula:
=INDEX(LINEST(OFFSET($C2,0,1,1,'Liquidations actuals'!$DS$2-1),OFFSET($C$1,0,1,1,'Liquidations actuals'!$DS$2-1)^{1,2,3,4,5,6},1),1)
I tried replacing the offsets with some sample references and it didn't work. All values are +ve, and plotting the data with a 6th order trend gives no problems, the equation shows nothing amiss.

What's going wrong? Does the data need to be in columns rather than rows?

Evaluate gives
=INDEX($D$2:$AL$2,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36}^{1,2,3,4,5,6},1),1)
$D:$AL is a 35x1 array so that should be ok.
Next step in eval gives
=INDEX($D$2:$AL$2,{2,9,64.625,7776,117649,#N/A,#N/A,...#NA},1,1)
which then goes to INDEX(#VALUE!,1)
Substituting the OFFSETS with D2:AL2 and D1:AL1 yields the same result.

I have attached a file with the errors in, it's the yellow cells.

I posted on MrExcel but didn't get any meaningful response.

 

[narunfca]

Hi

I'm trying to do a 6th order polynomial giving error.

This is the formula:
=INDEX(LINEST(OFFSET($C2,0,1,1,'Liquidations actuals'!$DS$2-1),OFFSET($C$1,0,1,1,'Liquidations actuals'!$DS$2-1)^{1,2,3,4,5,6},1),1)
I tried replacing the offsets with some sample references and it didn't work. All values are +ve, and plotting the data with a 6th order trend gives no problems, the equation shows nothing amiss.

What's going wrong? Does the data need to be in columns rather than rows?

Evaluate gives
=INDEX($D$2:$AL$2,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36}^{1,2,3,4,5,6},1),1)
$D:$AL is a 35x1 array so that should be ok.
Next step in eval gives
=INDEX($D$2:$AL$2,{2,9,64.625,7776,117649,#N/A,#N/A,...#NA},1,1)
which then goes to INDEX(#VALUE!,1)
Substituting the OFFSETS with D2:AL2 and D1:AL1 yields the same result.

I have attached a file with the errors in, it's the yellow cells.

I posted on MrExcel but didn't get any meaningful response.

Though I am not expert in polynomials, there is small lacuna in the formula.
Regarding the powers, the answers are 2^1, 3^2, 4^3, 5^4, 6^5, 7^6 and after this in first matrix the value is 8 and there is no corresponding value in second matrix and hence #N/A error. Subsequent errors are of similar nature.

I am not sure of your actual requirement. If your required result is 35x6 matrix please try semicolon in second matrix i.e., {1;2;3;4;5;6}.

with best regards

Arun N

 

[Johnny C]

Thanks Arun. I had got it working with the OFFSETs wrapped in TRANSPOSEs as a CSE formula. changing the commas to ; worked as a non-CSE without the transposes which is better.

Unfortunately it's now giving incorrect results, when I plot the equation of a 6th order polynomial trendline all the coefficients are different and gives incorrect values when I try and extrapolate beyond the 35th value.

Don't worry about answering, I'm abandoning polynomials for extrapolation for this.