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.

I noticed that I left out plotting the mean. Plot that the same way you plot the control limits.

I also just noticed that I improperly calculated the control limits the average is in cell B4, not cell B5.

Thanks dflak.

Made the changes and it works just fine.

Many thanks!

Q

I decided to do a bit more development on this for my own purposes. Your post got me rethinking my process. So I thought I would share it with you. This approach is a bit more organized. It also addresses the issue of “anchoring” the date row on the pivot table.

I hope I don't confuse you with the additional information, but think of this as another way to do this.

I moved all the helper cells to a separate worksheet that could be hidden. This sheet’s main purpose is to manage the average, LCLs and UCLs. It occurred to me that if I were to group the dates into weeks or months, then the scatter plot method for plotting these values wouldn’t work.

So I made a copy of the dates out to Column CZ (104 dates). You can extend it out as far as you want. I don’t really use these dates in the plot, it’s just a range I can do an offset from for the Average, LCLs and UCLs. In fact, it doesn’t need data in it at all.

I did the calculations for average and the LCL and UCL like I did in the previous post and I extended those values out to CZ as well.

This time, with the helper cells out of the way, I also made the Model List dynamic.

Plot date and plot value are the same as before, they are plotted off named dynamic ranges overlaid on the pivot table.

The average, LCLs and UCLs are plotted from the helper cells on the Chart Info page. Although the helper columns goes beyond the amount of data returned by the pivot table, the named ranges overlaid on the helper cells limits the ranges to the amount of data returned by the pivot table.

In the attached, I played with grouping the dates by week (7 day period), months, and months and year.

If you decide to group the dates by Month and Year, an extra line is added to the pivot table titles pushing the date row down. Excel formulas do not pick this move up like they do if you insert or delete rows.

To solve the problem, I added the key word “Metrics” (use whatever you want) on the same line with the date, and I use Match to find where this happens. I call this the Anchor Row. I also had to use INDIRECT to calculate the number of dates found on this shifting row.

So the date row is calculated off Cell B1 on the Chart page and offset by the number of rows to where the key word, “Metrics” appears. Likewise model list is also calculated off cell A1 and offset by the same amount.

LCL_2Sigma =OFFSET(Use_Date,2,0)
LCL_3Sigma =OFFSET(Use_Date,4,0)
Model_List =OFFSET(Chart!$A$1,'Chart Info'!$B$1,0,'Chart Info'!$B$3,1)
Plot_Average =OFFSET(Use_Date,1,0)
Plot_Date =OFFSET(Chart!$B$1,'Chart Info'!$B$1-1,0,1,'Chart Info'!$B$2)
Plot_Value =OFFSET(Plot_Date,'Chart Info'!$B$6,0)
UCL_2Sigma =OFFSET(Use_Date,3,0)
UCL_3Sigma =OFFSET(Use_Date,5,0)
Use_Date =OFFSET('Chart Info'!$B$15,0,0,1,'Chart Info'!$B$2)

P.S. I also got fancy with the chart title. You could probably hide row 22.