Cellwise Start free

Statistics function

Excel QUARTILE.INC function

QUARTILE.INC splits your data into four equal parts. Quart 1 is the 25th percentile, 2 the median and 3 the 75th percentile; 0 and 4 return the minimum and maximum. Q3 minus Q1 is the interquartile range, a measure of spread that one extreme value barely moves.

=QUARTILE.INC(array, quart)

Arguments

array
The range of numbers. Text and empty cells are skipped.
quart
Which quartile: 0 minimum, 1 first quartile, 2 median, 3 third quartile, 4 maximum. Decimals are cut to a whole number.

QUARTILE.INC examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1EmployeeSalary ($)
2Ruiz55000
3Patel48000
4Okafor61000
5Lindqvist42000
6Moreau90000
7Chen51000
8Novak67000
9Silva58000

Download this sheet as an Excel practice file (.xlsx)

  1. =QUARTILE.INC(B2:B9,1)

    Result: 50250

    A quarter of salaries are at or below $50,250, the first quartile.

  2. =QUARTILE.INC(B2:B9,3)-QUARTILE.INC(B2:B9,1)

    Result: 12250

    The interquartile range: the middle half of salaries spans $12,250. The $90,000 salary does not stretch it.

  3. =COUNTIF(B2:B9,">"&QUARTILE.INC(B2:B9,3)+1.5*(QUARTILE.INC(B2:B9,3)-QUARTILE.INC(B2:B9,1)))

    Result: 1

    The usual outlier rule flags values above Q3 + 1.5 x IQR, here $80,875. One salary, $90,000, is above it.

  4. =QUARTILE.INC(B2:B9,1)-QUARTILE.EXC(B2:B9,1)

    Result: 1500

    QUARTILE.EXC uses the exclusive method and puts Q1 at $48,750, $1,500 lower. Use one method throughout a report.

How to use QUARTILE.INC

  1. Click the cell for the result.
  2. Type =QUARTILE.INC( and select the numbers.
  3. Type a comma and the quartile number, 1 or 3 for most uses.
  4. Press Enter.

Common QUARTILE.INC mistakes

Typing 25 for the first quartile

quart is 0 to 4, not a percentage. For any other percentile use PERCENTILE.INC(range,0.25).

A chart that disagrees with the table

A Box and Whisker chart can use inclusive or exclusive quartiles (Format Data Series > Quartile Calculation). Match it to the function you report.

Using QUARTILE.EXC on tiny lists

QUARTILE.EXC returns #NUM! for 0 and 4, and for Q1 or Q3 when there are too few values. QUARTILE.INC always works.

QUARTILE.INC FAQ

How do I calculate the interquartile range in Excel?

Use =QUARTILE.INC(range,3)-QUARTILE.INC(range,1). There is no separate IQR function.

What is the difference between QUARTILE.INC and QUARTILE.EXC?

INC includes the minimum and maximum in the calculation and accepts quart 0 to 4. EXC excludes them, accepts only 1 to 3, and gives a wider spread on small lists.

Is QUARTILE the same as QUARTILE.INC?

Yes. QUARTILE is the older name and returns the same result.

Guides that use QUARTILE.INC

Related functions

Updated