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, s...
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!
- And thanks for the hack.
JoeUser2004Hi Joe. Thank you. My specific application is understanding some personal medical data - does the difference in my typical behavior on a (for example) Tuesday cause the trend to change at a different rate from my typical behavior on a (for example) Saturday. The trendlines would be compared as a starting point for cause analysis and I could use the analysis to try to improve my behavior. This is in addition to the overall trend.
You are correct that I did not create a separate chart "series" for each subset. My hope was that I could select subsets of the data and create a trendline for each.
In an industrial application, the trendlines could be used to see whether training is adequate - downward defect trend over time by operator or whether an increase in defects is caused by operator, time since maintenance, raw material supplier, tool provider, or a host of other applications.
I suspect this is not an Excel feature, so I am hoping this thread might be seen by a product owner.
Alan_Berow wrote: ``My hope was that I could select subsets of the data and create a trendline for each.``
I addressed that in my first response, when I wrote: ``the only way to have trendlines for "partial data" is to create a separate chart "series" for each subset``.
-----
Alan_Berow wrote: ``I suspect this is not an Excel feature, so I am hoping this thread might be seen by a product owner.``
Not likely. Use the Feedback feature to communicate the need for a feature or feature change. I've read that for best results, describe the feature change as a "defect"; include a detailed example (Excel file); and provide your email address. Even so, I would not have high hopes for a response or product change.
(That said, I wonder if you would have better luck by becoming an "insider" and participating in the "insider" forum. I don't know anything about that, so it might be a misdirection.)
FWIW, I agree that it would be a useful feature. I've had a similar need occassionally.