Zoho Sheet QUARTILE.EXC Function: Exclusive quartiles and statistical cutoffs

The word exclusive describes the percentile convention: it excludes the 0% and 100% endpoints when calculating quartiles. For a sorted sample with n observations, conventional exclusive percentile interpolation uses the one-based position (n+1)*p, where p is 0.25, 0.5 or 0.75. For eight observations the Q1 position is 2.25 and the Q3 position is 6.75. Zoho’s function article describes percentiles from 0 to 1 exclusive. However, it also contains a conflicting remark claiming q=0 and q=4 correspond to MIN and MAX; see the documented ambiguity below. Our numerical examples deliberately use only q=1, 2 and 3.

=QUARTILE.EXC(number_list; q)

• number_list (required): a range or list containing the data to analyze, for example B2:B9. The official reference shows a range such as A2:A6.
• q (required): the quartile index. For worked, nonboundary examples, use 1 for Q1, 2 for the median and 3 for Q3. Published help lists #NUM! when q is outside 0–4, while its text describes an exclusive method and does not clearly resolve q=0 and q=4. Treat these boundary outcomes as unverified in a native Zoho workbook, not confirmed. The article also does not establish how fractional quartile-index values are coerced; use integer indices in production examples.

This differs from PERCENTILE.EXC(data;0.25): the percentile function receives 0.25 for the first quartile, whereas QUARTILE.EXC(data;1) receives the quartile index 1. Do not confuse the two argument scales.

Create a blank worksheet and enter the following data in A1:B9. Do not sort it for a function to work; we deliberately list the quantities in ascending order so that manual calculations are easy to audit.

These values are hypothetical. Eight observations are convenient for learning, but production decisions need larger, representative samples. Quartiles describe the distribution of numeric observations, not which individual product is best or worst. If the observations have very different importance (for example one represents one sale and another represents a million sales), this unweighted calculation may be unsuitable.

1. Enter the eight quantities in B2:B9 exactly as shown.
2. In D2 enter =QUARTILE.EXC(B2:B9;1) — expected 9.
3. In D3 enter =QUARTILE.EXC(B2:B9;2) — expected 18.
4. In D4 enter =QUARTILE.EXC(B2:B9;3) — expected 27.
5. In E2 enter =QUARTILE.INC(B2:B9;1) — expected 11 under the inclusive convention.
6. In E4 enter =QUARTILE.INC(B2:B9;3) — expected 25. Compare the conventions instead of assuming they produce identical thresholds.

Manual arithmetic: With n=8, the exclusive Q1 location is (8+1)*0.25=2.25. The second and third sorted quantities are 8 and 12, giving 8+0.25*(12-8)=9. Q3 lies at (8+1)*0.75=6.75, between 24 and 28, giving 24+0.75*(28-24)=27. The median lies halfway between 16 and 20, giving 18. These expected results were calculated independently using the conventional exclusive definition; they were not obtained by opening or executing Zoho Sheet.

For a replenishment dashboard, use the exclusive interquartile range:

=QUARTILE.EXC(B2:B9;3)-QUARTILE.EXC(B2:B9;1)

Expected 18 units, compared with 14 units for the inclusive IQR. In a small sample this four-unit difference may substantially affect a rule based on quartile thresholds. Neither is a universal winner: the choice should be set by your reporting methodology, sample size and audience.

Count the products above the conventional exclusive upper quartile with:

=COUNTIF(B2:B9;">"&QUARTILE.EXC(B2:B9;3))

Expected 2 products (28 and 32 units). The text criterion is constructed by concatenating ">" with the calculated threshold. If your environment requires different formula separators, verify using a native Zoho Sheet worksheet; the expression here follows the semicolon syntax in Zoho’s function help.

An exploratory outlier rule using exclusive quartiles would have lower fence 9-1.5*18 = -18 and upper fence 27+1.5*18 = 54. All eight observations lie inside the fences. Outlier fences are screening heuristics; they are not claims that every value inside them is valid or that every value outside is a data error.

