Fix an Incorrect Moving Average Trendline in a WPS Spreadsheet Scatter Plot
Question details
The user needs to correct a moving-average trendline that appears inaccurate in a scatter plot.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Stock-price or other time-series records are being displayed in a scatter plot, and the user wants the moving-average trendline to follow the data chronologically.
- Observed behavior
- The trendline appears incorrect because the plotted points follow the order of the source rows rather than chronological date order.
Check that the date column contains actual date values rather than text, and identify the columns used for the chart's X values and Y values. Make a note of the desired moving-average period before editing the chart.
Sort the source data chronologically and recreate the moving-average trendline
Use this solution when the trendline follows the source-row order instead of the time sequence.
A moving-average trendline uses the order of the plotted points. Sorting the source range from the oldest date to the newest date ensures that the calculated average follows the intended timeline.
In WPS Spreadsheet, select the complete data range, including the date column, value column, and headers.
Open the Data tab, choose Sort, select the date column as the sort key, and set the order to Oldest to Newest. Confirm that the date column is recognized as dates.
Click the chart, open the chart's data or series settings, and confirm that the date column is assigned to the X values and the stock-price column is assigned to the Y values.
Right-click the plotted data series, choose Add Trendline, select Moving Average, and enter the appropriate Period value.
Use a line chart for evenly spaced observations
Choose this option when the data contains evenly spaced daily or periodic observations and a category axis is more suitable than numeric X values.
Create an accurate moving-average trendline in WPS Spreadsheet
WPS Spreadsheet lets you sort time-series data, assign date values to scatter-plot X values, and add a moving-average trendline with a configurable period. Its familiar chart tools support a practical workflow for correcting trendline order and presentation.
- 1. Open the workbook: Launch WPS Spreadsheet and open the workbook containing the scatter plot and source data.
- 2. Sort the records: Select the source range, open Data > Sort, and sort the date column from Oldest to Newest.
- 3. Check the series: Select the chart and verify in the series or data settings that the date column supplies X values and the measurement column supplies Y values.
- 4. Add the trendline: Right-click the data series, choose Add Trendline, select Moving Average, and specify the period.
Frequently Asked Questions
Why does my moving-average trendline look wrong even though the data values are correct?
The trendline follows the order of plotted points, not necessarily the calendar order. Sort the source data by the date column from oldest to newest before adding or refreshing the trendline.
How can I confirm that the scatter plot is using the correct dates?
Select the chart and open its data or series settings. Confirm that the date column is assigned to X values and the measurement column is assigned to Y values. Also check that the dates are stored as real dates, not text.
What moving-average period should I use?
Set the period to the number of observations you want in each average, such as 5 for a five-observation moving average or 20 for a 20-observation average. The appropriate period depends on the time span and the amount of smoothing required.
Should I use a scatter plot or a line chart for daily stock prices?
Use a scatter plot when observations are irregularly spaced or numeric date spacing matters. Use a line chart when observations occur at evenly spaced intervals and you want the dates displayed as an ordered category axis.
What should I check if sorting does not fix the trendline?
Look for text-formatted dates, blank or duplicate dates, missing values, and chart ranges that exclude part of the source data. Recheck the series assignments and recreate the trendline after correcting the source range.
