Forum Discussion
Trendlines for partial data
I have data sorted by day of week (then date) and placed in a chart. I would like to be able to add a separate trendline for each day. This application could be extended to other, stratified data, such as machine turrets by day of week, operator, age of tool, time since maintenance, etc.
Alan_Berow wrote: ``separate trendline for each day``
Without a concrete example (Excel file with data and charts), I don't know what that means.
But in general, the only way to have trendlines for "partial data" is to create a separate chart "series" for each subset. With some effort, you can make all of the subset series appear to be part of one complete series, making the line and marker colors the same for all.
Then select each subset series and click to add separate trendlines, again making the line colors the same, if you wish.
If you need to "extend to other stratified data", you might consider creating a macro -- although that might be challenging, depending on the similarity (or not) of the "other data".
JoeUser2004Hi Joe. Thank you for the quick response and offer. An example is attached. Look to the Day of week tab for the chart. I anonymized the values column, so the title will look strange.
Manually aligning a set of independent charts is viable for my application, but will fall apart if there are a large number of series. I'll give it a shot to see if I can make a template or create a macro.
I doubt that I will be able to offer solutions, especially if you are looking for charting solutions.
But I might be able offer some guidance on what additional information someone else might need in order to help you.
You wrote: ``I would like to be able to add a separate trendline for each day``. To what end? For what purpose?
Do you simply want to show the trendlines on charts, for example for presentation? Or do you intend to use the trends as a basis for additional computations; for example, forecasting?
-----
If you are not interested as much in the visual presentation as you are in the trendline formulas, I would suggest that you abandon the charts and rely on appropriate Excel functions. LINEST will probably satisfy most needs.
And to that end, what type(s?) of trendline are you looking for? Only linear? Or whichever type fits the data best (linear, exponential, polynomial, etc)? The latter can be very challenging to automate.
(FYI, we can use LINEST for non-linear trendlines.)
-----
OTOH, if you are primarily interested in the visual presentation of the trendlines, note that a Line Chart might not be reliable for that purpose, even if you only want to show rising vs falling trends and steep vs flat trends without caring about the numerical details.
You did not really ``create a separate chart "series" for each subset``, which is necessary to present individual trendlines for each subset.
Instead, in your charts, the visually-separate subsets (e.g. days of the week) are physically part of one chart series. Consequently, there is only one trendline based on all of the data together.
For separate subsets, especially presented side-by-side as you do, I think you will have to resort to XY Scatter Charts.
You might need help from a skilled chartist (not me!) who can offer guidance on how to present the data visually -- once you elaborate on the purpose for the presentation.
But the following is my hack approach to demonstrate what I think you want. For details, see the chart in the "in" worksheet of the attached Excel file.
Caveat: The slopes of the trendlines are not numerically accurate because I simply assign ordinal numbers (1, 2, 3, etc) to the data, just as a Line Chart would. OTOH, it is unclear whether using the time of day is any more "numerically accurate". Again, it depends on your purpose in presenting the data (TBD).
I think that is as much (mis?)direction that I can offer. I hope someone else will step up. Good luck!
