STANDARDIZE function

STANDARDIZE returns the z-score of a value, measuring how many standard deviations it is from a dataset's mean.

=STANDARDIZE(x, mean, standard_dev)

Generate a STANDARDIZE formula

Describe what you need. The generator will reach for STANDARDIZE where STANDARDIZE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How STANDARDIZE reads its arguments
xrequiredmeanrequiredstandard_devrequiredSTANDARDIZE →
ArgumentRequiredDescription
xRequiredThe numeric value to standardize; can be a cell reference or direct number.
meanRequiredThe arithmetic mean (average) of the dataset; if identical to x and standard_dev, returns 0.
standard_devRequiredThe standard deviation of the dataset; must be non-zero or the function returns #DIV/0!.

Returns

A numeric value (typically between -3 and 3) representing the number of standard deviations from the mean.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find how far a customer's MRR is from average billing

CustomerPlanMRR
AlicePro99
BobBasic29
CarolEnterprise299
DavidPro99
EveBasic29
FrankPro99
GraceEnterprise299
=STANDARDIZE(C2, AVERAGE($C$2:$C$8), STDEV($C$2:$C$8))

Result: -0.32 (for Alice's MRR of 99)

Alice's $99/month is 0.32 standard deviations below the mean of ~$136. The negative sign indicates she's below center. Positive results mean above center. This tells you immediately whether a customer's billing is typical, high, or low for your base.

2. Identify enterprise customers as revenue outliers

CustomerPlanMRR
AlicePro99
BobBasic29
CarolEnterprise299
DavidPro99
EveBasic29
FrankPro99
GraceEnterprise299
=IF(ABS(STANDARDIZE(C2, AVERAGE($C$2:$C$8), STDEV($C$2:$C$8))) > 1.5, "Outlier", "Normal")

Result: Outlier (Carol's z-score is ~1.41, exceeding the 1.5 threshold)

ABS measures absolute distance from mean, so both high and low outliers are flagged. A 1.5 SD threshold catches ~93% of normal data; adjust higher (2.0) for stricter or lower (1.0) for looser detection. Enterprise plans predictably exceed this threshold.

3. Standardize signup timing to identify early vs. late adopters

CustomerPlanMRRSignup Date
AlicePro992025-01-15
BobBasic292025-02-01
CarolEnterprise2992024-12-01
DavidPro992025-03-10
EveBasic292025-01-20
FrankPro992025-04-05
GraceEnterprise2992025-02-28
=STANDARDIZE(DAYS(D2, MIN($D$2:$D$8)), AVERAGE(DAYS($D$2:$D$8, MIN($D$2:$D$8))), STDEV(DAYS($D$2:$D$8, MIN($D$2:$D$8))))

Result: -0.53 (for Alice, who signed 45 days after Carol, the earliest adopter)

By converting signup dates to days since the earliest signup, then standardizing, you can see adoption patterns. Alice's negative z-score marks her as an early adopter. Frank (126 days later) has a z-score above +1.4, marking him as a late adopter for comparison.

Common errors

Which STANDARDIZE error are you seeing?
STANDARDIZE returned an error#DIV/0!
Verify your dataset has actual variation. If all MRRs happen to be identical, use a conditional: =IF(standard_dev=0, 0, STANDARDIZE(x, mean, standard_dev)) to handle zero-variance edge cases gracefully.
#VALUE!
Ensure all three arguments resolve to numbers. Check for text-formatted numbers, blank cells, or formulas returning #N/A or other errors. Use IFERROR to trap and handle these: =IFERROR(STANDARDIZE(...), "Error").
#NUM!
Rescale your data to a reasonable magnitude. For example, if MRR is in millions with tiny standard deviations, divide all values by 1000 before standardizing to avoid floating-point precision issues.
ErrorWhy it happensHow to fix it
#DIV/0!The standard_dev argument is zero, meaning all values in the dataset have identical values (no variance).Verify your dataset has actual variation. If all MRRs happen to be identical, use a conditional: =IF(standard_dev=0, 0, STANDARDIZE(x, mean, standard_dev)) to handle zero-variance edge cases gracefully.
#VALUE!One or more arguments (x, mean, or standard_dev) contains non-numeric data, text, or a cell reference that returns an error.Ensure all three arguments resolve to numbers. Check for text-formatted numbers, blank cells, or formulas returning #N/A or other errors. Use IFERROR to trap and handle these: =IFERROR(STANDARDIZE(...), "Error").
#NUM!The calculation produces a numeric overflow or the arguments contain invalid numeric states (extremely large or small values causing precision loss).Rescale your data to a reasonable magnitude. For example, if MRR is in millions with tiny standard deviations, divide all values by 1000 before standardizing to avoid floating-point precision issues.

Tips and when to use something else

  • Z-scores follow the 68-95-99.7 rule: roughly 68% of data falls within ±1 SD, 95% within ±2 SD, 99.7% within ±3 SD. Use this to spot outliers without manually checking thresholds.
  • Standardize when comparing datasets with different scales (revenue in USD vs. customer tenure in years). Both become dimensionless z-scores, making them directly comparable.
  • If you need values between 0 and 1 instead of z-scores, use =(x-MIN)/(MAX-MIN) with MIN and MAX functions, or NORMALIZE if available—don't use STANDARDIZE for that purpose.
  • Combine STANDARDIZE with COUNTIF to audit outliers: =COUNTIF(formula_array, ">2")+COUNTIF(formula_array, "<-2") counts how many values exceed ±2 SDs.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.

Frequently asked questions

What is a z-score and why use it?
A z-score measures how many standard deviations a value is from the mean, creating a universal scale for comparison. Use it to identify statistical outliers, compare metrics on different scales, or detect anomalies. For example, a z-score of 2.5 means the value is reliably unusual—in the top 0.6% of a normal distribution.
Can STANDARDIZE return negative numbers?
Yes, and it should. Negative z-scores mean the value is below the mean; positive means above. For Alice's MRR of $99 vs. the average $136, the negative z-score (-0.32) correctly signals that her billing is below typical.
What's the difference between STANDARDIZE and MIN/MAX normalization?
STANDARDIZE creates z-scores using mean and standard deviation, typically resulting in values between -3 and 3. MIN/MAX normalization scales to 0–1 using (x-MIN)/(MAX-MIN). Use STANDARDIZE for statistical analysis and outlier detection; use MIN/MAX for UI scaling or rescaling to fixed ranges.
How do I find outliers with STANDARDIZE?
Use the ±2 rule-of-thumb: z-scores beyond ±2 are outliers (they represent ~2% of normally distributed data). Wrap STANDARDIZE in ABS and IF: =IF(ABS(STANDARDIZE(...))>2, "Outlier", "Normal"). Adjust the threshold based on sensitivity: 1.5 for looser detection, 2.5 for stricter.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17