QUARTILE.INC in Excel: Quartiles, IQR and Explicit Outlier Fences

• Introduced: the Microsoft function catalogue marks QUARTILE.INC as an Excel 2010 addition; an exact first build or preview date is not established here.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official QUARTILE.INC reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

=QUARTILE.INC(array, quart)

Both arguments are required. The selectors are 0=min, 1=25th percentile, 2=median, 3=75th percentile, and 4=max. Microsoft documents truncation of a noninteger selector.

For example, 1.9 selects the first quartile after truncation. Supplying 0.25 does not request the 25th percentile: it truncates to selector zero. Use 1 for that quartile, or PERCENTILE.INC with a fraction of 0.25.

Enter 2, 4, 8, 10, 14, 18, 22 and 30 in A2:A9.

For the lower quartile, the inclusive position is 1+7*0.25=2.75; 4+0.75*(8-4)=7. The upper position is 6.25, giving 18+0.25*(22-18)=19. The median averages 10 and 14.

=QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1)

Expected interquartile range: 12. This is a difference in the same units as the observations. It is not a percentage of the maximum and does not have the same meaning as the full range, 30-2=28.

For a fictional duration dataset measured in minutes, the IQR would be 12 minutes. Retain the units when comparing reports from different measurement systems.

A common exploratory policy flags observations below Q1-1.5*IQR or above Q3+1.5*IQR. This guide uses that policy as an explicitly chosen calculation, not as a universal definition of invalid data.

Put Q1 in D2, Q3 in D3 and =D3-D2 in D4. Put =D2-1.5*D4 in D5 and =D3+1.5*D4 in D6. The fences are -11 and 37. None of the original eight values crosses them.

In B2:B9 make a separate copy with the last value changed from 30 to 80. Q1 remains 7, Q3 remains 19, and IQR remains 12. The upper fence remains 37, so only 80 is flagged by:

=COUNTIF(B2:B9,">"&D6)

Expected count: 1. Investigate that record; the arithmetic alone does not show that it is erroneous or should be deleted.

On the original A2:A9, =QUARTILE.INC(A2:A9,1.9) gives 7, while =QUARTILE.INC(A2:A9,0.25) gives 2. The latter is a minimum, not the lower quartile.

MIN, MEDIAN and MAX provide independent controls for selectors 0, 2 and 4. The exact first and third quartiles can fall between observations, so they need not appear as rows in the source table.

The reference specifies #NUM! for an empty array or selectors outside 0–4. The examples do not establish the order of validation and truncation for negative fractions or values just above 4; use the documented integer selectors for unambiguous requests.

QUARTILE.EXC uses a different convention and excludes selectors 0 and 4 under its explicit error rule. Do not combine the lower quartile from one convention with the upper quartile from another. Mixed-type and error-valued input handling remains outside these all-numeric controls.

• Microsoft QUARTILE.INC reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.