Course / Charts

Trendlines and forecasts

Add the line of best fit to a scatter chart, read how well it fits, and use the same line to forecast with FORECAST. Intermediate · 5 min · Trendline, FORECAST, RSQ

A trendline is the straight line that runs closest to all the dots of a scatter chart. It turns "these seem to rise together" into something you can measure and use.

The line has an equation, y = slope × x + intercept. The slope says how much the vertical value changes for each extra unit along the bottom. Put a new x into the equation and you have a forecast.

The chart draws the line; the functions give you its numbers. FORECAST reads a value off the line, SLOPE and INTERCEPT give the two parts of the equation, and RSQ says how well the line fits.

In Excel, click the chart, then the + button beside it (Chart Elements) and tick Trendline. Here, it's Trendline in the Chart panel.

Steps

  1. A kiosk noted the temperature and its drink sales on ten days. Select A1:B11, click Chart, choose Scatter, and click Insert.

  2. Now draw the line through them. Click the chart to select it: the Chart panel opens on the right (on a phone, tap Chart settings in the toolbar). Tick Trendline.

  3. Tomorrow is forecast to reach 35°C, hotter than any day in the data. In B14, read the line at that temperature: enter =FORECAST(B13, B2:B11, A2:A11).

  4. How much should you trust the line at all? In B15, enter =RSQ(B2:B11, A2:A11).

Remember

In the function reference: FORECAST, RSQ

Previous: Scatter charts for relationships · Next: Chart the summary, not the rows