UCL/LCL w/pivots

[Toastis]

I have graphs that include secondary axis of average, UCL and LCL. I usually use pivot tables to update the graphs. does anyone know of a better way to add the calculations to the pivot table so I can use Pivot charts that automatically update when the data is updated? I can't figure out how to make the calculated field work since the Avg, Ucl, and Lcl have to be the same number for all data pieces.

It's a pain to have to constantly update these across 20 + pivots, or worse - copy pivot information and then create these every week.

For instance:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">AM</td><td style=";">PM</td><td style=";">Average</td><td style=";">UCL</td><td style=";">LCL</td><td style=";">Std Dev</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">1/3/2011 - 1/9/2011</td><td style="font-weight: bold;text-align: right;;">1211</td><td style="font-weight: bold;text-align: right;;">1273</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;;">1/10/2011 - 1/16/2011</td><td style="font-weight: bold;text-align: right;;">1160</td><td style="font-weight: bold;text-align: right;;">1406</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">1/17/2011 - 1/23/2011</td><td style="font-weight: bold;text-align: right;;">1116</td><td style="font-weight: bold;text-align: right;;">1401</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">1/24/2011 - 1/30/2011</td><td style="font-weight: bold;text-align: right;;">1093</td><td style="font-weight: bold;text-align: right;;">1339</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;;">1/31/2011 - 2/6/2011</td><td style="font-weight: bold;text-align: right;;">1073</td><td style="font-weight: bold;text-align: right;;">1358</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;;">2/7/2011 - 2/13/2011</td><td style="font-weight: bold;text-align: right;;">1111</td><td style="font-weight: bold;text-align: right;;">1404</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;;">2/14/2011 - 2/20/2011</td><td style="font-weight: bold;text-align: right;;">1121</td><td style="font-weight: bold;text-align: right;;">1448</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;;">2/21/2011 - 2/27/2011</td><td style="font-weight: bold;text-align: right;;">1186</td><td style="font-weight: bold;text-align: right;;">1446</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;;">2/28/2011 - 3/6/2011</td><td style="font-weight: bold;text-align: right;;">1250</td><td style="font-weight: bold;text-align: right;;">1412</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;;">3/7/2011 - 3/13/2011</td><td style="font-weight: bold;text-align: right;;">1151</td><td style="font-weight: bold;text-align: right;;">1326</td><td style="text-align: right;;">1147.2</td><td style="text-align: right;;">1258.227</td><td style="text-align: right;;">1036.173</td><td style="text-align: right;;">55.51336</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[ChrisM]

The easiest rule when it comes to getting fancy with pivot tables is to put as much data as possible into your source table.

Can you add some columns in your source that contain UCL and LCL?

 

[Toastis]

Possibly, the average changes everytime new data is added - so not quite sure logistically how to add the columns and for them to be updated without additional follow up on my side to modify the average and add new cells...
does that make sense? I am starting to hate pivot tables but they are so handy.