Using Excel array formulas to check conformance of data with the Empirical Rule
Evaluate formula tool handy for visualizing array formulas
In one of the classes I teach on business analytics, I was putting together a quick review of descriptive statistics using Excel’s Data Analysis ToolPak and various statistical functions. I was doing a little bit on the Empirical Rule and wanted to show how we see if one of our data columns conformed to predictions made by the rule. The histogram looking reasonably “bellish” and I thought it would be fun to check the percentage of values that fell within plus or minus 1, 2, and 3 standard deviations from the mean. The method I used to do the counts involved the use of an array formula involving multiplication of logical clauses along with Excel’s SUM() function. After building the formula, I walk through it using Excel’s Evaluate Formula tool to show exactly how this array formula works. Check if out if you are interested.
Screencast - Using Excel array formulas to check conformance of data with the Empirical Rule