Is there a typo dragging your average up?
Twelve team lunches around $50 and one typed as $520, which is what $52.0 looks like when the decimal does not survive data entry. Outlier Detection builds fences from your own quartiles and flags what falls outside them.
Work Starter Statistics free
After you install, this is the model to open.
Catch the Outlier Entry
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
The fences come from the data, so honest values pass and the typo does not:
- Lower fence
- $26.50
- Upper fence
- $78.50
- Flagged
- 1 row: the $520
- False alarms
- 0
Because the fences are computed from the middle of your data, the same template works on $40 lunches and $40,000 invoices without changing a setting. Flag first, then investigate: an outlier is either an error to fix or the most interesting row you own, and deleting it before looking is how both get lost.
The model
Thirteen expense lines with memos. The IQR method computes quartiles, extends them by 1.5 times the interquartile range, and flags anything outside, no thresholds guessed by a human.
| Expense lines | 13, twelve between $38 and $63 |
| The suspect | $520, memo "Team dinner (typo?)" |
| Fences (from quartiles) | calculated |
| Flagged rows | calculated |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- One group is wild, one is steady. Is the gap real?Manual vs Automated, Tested Fairly
- When the level shifts, how fast does your forecast follow?A Forecast That Trusts Recent Weeks
- What happens to the plan when reps leave?Will the Sales Team Make the Number?
- What is a pilot actually worth before you commit?Pilot First, or Go Big?
- What does each of the three budgets actually produce?Which of Three Budgets Should the Practice Plan On?
- Is the biggest engagement worth the two it blocks?Which Engagements Should We Take?
Every model like this one, and the method behind them: Statistics in Google Sheets.