This template will calculate the Z-Score statistic for three separate numeric (eg. $, % or #) variables in the last three columns of the grey shaded area of the 'Data' worksheet. It will average the Z-Score values across the three variables in each row to create a 'Scorecard' column. The Scorecard variable is used to create the 'Outlier Flag' column variable: values highlighted in yellow indicate unusually high Z-Score values across the three numeric variables being analyzed.
Click the 'Scorecard Analysis!' button after downloading the 'ScorecardDataAnalysis.xlsm' outlier and anomaly detection template. Follow the template's prompts and select your 'Scorecard' variable column of data - without the heading - and click 'Ok'. Click the 'Start Over' button to begin an analysis from scratch. Great for Data Analysis Toolpak users. You may need to do the following before using this template:
- Enable or disable macros in Microsoft 365 files
- Unblock macros from downloaded files
Notes:
- An excellent alternative to the K-Means or Hierarchical clustering algorithms commonly used for database segmentation analytics.
- The box & whisker plot algorithm is a more stable database segmentation solution that relies on the median for identifying segments; K-Means and Hierarchical clustering use the mean (average) to define segments making it sensitive to outliers and anomalies.
