Well, not exactly, because of an unequal number of points. When doing regression, every Y value needs an X value, so I selected the X range three times.
#EXCEL TRENDLINE DATA POINTS SERIES#
I added the data for this series using the Select Data Dialog, and selecting ranges with the Edit Series dialog open. The right chart shows the addition of a combined data set, which I named “All” so the legend would fit in one row in these small charts. All three have negative slope, and the three data sets are spread out vertically. The left chart below plots data for the three volunteers, and I’ve added a trendline for each. Note that I am using row 2 as my X values, the numbers 1-9 rather than the letters a-i. Not sorted: y = 0.0569x – 1.1165 upward slope all below the 0 y value line
Sorted: y = -0.1573x + 0.3776 downward slope intersecting the 0 y value line Since I can only post text here are the formula of the 2 lines Here are the first three rows sorted vs not leaving them randomly sorted, I get 2 different lines. If I sort the rows so that those with the most data are at the top, vs. I then added the trend line to this new line (series). I then entered the y data using the comma. For the X axis, I only entered the range for the the visits ONCE since they all the same for every volunteer. To get a common trend line, I used the select data approach. I graphed them as a series in a scatter plot with smooth lines with the x being the visits and y the scores. So the data is in a table where there are 9 columns ( one per sequential visit) and then a row for each volunteer. As expected I had volunteers drop out over time therefore some with 9 scores, some with 8, etc. Don’t forget to end the formula with a comma, the Plot Order 4, and the closing parenthesis.Ī group volunteered for an experiment where they returned every x months for a total of 9 visits. You can hold Ctrl while selecting multiple areas, but I find it easier to type a comma between range addresses. You can type the addresses of the individual names, which is inconvenient since you have to remember to include the sheet name and exclamation point it’s easier to select the ranges with the mouse. Note that you have to enter double quotes around the series name and parentheses around the multiple areas of the X and Y values. If you select the chart area (just the outermost rectangle containing the chart), you can click in the Formula bar, and enter your formula.
You don’t need to use the Select Data Source dialog to add data to a chart. We can see that it includes the X values and the Y values for the three original series. Right click on the chart and click on Select Data from the pop up menu. There are at least two ways to get this series. What we need to do is add a series to the chart that uses all of these X values and all of these Y values. The numbers at the end are the plot order of each series. I’ve color coded the formula arguments to show series names in red, series X values in purple, and series Y values in blue (the colors of the series highlights in the worksheet). But it’s no big deal to get what they wanted.įirst, let’s just check out the chart. Well, this is not what the person wanted to know. Note: In a recent version of Excel (I don’t recall if it was 2013 or 2007), trendlines changed from black to the color of the points, which was good for visibility, and they also became a dotted line, which was bad for visibility.