Calculate X and Y Values from an Excel Chart Trendline
Question details
Learn how to calculate predicted Y values and estimate X values using an Excel chart trendline.
- Product
- Microsoft Excel chart trendlines
- Device & OS
- not provided
- Scenario
- The user wants to find values that fall on a chart trendline by displaying its equation or using the original X and Y data.
- Observed behavior
- A trendline visually shows the relationship between data points, but its values must be calculated from the trendline equation or a forecasting formula.
Confirm that the chart has a trendline and that you know which trendline type is being used. FORECAST.LINEAR is intended for linear relationships and requires the original numeric X and Y ranges.
Display the linear trendline equation and calculate the value
Use this method when you need to see the trendline formula directly on the chart and calculate a Y value for a chosen X value.
For a linear trendline, the displayed formula normally has the form y = mx + b. Substitute a known X value to calculate Y. To estimate X from a known Y value, rearrange the formula as x = (y - b) / m.
The equation displayed on a chart can be rounded. Increase the displayed decimal places when possible, or use the forecasting function with the source data for a more precise predicted Y value.
Click the chart, then click the trendline line to select it. Right-click the trendline and choose Format Trendline.
In the Format Trendline pane, enable Display Equation on chart. For X and Y calculations, choose a Linear trendline type if the relationship should be linear.
Use the displayed equation y = mx + b. Replace x with your selected X value; for example, if y = 2.5x + 10 and X is 4, calculate Y as 2.5*4+10 = 20.
For a linear equation y = mx + b, use x = (y - b) / m. Enter the coefficients and known Y value in worksheet cells to calculate the result.
Use FORECAST.LINEAR to predict Y from the original data
Use this method when you need a calculated Y value for a specific X value without relying on rounded coefficients shown in the chart.
Calculate Linear Trendline Values in WPS Spreadsheets
WPS Spreadsheets lets you add a linear trendline, display its equation on a chart, and use compatible forecasting formulas to calculate predicted values from your worksheet data.
- 1. Create or select a chart: In WPS Spreadsheets, select your X and Y data, then use Insert > Chart, or click an existing chart.
- 2. Add a linear trendline: Select the data series in the chart and open the chart tools or context menu to add a Trendline. Choose Linear.
- 3. Display the equation: Open the trendline formatting options and select Display Equation on Chart to show the y = mx + b formula.
- 4. Calculate a forecast value: Click an empty worksheet cell and enter a linear forecast formula using your X and Y ranges, such as =FORECAST.LINEAR(E2,B2:B10,A2:A10).
Frequently Asked Questions
How do I show a trendline equation in Excel?
Right-click the trendline on the chart, choose Format Trendline, and select Display Equation on chart. You can also select the trendline and open its formatting pane from Chart Format tools.
How can I calculate Y from a linear trendline equation?
Use the equation shown on the chart in the form y = mx + b. Replace x with the X value you want to test, then calculate m multiplied by x plus b.
How do I calculate X when I know the Y value on a trendline?
For a linear trendline y = mx + b, rearrange the equation to x = (y - b) / m. This works only when the trendline is linear and the slope m is not zero.
Why does FORECAST.LINEAR give a slightly different result from my chart equation?
Chart equations often display rounded coefficients. FORECAST.LINEAR calculates from the complete source data, so it can return a more precise predicted Y value.
Can FORECAST.LINEAR be used with a non-linear trendline?
No. FORECAST.LINEAR fits a straight-line relationship. For exponential, logarithmic, polynomial, or other trendline types, use the appropriate regression method or calculate from a sufficiently precise displayed equation.
