trendlines for two phases

[Elizabeth]

Hello. I am working on a line chart that shows 12 weeks of data for homework
completion. The X axis is weeks and the Y axis is rate of completion. Weeks
1-6 were baseline and data was collected for completion. Weeks 7-12 were
experimental where an intervention was used to influence homework completion.
I would like to be able to insert a trend line for weeks 1-6 and a separate
trend line for weeks 7-12 on the same graph to compare student performance.
Right now, when I right-click on a data point in my grapn to create a
trendline, I get a trendline for all 12 weeks of data. Can anyone help me
create two seperate trendlines? Thanks

Elizabeth

 

[Tushar Mehta]

Plot the data as two seperate series. Now, the trendlines would be for
weeks 1-6 and 7-12 respectively. Using two series would also make
sense since the data represent potentially different events.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Derfel]

Elizabeth:

I am trying to do the same thing. Did you get this to work? I wasn't
successful plotting two sets of data on a single x-axis. The second data set
plotted from the y-intercept rather than along the end of the horizontal axis

 

[Bernard Liengme]

Sounds like you made a Line Chart when an XY chart was needed.
Try that and let us know how it goes.
best wishes

 

[Derfel]

I made it work. I had to create two new rows in the source spreadsheet and
include the blank cells that correspond to the x values. Now if I can just
figure out how to get a trendline for each that doesn't spread across the
entire sheet!

 

[Bernard Liengme]

Starting with
x y z
1 5 #N/A
2 7 #N/A
3 9 #N/A
4 11 10
5 #N/A 13
6 #N/A 16
7 #N/A 19
8 #N/A 22

Select everything and make a chart
Now add the trendlines
best wishes

 

[Bernard Liengme]

Continued:
To get partial trendline
Make a chart with the first dataset (A1:B5)
Select A5:A9; hold CTRL key and select C5:C9; click chart; use Edit |Paste
Special specifying New Series with x-values in first column
Now add the trendlines
best wishes

 

[Bernard Liengme]

Alternatively,
Make chart from all the data (two series)
Right click chart; open Source Data dialog
Make y data series have x-values A2:A5 and y-value B2:B5
Make z data series have x-values A5:A9 and y-values C5:C9
best wishes

 

[Derfel]

Thanks for all your input. The next hurdle is the trendline; it extends for
the full length of the x-axis. In my case this is 36 entries. This will get
messy with two data series, four trendlines, and three milestone markers.
Alternatively,I wanted to create data for the trendline (using y=mx+b), but I
don't have any 'real' x values - there just labels. I tried adding a line
graphic, but this isn't well preserved after saves or chart changes.