Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 93 ยท Statistics and Math

Outliers, Normalization and Linear Interpolation

Find outliers with the IQR rule, LARGE/SMALL and z-scores, scale data with min-max and STANDARDIZE, and interpolate missing values with FORECAST.LINEAR, TREND, XLOOKUP and LINEST.

Real data has odd values and missing values. Three small techniques cover most of what analysts need before the real analysis: spotting outliers (values far from the rest), rescaling columns so different units can be compared (normalization), and interpolating a value that falls between two known points (a price at 45 units when you only know 40 and 50). All of them are a few worksheet formulas.

Excel cannot run on this site, so formulas are real Excel but read-only. The TryIts are JavaScript stand-ins that apply the same formulas so you can check the numbers; they are not Excel's code. The functions used here (QUARTILE.INC, STANDARDIZE, FORECAST.LINEAR) are explained further in the statistics lessons.

Finding outliers

An outlier is a value unusually far from the others. It might be a typing error, or a genuine extreme that matters. Decide which before you remove anything, and always keep the original column.

The IQR rule (Tukey fences)

The interquartile range is the spread of the middle half of the data. A value is flagged if it lies more than 1.5 IQRs below the first quartile or above the third:

TEXT
Q1      =QUARTILE.INC(A2:A31, 1)
Q3      =QUARTILE.INC(A2:A31, 3)
IQR     =Q3 - Q1
Lower   =Q1 - 1.5*IQR
Upper   =Q3 + 1.5*IQR
Flag    =IF(OR(A2<$F$4, A2>$F$5), "Outlier", "")

This rule uses quartiles, which are not pulled by the extremes, so it still works when the outliers are big. Use 3 instead of 1.5 for "far out" values. Highlight flagged cells with a conditional formatting formula rule (Conditional Formatting), and list them with =FILTER(A2:A31, (A2:A31<F4)+(A2:A31>F5)) (FILTER and SORT).

The z-score rule

A z-score says how many standard deviations a value is from the mean: =(A2-AVERAGE($A$2:$A$31))/STDEV.S($A$2:$A$31), or =STANDARDIZE(A2, mean, sd). Values with an absolute z above 3 (some use 2.5 or 2) are suspects. The catch: the outlier itself inflates the mean and the standard deviation, so on small samples it can hide itself. The IQR rule is the safer first choice.

LARGE and SMALL

To look at the extremes without sorting, use =LARGE(A2:A31, 1), =LARGE(A2:A31, 2)... and =SMALL(...), or =SORT(A2:A31) in Microsoft 365. Seeing the top and bottom three values often tells you whether the highest is a plausible record or a misplaced decimal point.

JS IQR fences and z-scores on the same column

Press Run (or Ctrl + Enter) to see the console output here.

No output.

What to do with an outlier

  1. Check the source. Is it a typo (2500 for 25.00)? Fix it at the source.
  2. Keep it and use robust statistics such as the median and IQR when it is real.
  3. Exclude it from a specific calculation with a helper column: =IF(flag="Outlier", NA(), A2), since most statistical functions ignore #N/A only if you filter or wrap them. AGGREGATE(1, 6, range) averages while ignoring errors.
  4. Document the decision in a cell note or a notes column.

Normalization

Normalization rescales a column so columns in different units (dollars and years) become comparable, or so a machine-learning model is not dominated by the biggest numbers.

Min-max scaling

Min-max scaling maps the smallest value to 0 and the largest to 1:

TEXT
=(A2 - MIN($A$2:$A$31)) / (MAX($A$2:$A$31) - MIN($A$2:$A$31))

For another range, such as 0 to 100: multiply by 100, or generally new_min + scaled*(new_max-new_min). Min-max scaling is sensitive to outliers, because one huge value squeezes everyone else toward 0.

Z-score standardization

STANDARDIZE(x, mean, standard_dev) returns (x - mean) / sd. After standardizing, the column has mean 0 and standard deviation 1:

