This doesn’t have to be difficult.
It doesn’t have to be hard.
No need to stick your head under the covers.
There is no boogie-man coming to get you.
Doing “mathy” type things in Excel can actually go a long way in helping you run your business or team.
It is common for Executives, Management, Small Business Owners, etc spend way too much reacting to data that they have no need to react to. This happens all the time. People are used to reacting, but not necessarily used to following a process of thinking through a problem.
Processes are designed to give specific outputs – within a range. You want to spend your time reacting to things that actually require your attention — not things that are just part of the normal fluctuations in your process.
These are not written in stone. But rather some high-level things I take into consideration before deciding to use standard deviation.
This is simply calculating the average of your data points. Realize that standard deviations are based on the mean, so this must be your starting point. In the example, our data points reside in cells C3:C127. So the Mean would be…
Use the formula STDEV() for this. Within the parenthesis you will enter the range for the data points that you used to calculate the MEAN in step 1. So one standard deviation would be…
Before we move any further, we should discuss the difference between 1, 2, and 3 standard deviations.
In a normal distribution a certain number of data points would typically expected to be included within 1, 2, or 3 standard deviations from the mean.
Take the one standard deviation output and multiply it by 3.
In the example, I use the terms UCL and LCL. These stand for “Upper Control Limit” and “Lower Control Limit”. If you were creating a Control Chart (like in Six Sigma) these limits would be the lines on the chart that would tell you when your process is out of control.
In the example, I set this for 3 standard deviations from the mean, so that I am only reacting 0.27% of the time. Reset this for a different number of standard deviations based on your business needs.
For the UCL – Add the Mean plus the standard deviations. In this instance I am using 3 standard deviations, so 496.9+86.6 = 583.50
For the LCL – Subtract the standard deviations from the mean For the 3 st dev example it is 496.9-86.6 = 410.3
Now bring it all together with a nested IF statement. The statement is stating that if the data is lower than the LCL or Higher than the UCL – then “ALERT”, else null “”.
Definitely could have used conditional formatting or something more exotic here, but the point is not about this formula, but rather that you are now using alerts in conjunction with Standard Deviations to more accurately pinpoint data that requires your attention.
The stats show that 40% of you are repeat visitors to the website. So you might as well just join our mailing list and get alerted when we release new content.
First timer? Then tell your colleagues about us and hit one of those social sharing thing-a-ma-jigs.
Attribution: “Standard deviation diagram” by Mwtoews. Licensed under CC BY 2.5 via Wikimedia Commons – http://commons.wikimedia.org/wiki/File:Standard_deviation_diagram.svg#/media/File:Standard_deviation_diagram.svg
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.