In Excel or Google Sheets, z-score for the sample data is =STANDARDIZE(C18,AVERAGE(C2:C31),STDEV.S(C2:C31)). It returns 4.04.
z = (Value − Mean) ÷ Standard deviation
Step by step
- Download daily.csv below and open it in Excel (File → Open) or Google Sheets (File → Import). Columns: A day, B ad_spend, C website_orders, D app_orders, E avg_basket; data in rows 2 to 31.
- Click an empty cell and enter =STANDARDIZE(C18,AVERAGE(C2:C31),STDEV.S(C2:C31)).
- Check the result against 4.04. If it differs, check that the ranges cover rows 2 to 31 and that no cell holds text instead of a number.
The formula
=STANDARDIZE(C18,AVERAGE(C2:C31),STDEV.S(C2:C31))Expected result on the sample data: 4.04.
What the result means
Day 17 had 61 website orders. Its z-score is computed with a mean and standard deviation that include day 17 itself, which shrinks the score; leaving the day out of the baseline gives a much larger one.
Notes for Excel
- Google Sheets supports the same function names. In locales that use a comma as the decimal separator, separate arguments with semicolons.
- Excel functions skip blank cells and text but include zeros. A missing day typed as 0 changes the answer; leave it blank if it is unknown.
Where z-score misleads
- Outliers inflate the standard deviation and so hide themselves. For outlier hunting, a robust rule (IQR, or median absolute deviation) is safer.
- The "beyond 3 is rare" rule assumes roughly normal data. Skewed data produces large z-scores routinely.
- Use the same standard deviation version (sample or population) consistently; STANDARDIZE takes whichever you give it.
The plain-language explanation is on the Z-score page.
The data
| day | website_orders |
|---|---|
| 1 | 24 |
| 2 | 27 |
| 3 | 22 |
| 4 | 30 |
| 5 | 28 |
| 6 | 25 |
| 7 | 31 |
| 8 | 29 |
| 9 | 26 |
| 10 | 33 |
| 11 | 30 |
| 12 | 28 |
| 13 | 35 |
| 14 | 32 |
| 15 | 29 |
| 16 | 34 |
| 17 | 61 |
| 18 | 31 |
| 19 | 36 |
| 20 | 33 |
| 21 | 30 |
| 22 | 37 |
| 23 | 35 |
| 24 | 32 |
| 25 | 38 |
| 26 | 36 |
| 27 | 34 |
| 28 | 40 |
| 29 | 37 |
| 30 | 39 |
Same method, other tools
Related methods
Learn more about Excel
About the data on this page
The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as daily.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.
To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.
Legal entity
Devansh Yadav
Moradabad, Uttar Pradesh
Governing law: Courts at Moradabad, Uttar Pradesh
Phone: +91 8433475698
Legal: legal@analyzeit.in