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
A kiosk noted the temperature and its drink sales on ten days. Select
A1:B11, click Chart, choose Scatter, and click Insert.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.
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).How much should you trust the line at all? In
B15, enter=RSQ(B2:B11, A2:A11).
Remember
- A trendline is the straight line closest to the dots of a scatter chart.
- Its equation is y = slope × x + intercept:
SLOPEandINTERCEPTgive the two numbers. FORECAST(x, known y, known x)reads a value off the line.- R² (
RSQ) says how well the line fits: near 1 is a close fit.
In the function reference: FORECAST, RSQ
Previous: Scatter charts for relationships · Next: Chart the summary, not the rows