Formula to match Polynomial trendline

Hi Guys

I need to forecast a future value.
I have only two metrics, Month/year and the cost within each month

Using the Forecast (and est variant) testing the accuracy of the formula does not meet actual values (ie if I retrospectively "test" the forecast against values now known values)

However, I notice that a Polynominal trend line is a good match

I think my requirement might NOT be what these functions were designed for (I want a line graph {for a future estimate timeline} rather than a scatter graph)

I thought I could use the R square coefficient, so have googled the problem and the maths behind this was either directly relating to scatters or well over my head.
Any ideas please

I can see what you are doing in your file but it is not clear what your question is.

You have created a forecast using a polynomial model, and it's a good R² match.

What is the remaining problem?

See the 'Timeline (2)' worksheet in the attached file.

Select B2:D2 and array-enter (press ctrl+shift+Enter instead of just Enter) the following:

=LINEST(B6:BQ6, B5:BQ5^{1;2})

Although the coefficients are slightly different, they are the same up to 11 to 12 decimal places, which we can see when we format the trendline label to display Scientific with 14 decimal places.

To calculate the data for the trendline, enter the following formula into B7 and copy in C7:CJ7:

=SERIESSUM(B5, 2, -1, $B$2:$D$2)

The two data series (B6:BQ6 and B7:CJ7) are used to create "Calculated Line Chart" on the right.

----

Sigh, I cannot resist some editorial comments.

Generally, it is better to replace the date range in the chart with either an ordinal number series (1st, 2nd, 3rd, etc month) or a series of cardinal number of days since the first date (with 1 for the first date).

Excel dates are in the range 41640 to 44256 for your dates. Although squaring them (B5:BQ5^2) is well within the limits of the internal binary representation that Excel uses, that might not be the case if you choose an order-4 polynomial or exponential trendline (for some other problem).

In fact, I'm surprised that the Line Chart seems to have used the dates for its trendline. Usually, the problem with Line Chart trendlines is that they use an ordinal number series, no matter what the x-axis values are.

For that reason, it is better to use a Scatter Chart with a Line subtype.