Find and Label an Excel Trendline Crossing a Target Value
Question details
Find the date or x-value at which a polynomial trendline reaches a specified target value, then show and label that intersection on a chart.
- Product
- Microsoft Excel chart with a polynomial trendline
- Device & OS
- not provided
- Scenario
- The user needs to identify and visually mark where a second-order trendline crosses a target y-value.
- Observed behavior
- Excel can display a trendline equation but does not automatically calculate, plot, or label the target intersection date.
Create the chart using numeric x-values; dates must be stored as real Excel date serial numbers, not text. Add a second-order polynomial trendline and enable its equation before calculating the intersection.
Calculate the polynomial trendline intersection and add a labeled helper point
Use this method when the chart has a second-order polynomial trendline and you need the valid date where it reaches a target value.
For a displayed trendline equation of y = ax² + bx + c and a target value T, solve ax² + bx + (c - T) = 0. The possible x-values are (-b ± SQRT(b² - 4a(c - T))) / (2a). Use only a real root that falls within the chart's x-axis date range.
Select the chart series, choose Chart Elements > Trendline > More Options, select Polynomial, set Order to 2, and select Display Equation on chart.
In worksheet cells, enter the target value T and copy the coefficients a, b, and c from the displayed equation y = ax² + bx + c. Keep the signs exactly as shown in the equation.
In two cells, calculate the roots with =(-b+SQRT(b^2-4*a*(c-T)))/(2*a) and =(-b-SQRT(b^2-4*a*(c-T)))/(2*a). If the value inside SQRT is negative, the trendline does not cross the target.
Compare the two roots with the minimum and maximum x-values in your source data. Keep the root that is within the chart range, then format that cell as a date if the x-axis uses dates.
Create a two-cell helper range containing the valid x-value/date and the target T. Right-click the chart, select Select Data > Add, set the helper range as the series X and Y values, and change that series to an XY Scatter marker if needed.
Select the helper marker, choose Add Data Labels, and edit the label to show the calculated date and target value. To show a vertical guide, add a second XY helper series with the same x-value and two y-values spanning the chart, then format it as a line.
Use a manual annotation when only one chart needs the result
Use this faster option when you do not need the target label to update automatically after source data changes.
Mark a Polynomial Trendline Target Crossing in WPS Spreadsheets
WPS Spreadsheets lets you calculate the valid quadratic root in cells, add the result as an XY helper series, and label the target crossing directly on your chart. This provides a clear, maintainable way to show the date and value where a polynomial trendline reaches a target.
- 1. Create the trendline: In WPS Spreadsheets, select the chart data series and use Chart Tools to add a Polynomial trendline with order 2 and show its equation.
- 2. Calculate the crossing: Enter the target and trendline coefficients in worksheet cells, then use the quadratic formula to calculate the possible x-values.
- 3. Validate the date: Select the root that falls between the first and last x-values in the chart, and format it as a date when applicable.
- 4. Add a helper marker: Use Chart Tools > Select Data to add a helper series containing the crossing date as X and the target as Y.
- 5. Show the result on the chart: Format the helper series as a visible marker, add a data label, and add a second helper line series if a vertical guide is needed.
Frequently Asked Questions
How do I find where a quadratic trendline reaches a specific target?
If the trendline is y = ax² + bx + c, replace y with the target T and solve ax² + bx + (c - T) = 0. Use =(-b±SQRT(b^2-4*a*(c-T)))/(2*a), then retain the root inside the chart's x-range.
Why does my trendline crossing formula return a #NUM! error?
The expression inside SQRT is negative, which means the polynomial trendline does not reach that target value as a real intersection. Check the target, coefficients, and signs copied from the displayed equation.
Which root should I use when the formula returns two dates?
Use the root that falls within the dates displayed on the chart and matches the crossing you want to analyze. A second-order trendline can cross the same target twice.
How can I show the calculated trendline crossing on an Excel chart?
Put the selected x-value/date and target y-value in a helper range. On the chart, use Select Data > Add to add the helper series, format it as an XY Scatter marker, and choose Add Data Labels for the point.
Can I draw a vertical line at the trendline target date?
Yes. Add a second helper XY series with the calculated crossing x-value repeated twice and y-values equal to the chart's lower and upper bounds. Format that helper series as a line with no marker.
