In my data science work, I often need to find outliers. There are many ways to do this and one might think it can be done reliably using a simple filter – e.g.,
Show me anything that falls outside this range.
This assumes (a) you know the correct range of outliers, and (b) the context for the range is never likely to change. In practice, your data will prove you wrong about these assumptions time and time-again.
In cases where transaction flow is small, a quick scan will usually suffice to see if your data passes the sniff-test. But when you have many thousands of records and dozens of fields to consider, you might want to audit your data with powerful statistical analysis.
Ideally, you need a process that can learn based on all of the data available and apply its rules consistently, even when you’re not in the room.
To that end, here’s a script block that uses the multiple interquartile methodology to find outliers. Once again, this is a pretty good use of a Script Block because doing this with formulas is likely impossible.