The Zoho QUARTILE.EXC help article says the function uses percentile values between zero and one exclusive, but its remarks also state the function equals MIN for q=0 and MAX for q=4. In addition, its generic error guidance names #NUM! for q outside 0–4 rather than specifying what occurs at the endpoints. Those statements do not completely settle actual boundary behavior. Microsoft Excel’s documented QUARTILE.EXC rejects q≤0 or q≥4 with #NUM!, even though its own support remarks include an inconsistent generic MIN/MEDIAN/MAX sentence. Do not transplant Excel’s boundary result into Zoho without a real Zoho execution. For endpoints, use explicit =MIN(B2:B9) and =MAX(B2:B9) and flag any q=0/q=4 Zoho test as pending.

For very small arrays, an exclusive requested percentile rank can lie outside the observed data, so an ordinary interpolated value may not exist. The eight-record example avoids that issue. The vendor’s article does not establish every minimal sample-size error condition. Add native checks for n=1, n=2, n=3 and noninteger q before creating tutorials that promise exact error messages.

Both Microsoft and Google document exclusive quartile formulas, but their UI/locale separators and edge behavior must not be inferred from Zoho’s syntax alone. Microsoft’s help uses QUARTILE.EXC(array,quart) and explicitly documents q≤0 and q≥4 as errors; Google’s reference describes the valid quartile index as strictly between 0 and 4. That convergence supports the conventional meaning of exclusive, not an untested Zoho implementation claim. See Microsoft’s QUARTILE.EXC reference and Google Sheets QUARTILE.EXC reference. In all three products, verify the actual build and type handling when importing a workbook from another platform.

Check the number range first. A header or a copied extra row can change the result. Use B2:B9, not B1:B9, for the practice dataset. A completely empty numerical input is documented as a possible #NUM! situation. If all values are text, first convert intended numbers to actual numeric cell values. The vendor’s general error table also lists #VALUE! for wrong argument types, #REF! for invalid references and #NAME! for an invalid function name. Which error appears for every mixed-data coercion is not verified by this guide.

Understand ties and repeated rows. Two identical quantities count as two observations; that is usually correct for two different products. De-duplicating values changes the distribution and is normally inappropriate. Negative inventory balances should be investigated rather than silently excluded; a quartile can legitimately be negative if negative numbers were intentionally supplied.

Check the intended definition of a quartile. Different statistical systems and textbooks use different conventions for interpolating observations. A quartile from one convention should not be compared to a published benchmark using another without identifying its method. A quartile is a distribution summary, not a prediction or an exact count of records below it—especially with repeated values.

Troubleshoot a surprise result methodically. First enter a literal 1, 2 or 3 for q. Then verify every input cell is numeric; evaluate the sample data exactly as shown; compare inclusive and exclusive results; and finally test any unusual edge case in your own Zoho Sheet account. Do not treat the independent Python calculations in this guide as proof that a particular Zoho account, language setting, desktop release or mobile application behaves identically.

The function appears in Zoho’s public Sheet function-reference catalogue and its individual help article was checked October 12, 2026. The retrieved article does not supply a dependable first-supported release date, complete platform matrix, tenant rollout requirements or a promise of behavior across all offline clients. Therefore, this guide does not invent a version number or assert support on every device. Syntax shown uses the semicolon argument separator in Zoho’s published help; locale or editing mode can affect separators in an actual workbook. Validate the formula directly in the version and environment used by your team before depending on it.

• MEDIAN — the middle observation
• PERCENTILE.INC — inclusive percentile thresholds
• PERCENTILE.EXC — exclusive percentile thresholds
• COUNTIF — count records meeting a threshold

Authoritative function reference: Zoho Sheet function help (checked October 12, 2026). Catalogue comparison: Zoho Sheet functions catalogue.

Verification status: Official documentation checked; interpolation and example outcomes independently calculated. Not executed in Zoho Sheet. No genuine application screenshots are included; no mock application images are used. Native runtime and exceptional-input behavior remain unverified.

The official Zoho QUARTILE.EXC page has a genuine unresolved conflict between the description of an exclusive percentile and remarks identifying q=0 and q=4 with extrema. Treat 0/4 behavior, very small datasets and fractional q as unverified cases. No definite Zoho error claim is made for these exact cases.