Separate Trendlines Across Multiple Series

[frankie320]

Hello,

I have a graph attached as my example for the end product. I am trying to create separate trendlines (one for person A, one for person B). The bars are values per week that each person logged which are all separate series by week. Am I able to create the separate trendlines across multiple series? Also, I would like to have the week number written below each column. Is this possible in this case?

Thank you for your help.

Data Per Week.jpg

[MrShorty]

I'm optimistic that the overall analysis is possible, but it may not be easiest the way you are wanting to try doing it. Excel's chart engine does not know how to create a trendline over multiple data series, but it will do just fine if you can combine each person's data into a single data series, and have separate data series for each person.

I find that a lot of charting questions are about arranging the data in the spreadsheet. If I wanted to do this, I would expect to:

1) Arrange the data in the spreadsheet so that "person" is the leftmost column, "week number" is the 2nd column, person A's data is in column 3, person B's data is in column 4 (and so on for however many people you have).

Please Login or Register  to view this content.

2) Select this block of cells and insert a clustered column chart. Hopefully Excel will recognize that you want the left two columns to be the horizontal category x axis data and the remaining columns will be separate y axis series.
2a) If Excel guesses the data wrong, go into the Select Data dialog and fix Excel's mistakes (like deleting the "week number" series and setting the horizontal category axis data range to be the 2 leftmost columns).
3) Add trendlines for each data series.
4) Format chart elements as desired

Does that help?

[frankie320]

Hello!

Yes, this helps a lot. Thank you so much! I really appreciate your help and time. I have a couple more follow up questions.

1) I'm unable to adjust the length of my trendlines to just be within each person's data set (see first image). The forward forecast extends while the backwards forecast clears the field when I input a value. Is it possible to shorten the trendlines another way?

2) Since the data sets are now each a single color, it is slightly more difficult to view by week. Am I able to make the x axis have the person centered as well as show the week number? (See second image).


Attachment 866670
Attachment 866671

[MrShorty]

Your pictures failed to attach correctly. Be sure to follow the instructions in yellow at the top of the page when adding attachments. Adding a sample Excel file is better than a picture, too.

1) I am unaware of any other way to change the length of the built in chart trendline. I would probably move the regression/trendline calculations into the spreadsheet (use LINEST() to calculate slope and intercept for the trendline, then calculate values for each person for each week from that, then add those values as a new series to the chart, change chart type for these series to line to create a column + line combination chart). That will give you more control over the trendline. Would that be an acceptable approach?

2) I would have expected Excel to automatically show the person centered below each block of week numbers. What is Excel using for the "horizontal category X axis" range? Is it using the two columns where you have entered the person name and week numbers?

[frankie320]

Hello,

Thank you for your feedback!

The calculation of the trendline method worked and is an acceptable approach. As for the x axis range, for some sections I had selected one column and in others I had selected both. I have fixed the mistake! Thank you for your help! I was able to create the exact chart I wanted with your feedback.