how to use numbers to create trend line to predict future values

Hi Daniel,

The FORECAST function uses linear regression to calculate/estimate predicted values.

A linear forecast is an estimate because the trend of the real data is not linear.

Examining the real data may show a trend (in this made-up example, quadratic, i.e Y=X squared)

Apply that equation to calculate Y for future X values.

I realise that my data are much simpler than Mr Tim Cook's Apple sales.

Good luck with fitting an equation to those imaginary data 😉.

Regards,

Ian.

Hi Ian. Happy Easter. Thank you for participating.

Now, although my question has been solved in the link above, I am still keeping my mind open and trying to understand your way of doing this.

So, this is how you explained it:

1st Step: You have column A and B and C and you wanted to predict future value for A7, based on data in columns B and A. That's fair enough.

2nd Step:

Now, why did you create another 2 columns (Prediction Table)? Now you lost me: Why not stick with 3 columns (3rd being Forecast)? We don't know future values. I am having difficulties replicating your steps.

Hi Daniel,

Now, why did you create another 2 columns (Prediction Table)? Now you lost me: Why not stick with 3 columns (3rd being Forecast)?

OK, three columns. This table has one Header Row and no Header Columns.

Select Columns A and B and insert a Scatter Plot.

With the graph selected,

Format Panel > Series > Connection Line > Curved (for clarity; this has nothing to do with the FORECAST function).

Click on the Series within the graph to select it and Format Panel > Series > Trendline > Linear (this is what FORECAST will use to project, but it is not necessary; only shown for clarity).

Show Equation (if you like, for clarity).

In the table, add more rows to contain Y forecast and enter the new X values.

Formula in C14 (and Fill Down) =

FORECAST(A14,B,A)

Click on the graph to select it and Edit Data References

Drag the white circle (Fill Handle) down and right to include all three columns and all rows

The green data points are a linear projection (forecast) of the data, not a projection of the connection line.

If I were Tim Cook looking at Apple sales, I would not pay much attention to this forecast 👿.

Thank you for this opportunity to explore the FORECAST function. We learn by doing.

Regards,

Ian.

Ian, your answer does not work. For those wishing to try again and solve this question, here is the link to the similar problem I am trying to solve:

Plotting the future sales with "FORECAST" Function: Could Apple Numbers be Inferior to Microsoft Excel? Plotting the future sales with "FORECAST" Function: Could Apple Numbers be Inferior to Microsoft Excel?

HI Daniel,

Should that link not have gone to this discussion Forecast Function where you marked Wayne's answer as having solved your similar question more than an hour before posting the 'wishing to try again' comment above?

Regards,

Barry

Barry, not more than an hour before. I marked it solved on April 14th. FORECAST function started returning results, but I could not plot future sales, so I needed help with it. I posted new question on April 15th. Finally, it's been solved at this link: Re: Re: Plotting the future sales with "FORECAST" Function: Could Apple Numbers be Inferior to Microsoft Excel?

Helpful, Ian!

I reproduced your nice example and ended up with the equation displayed twice in the chart. Eventually figured out that I needed to click the Forecast data series in the chart and in the Series pane at the right uncheck the 'Show Equation' (for that data series only).

SG

Thanks, SG!

We learn a little each day 🙂.

Regards,

Ian.

Hi jettenfarm,

Plot your real data, add a trendline and show equation

Use the equation to calculate predicted Y values from future X values. Formula in B2 of the Prediction table (and Fill Down):

=(0.9618×A2)+0.1681

From y = 0.9618x + 0.1681 the trendline equation

Here I have calculated, then plotted the predictions. To get them on the same graph, click on Edit Data References and select to include them.

Regards,

Ian.

Daniel,

You are throwing a wide net without many details. Can you share why Ian's solution does not work? Posting many time just confuses the issue.

Not really a "new" question, but a rephrasing of the original. See Wayne's comment above.

Regards,

Barry

Hi Ian,

How about that! Two equations on one chart. Didn't know Numbers could do that.

SG