Polynomial regression equation in a formula

I'm in over my head and need some help.

I'm trying to create a range of projections for demographic data sets at a small level and have over 100 rows of data. I have been able to do exponential and linear projections in a formula no problem and apply it to the whole data set, but I cannot find a function in excel for a polynominal equation.

I have read extensive posts and tried to use the LINEST function but I'm not really sure what I'm doing. I have had numerous attempts at creating array formulas and establishing coefficients, but I'm literally following word by word a number of other posts and not really understanding what I'm doing. No joy so far.

I'm looking for a formula to use on the following example data (see attached):

1991 = 1,064,166
2000 = 1,231,209
2010 = 1,526,324
2020 = ??????

I have been able to establish the figure for 2020 (1,924,316) by using the equation generated from a chart polynominal trend line: y = 64036x2 - 25065x + 1E+06, which I took to mean =64036*4*4-25065*4+1000000 in the attached, which I think is correct, but I need to establish this in a formula so that I can apply it to a further 100 rows of data.

Can anyone help? A basic as possible response would help most...

Many thanks

Stuart

Shg

Thank you so so much for your reply. I'm sorry to sound like such a novice, but can you send me a spreadsheet with the worked example. I have tried to apply the details in your post, not clear on how to apply the formula in F9. I had a play with it but get a slightly different value,
and I had to remove the outside brackets in F9 to return the value 38.06. Also unclear how you derived the figure in E9. I need to be able to apply this as a formula to 100 rows of data.... see updated attachment

I would also like to generate a polynominal equation as this is how other forecasts have been generated. Can it be done in a similar way?

Many thanks

Stuart

Thank you again, but still having trouble applying the array formula to the remaining rows. Because I don't really understand the process I cant effect it, for example I don't understand where the value -275.28 has come from. I have managed to apply the formula, but confused by column H (realigned the data to apply it down) as its not generated by the formula.

See attached!

Is this the formula I would use for a polynominal regression? Where Y = B6:D6 and X = B7:D7 (tab 2 of the workbook)

=LINEST(y, x^{1, 2, 3})

Many thanks

Stuart

however I have a number of unexplained errors where #NUM! is returned even though the formula is exactly the same. The cells are all formatted the same etc.

The "smallest" (closest to 0 that is not 0) number Excel allows is about 2E-307, or approximately exp(-700). The #NUM error indicates that you've asked Excel to exceed that limit. You might look at the algebra and see if you can reorder the operation so you aren't raising e to such a large power. [exp(A)*x^B=exp(A)*exp(B*ln(x))=exp(A+B*ln(x))]

I also graphed some of the results and the forecasts seem too high in a number of places. I graphed the three point data and added a power trendline and the values given in the example are significantly lower than that generated by the array.

the Example in your spreadsheet of this behavior shows that you only plotted the y data, so Excel assumed x was {1,2,3} for the chart. In this way, the regression in the plot is different from the one in the spreadsheet. If I plot the same y data against the actual years (as in the spreadsheet regression), then the plot trendline extrapolates to approximately the same value.

Mr Shorty

Thank you, my mistake in not plotting the Y values, well spotted. I'm afraid I cant follow your suggestion in reordering the operation, as you will see from the posts above I'm already in way over my head and I'm surprised to have got this far..

I'm afraid I cant follow your suggestion in reordering the operation, as you will see from the posts above I'm already in way over my head and I'm surprised to have got this far..

I can appreciate this. I've found that, the better I understand the underlying math, the easier it is to program the problem into Excel. When I don't understand the math, then the programming is more difficult.

In your spreadsheet, you are using the formula in column d =exp(h14)*D$6^g14 (algebraically =e^A*x^B). What we want to do is algebraically find an equivalent expression that will calculate the exponents before actually raising anything to that power. The third expression derived in my post #8 is equivalent (you should review your algebra of exponents to show this is true). This formula in column d looks like =exp(h14+g14*LN(d$6)). That should get rid of at least some of the number errors.

Many thanks, I managed to apply the suggested formula and it removed the errors! Your right, you really need to understand the process if you are going to have confidence in the results. I have never come across array formulas before.

I wonder if you can help me explain the differences between the forecasts. The state level forecast is 1,828,896, however the sum of the district level forecasts is 1,846,345 and the sum of the sub district level forecasts is 2,011,118! I know small area forecasts should be taken with pinch of salt, but I kind of expected them to sum up.

Any ideas?