Clustered column chart with trendlines

[Shamusvw]

I have been trying to create trendlines on a clustered bar chart. Although this isn't my question in this link, it is exactly what I am trying to do : example
Unfortunately it is from over a year ago, and no solution is given.

My setup is that I have data for a few weeks of data, and I am trying to compare data on any specific day to data in the following few weeks for the same day. E.g. all Monday data is clustered together.
I created the clustered column type chart, and then switched the row/column under Chart Design menu. This then groups all my Mondays together, Tuesdays together, etc.

Now I want to trend each group of Mondays to see how the data is behaving, but when I try adding the trend line, it trends the first Monday, first Tuesday, first Wednesday... and not Monday 1, Monday 2, Monday 3,etc.

I have seen people mention to calculate your own trendline using LINEST which I can do, but I don't know how to then insert it as a line graph over the existing column chart to appear as a trendline. I have looked at using a secondary y-axis as well, but just not figuring it out.

Any assistance would be appreciated.

 

[Shamusvw]

I've seen this trendline question asked a few times with no answer, so as far as I have searched and found, this is the first solution.

 

[BSALV]

glad to hear.
In a 2021 or 365, it's probably possible without VBA, with the latest formulas and named ranges, but that's not my thing.

 

[BSALV]

when you're ready with this, you can do a little bit of finetuning by hiding both secondary axis and gridlines (not deleting).
Perhaps also change the serietype for the trendline without markers and the datalabels, ... .

 

[Shamusvw]

Will go through the solution today, and then apply it to my data.
I had thought of the secondary axis, but couldn't get it constrained to each cluster, it still went from group to group so will be interesting to see you having done it.

 

[BSALV]

i didn't try it, but i guess if you just move the graph to your excelsheet and do :
* copy the change-event in the sheetmodule of the table with your data
* perhaps change the column numbers of your layout is different
that' the easiest way ...

 

[Jon Peltier]

No need for VBA. The formulaic approach isn't too hard.

Here's the setup. At the top is the column chart data, which is also plotted in the chart. Below the chart data is the fitted data, which is calculated using a FORECAST.LINEAR formula. Below the chart, I've tabulated some details about the chart, like overlap, gap width, and the number of categories and series. These will be used to position the points of the fitted line (actually an XY Scatter series). I wrote about this in Precision Positioning of XY Data Points.

This precision point positioning data (the scatter chart X values) is below the fitted data (the scatter chart Y values). The formula is shown below the positioning data. Essentially, the formula for each point is:

category number - 0.5 + ( gap/2 + ( series number - 0.5 ) + ( series number - 1 ) * gap ) / ( series count + ( series count - 1 ) * ( -overlap ) + gap width )

It's algebra, that's all.

Finally, the Scatter Chart X and Y values are put into single columns. INDEX formulas shown below the table retrieve the data from the two blocks of calculations above. Blank rows in the data result in gaps in the scatter plot between categories.

Copy D30:E49, select the chart, and paste special, as new series, in columns. categories in first column, series name in first row. Change this series from another clustered column series to an XY Scatter series, and format it.

Here is how the fitted line looks on two other charts with different gap and overlap.

 

[BSALV]

Perhaps other experts or chart-gurus here can do it with formulas (???) but in a 2016-excel, i think there is not very much hope.

From the guru himself and for excel 2016 ! GREAT !

 

[BSALV]

My solution was with the "trial and error"-method and now, as you show the "presicion point positioning" formula, i can perhaps improve/finetune my graph.

Just 1 question, i suppose this question wasn't static.
What has to be done in your solution, when TS adds an additional week (row of data).
With 2021 or 365 and the sequence-formula, i think you can solve most of the problems, but not all of them ?
Re-organize the fitted XY chart data horizontal and with named ranges ???

Anyhow, i had a lot of fun the last half hour with your descriptions