Charting Rolling Mean plus Standard Deviations of Attribute Data

I think that you may be looking for the following formula:

Formula:

Please Login or Register  to view this content.

Since you are using the 365 version you can probably just enter like any other formula.
If you were using an earlier version you would need to press the Ctrl, Shift and Enter keys simultaneously to activate the formula.
Note that since the values in column Q are constant for each defect, the standard deviations are all zero. As in the value for all cells in column Q corresponding to defect 12 is 35 so there is no variance.
If you are not looking to get all zeros, then perhaps if we could see a couple of manually calculated standard deviations in column S along with explanations then we would be better able to help.
Note that I suggest using structured references, i.e. [Month] instead of whole column references, i.e. F:F, as whole column references makes it very hard to troubleshoot a formula.
Let us know if you have any questions.

I feel that it is easier to calculate the standard deviation from the pivot table.
If you include a timeline slicer then you can change the data displayed in the pivot table to show the 24-month period desired.
The formula to calculate standard deviation is: =STDEV.P(INDEX($B8:$V32,,MATCH(B1,$B6:$V6,0)))
To illustrate the problem of calculating the standard deviation in column V on the Data sheet note that for March 2022 the formula will include the 5's for defect 6 five times instead of one.
Let us know if you have any questions.

I really appreciate you helping me with this issue. Thank you

My end goal is to have an automated chart sliced by Defect, Year, Type that shows the count of defects (bar) a trend line, the mean plus 1 standard deviation (line), the mean plus 2 standard deviations (line), and the mean plus 3 standard deviations (line). The deviations are based on the 24 month data set and to change when filtered by Defect and or Type (Not year). This is illustrated in my first post, however the calculations are not automated and do not change when filtered by Type (F/W). in the first file, I use a second manually populated table which does not separate the deviations when the chart is filtered by Type (F/W). I thought perhaps this could be accomplished with one table and update accordingly, but I do not see the solution.

I think this will work with the second table. I will work on this tomorrow and post an update.

Thank you JeteMc.

Glad that you found a solution. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.