WPS Spreadsheet: Find X Values for a Given Y on a Moving-Average Trendline
Question details
The user needs to find the two X values where a moving-average trendline reaches a specified Y value, such as 5.5.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A chart contains known X and Y data with a moving-average trendline, and the user wants to perform an inverse lookup for a target Y value.
- Observed behavior
- WPS Spreadsheet does not provide a direct inverse lookup for a moving-average trendline because the trendline is calculated from neighboring data points rather than a single displayed equation.
Identify the moving-average period used by the trendline and confirm which source X values correspond to each Y value. Record the target Y value, such as 5.5, and make sure the source data is arranged in ascending X order.
Calculate moving-average values and interpolate the X crossings
Use this method when the moving-average trendline does not provide an equation and you need accurate X estimates for one or more target Y values.
A moving average is generated from a group of neighboring Y values, so it cannot normally be inverted with a single trendline formula. Recreate the displayed moving-average values in helper cells, locate the points surrounding the target Y, and interpolate between their X values.
For two crossings, repeat the interpolation for each pair of neighboring moving-average values that brackets the target Y.
In WPS Spreadsheet, add a column beside the source data and enter an average formula matching the chart period. For a three-point moving average with Y values in column B, enter a formula such as =AVERAGE(B2:B4), then fill it down. Align each result with the center X value used by the chart.
Use Find or scan the helper column to locate two neighboring moving-average results where one is below and the other is above the target Y, such as 5.5. Record their corresponding X values and moving-average values.
In an empty cell, calculate the crossing with the linear interpolation formula =(X1+(TargetY-Y1)*(X2-X1)/(Y2-Y1)). Replace X1 and X2 with the bracketing X values and Y1 and Y2 with their moving-average values.
If the moving-average curve reaches 5.5 twice, identify the second pair of points that brackets 5.5 and apply the same interpolation formula to obtain the other X value.
Use Goal Seek with an interpolated trendline formula
Use Goal Seek when you want WPS Spreadsheet to calculate an X value automatically after defining a helper formula for the moving-average segment.
Solve a displayed trendline equation when the trendline type supports one
Use this option only when the chart trendline has a usable equation, such as a polynomial trendline, rather than a moving-average trendline.
Use WPS Spreadsheet to calculate trendline crossings and target X values
WPS Spreadsheet lets you recreate moving-average values in worksheet cells, identify the segments that cross a target Y, interpolate the corresponding X values, and use Goal Seek for automated calculations. Its familiar spreadsheet tools also support charts and compatible Excel workbooks.
- 1. Enter or open the data: Open WPS Spreadsheet and enter the source X and Y values, or use File > Open to load an existing Excel workbook.
- 2. Build the moving-average helper column: Add a worksheet column and use AVERAGE formulas that match the chart's moving-average period.
- 3. Calculate each crossing: Use an interpolation formula or open Data > What-If Analysis > Goal Seek to solve for the X value at the desired Y.
- 4. Review the chart and results: Select the chart to verify the trendline and compare the calculated X values with the plotted crossings.
Frequently Asked Questions
Can I directly reverse a moving-average trendline to find X for a given Y?
Usually not. A moving-average trendline is calculated from neighboring data points and generally does not expose one equation that can be solved directly. Recreate the moving-average values in worksheet cells, then use interpolation or Goal Seek.
Why might a moving-average trendline have no values at the beginning or end of the chart?
A centered moving average needs data points on both sides of each calculated point. The first and last positions may therefore be blank or unplotted. Match the helper-column alignment and chart settings to avoid shifted results.
How do I find both X values when the trendline reaches Y = 5.5 twice?
Find both pairs of neighboring moving-average values that bracket 5.5. Apply the interpolation formula separately to each pair, or run Goal Seek separately after defining an interpolation formula for each segment.
When should I use a trendline equation instead of interpolation?
Use an equation when the selected trendline type provides a meaningful displayed formula, such as linear or polynomial. For a moving-average trendline, interpolation based on the calculated values is generally the more appropriate method.
Does Goal Seek calculate the X value from the chart itself?
No. Goal Seek changes an input cell until a worksheet formula reaches the specified target. Create a formula that represents the relevant moving-average segment, select Data > What-If Analysis > Goal Seek, and set the target Y value.