TEXT
=STANDARDIZE(A2, AVERAGE($A$2:$A$31), STDEV.S($A$2:$A$31))

Check it: =AVERAGE(B2:B31) should be (about) 0 and =STDEV.S(B2:B31) should be 1. Standardized values also answer "how unusual is this?" in one number, which is why test scores are often reported this way (Statistics for Analysts).

JS Min-max scaling and z-scores

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Linear interpolation

Interpolation estimates a value between two known points, assuming a straight line between them. If a table gives the price at 40 units (80) and 50 units (70), the price at 45 units is halfway: 75. The general formula between points (x1, y1) and (x2, y2) is:

TEXT
y = y1 + (x - x1) * (y2 - y1) / (x2 - x1)

With FORECAST.LINEAR on two points

FORECAST.LINEAR(x, known_ys, known_xs) fits a line through the points you give it. Give it only the two neighbors and it interpolates exactly:

TEXT
=FORECAST.LINEAR(45, B2:B3, A2:A3)

FORECAST is the older name, and TREND(known_y, known_x, new_x) gives the same result.

Automatically, from a table

For a lookup table in A2:A20 (x, sorted ascending) and B2:B20 (y), find the two neighbors of x in D1 with XLOOKUP and XMATCH (XLOOKUP):

TEXT
x1  =XLOOKUP(D1, A2:A20, A2:A20, , -1)       lower neighbor (exact or next smaller)
x2  =XLOOKUP(D1, A2:A20, A2:A20, , 1)        upper neighbor (exact or next larger)
y1  =XLOOKUP(D1, A2:A20, B2:B20, , -1)
y2  =XLOOKUP(D1, A2:A20, B2:B20, , 1)
y   =IF(x1=x2, y1, y1 + (D1-x1)*(y2-y1)/(x2-x1))

In versions without XLOOKUP, use MATCH(D1, A2:A20, 1) for the position of the lower neighbor and INDEX for the values (INDEX and MATCH). The IF(x1=x2, ...) guards the exact-match case, where the denominator would be zero. Values outside the table are extrapolation and are less trustworthy.

With LINEST and for filling gaps

For a fitted line through all points (not just two neighbors), LINEST or SLOPE/INTERCEPT give the line, and the interpolated value is slope*x + intercept. That smooths noise but ignores the exact values you have. To fill gaps in a series, use the neighbor formula above, or FORECAST.LINEAR using the nearest known cells on each side. For a time series with seasonality, use FORECAST.ETS (Correlation and Forecasting).

JS Interpolation between table rows

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A value is flagged by the IQR rule when it lies

    The fences are Q1 - 1.5IQR and Q3 + 1.5IQR. Quartiles are not pulled much by extreme values, so the rule stays reliable.
  2. 2. What are the mean and standard deviation of a column after standardizing every value with STANDARDIZE?

    Subtracting the mean centers the data at 0 and dividing by the standard deviation makes the spread 1.
  3. 3. Which formula gives min-max scaling to the range 0 to 1?

    Subtracting the minimum makes the smallest value 0, and dividing by the range makes the largest value 1.
  4. 4. The price at 40 units is 80 and at 50 units is 70. What is the linearly interpolated price at 45 units?

    45 is halfway between 40 and 50, so the interpolated price is halfway between 80 and 70, which is 75.

Summary

  • Flag outliers with the IQR rule (Q1-1.5*IQR, Q3+1.5*IQR), z-scores (|z| above about 3) and LARGE/SMALL; investigate before removing.
  • Min-max scaling maps to 0 to 1; STANDARDIZE gives mean 0 and SD 1; min-max is sensitive to outliers.
  • Linear interpolation is y1 + (x-x1)*(y2-y1)/(x2-x1), available as FORECAST.LINEAR or TREND on the two neighbors; XLOOKUP with match modes -1 and 1 finds them.
  • Outside the table is extrapolation, and a fitted line (LINEST) smooths instead of passing through the points.

Next: Financial Functions: PMT, NPV, IRR and More.