Including average, 2sds and 3 sd lines on pivotchart via pivottable

I fought this battle a couple of years ago and this is what I came up with.

The secret is not to plot off the pivot table, but to plot off named dynamic ranges overlaid on the pivot table.

I’ve attached a sample workbook. It shows a lot of helper columns on the same sheet with the pivot table and chart. This was done mainly so you can see everything at a glance. You can move these helper cells to a hidden sheet.

A word of caution: make sure you know where you want your pivot table to be. One of the named dynamic ranges, that I called Plot_Date depends on the row containing the dates to be on the same row. Adding and deleting rows won’t hurt the formula; Excel can keep up with that. However sometimes adding and removing filters moves the date row down or up and Excel doesn't catch that.

In this case, I’m depending on the date row being on row 20.

My first formula is in cell B1: =COUNTA(20:20)-1. This formula tells me how many dates are returned by the pivot table.

Cell B2 is used to select the model. For this example, I got lazy and hard coded the range (Model_List) to A21:A26. This should actually be a named dynamic range so if the number of models vary, the list will vary as well. This will be easier to compute without all the helper cells in the way.

Cell B3 shows the row number within the range where the model is found.

Next, I defined two named ranges:

Plot_Date =OFFSET(Sheet2!$B$20,0,0,1,Sheet2!$B$1)
Plot_Value =OFFSET(Plot_Date,Sheet2!$B$3,0)

Plot_Date is the range from the first date to the last date using cell B1 to tell me how many columns the range contains.

Plot_Value is the range of values to plot. This range is offset from Plot_Date by the number of rows shown as the value in cell B3.

Now we can compute the average in cell B4. =AVERAGE(Plot_Value) and the standard Deviation in cell B5: =STDEV(Plot_Value)

From this we can compute the upper and lower control limits for 2 and 3 standard deviations in cells B6:D7.

In anticipation of plotting these limits, I computed the Min and Max dates in cells B10:11 and B14:15 and copied down the limits next to the dates.

We are now ready to make the chart.

I picked a blank area, not in the pivot table and not in any of the data and selected a line chart.
Then I right clicked on the blank chart and selected Select Data.

I added the series Metric = Sheet2!Plot_Value

For the Horizontal Axis, I added = Sheet2!Plot_Date

Then I added a series LCL-2Sig and highlighted cells C10:11. This gave me a short bar on the chart.

I selected this short bar and changed the series type to scatter plot. This bunched everything up on the left axis. I went back and selected the series and edited it with X-Value =Sheet2!B10:B11, and the chart returned to “normal.”

From there on, I added the series for UCL-2Sig, LCL-3Sig and UCL-3Sig. These were already set up as type scatter plot.

Finally, I set the chart title to cell B2.

When you change the model, the helper cells change, Plot_Value Changes and so do the control limits.