Zoho Sheet PERCENTILE.EXC Function: Exclusive Percentiles and Worked Examples

=PERCENTILE.EXC(number_list; fraction)

Zoho’s function description and #NUM! explanation say the fraction range is exclusive. Documentation inconsistency: On the same Zoho page, the argument detail calls the fraction “0 to 1, inclusive.” These statements conflict. This guide treats the expressly exclusive title, function description and #NUM! condition as the intended behaviour, while flagging exact endpoint and small-sample cases for native testing. It does not silently convert the wording inconsistency into proof of tested results.

The official help illustrations use semicolons to separate function arguments. Locale settings and Zoho’s different editing modes may affect accepted typing syntax. A number formatted as 75% should mean the fraction 0.75; the plain integer 75 is not a valid percentile fraction.

Think of a percentile as a location within numeric observations sorted from smallest to largest. Several well-established conventions assign a fractional rank to that location. A common independent calculation model for exclusive percentiles sets rank = (n + 1) × fraction, where n is the number of numeric observations; adjacent values are interpolated when the rank falls between whole-number positions.

By contrast, an inclusive model often uses rank = 1 + (n − 1) × fraction. Neither formula is printed in Zoho’s checked help text; the distinction here is explicitly labelled as a standard mathematical model, not a native Zoho implementation test. The official examples confirm that Zoho distinguishes .EXC and .INC numerically, but the precise implementation must be validated for edge cases before relying on edge-case results.

An exclusive percentile can be useful in statistical coursework and analysis plans that require this convention. It is not inherently more accurate than an inclusive percentile for every small retail sample. Choose the convention based on the analytical specification, then use it consistently across time periods and charts.

Enter this fictional data at A1:C11:

Sorted values: 6, 8, 8, 12, 15, 18, 18, 18, 30, 50. The original worksheet does not need to be sorted first. Make sure column B values are truly numeric, especially when importing a CSV.

Example 1 — exclusive first quartile

1. Paste the sample into A1:C11, keeping the numeric values in column B.
2. Select E2 and enter:

=PERCENTILE.EXC(B2:B11;0.25)

Independently expected: 8. For ten observations, (10 + 1) × 0.25 = 2.75. Both the second and third sorted values are 8, so the interpolated value remains 8. The matching inclusive 25th-percentile model gives 9 instead.

Example 2 — the median position

=PERCENTILE.EXC(B2:B11;0.5)

Expected: 16.5. The fractional rank is 5.5. The fifth value is 15 and the sixth is 18; halfway between them is 16.5. In this sample both percentile conventions and MEDIAN agree at 50%, but their other percentile thresholds can diverge.

Example 3 — exclusive upper quartile

=PERCENTILE.EXC(B2:B11;0.75)

Expected: 21. Rank 8.25 lies one-quarter of the way between the eighth sorted value, 18, and the ninth, 30: 18 + 0.25 × (30 − 18) = 21. Compare =PERCENTILE.INC(B2:B11;0.75), with an independently expected threshold of 18. For clear reports, label the convention in the column header.

Example 4 — 90th percentile

=PERCENTILE.EXC(B2:B11;0.9)

Expected: 48. Rank 9.9 is close to the top of the dataset: 30 + 0.9 × (50 − 30) = 48. The corresponding inclusive model predicts 32. Such a large difference arises from a small ten-row sample with a big gap between 30 and 50. It does not establish that one convention is universally preferable.

Example 5 — a lower-tail percentile

=PERCENTILE.EXC(B2:B11;0.1)

Expected: 6.2 from rank 1.1, which sits 10% of the way between 6 and 8. This predicted threshold is not an observed transaction count. When counts must remain whole numbers for operational actions, apply an independently justified rounding or classification policy rather than changing the underlying percentile formula invisibly.

Example 6 — using a control cell

Set F2 to numeric 0.75, or enter and format numeric 75% in F2. Then use:

=PERCENTILE.EXC(B2:B11;F2)

Expected: 21 while F2 is 0.75. Changing F2 to 0.5 should change the independently expected result to 16.5. Clearly label F2 as Percentile fraction so colleagues do not accidentally type 75 or confuse the input with an absolute unit threshold.

Add Exclusive priority to D1 and enter in D2, then fill through D11:

=IF(B2>=PERCENTILE.EXC($B$2:$B$11;0.75);"Investigate capacity";"Normal")

The calculated exclusive cutoff is 21. Under that cutoff, Storage case (30) and Retro game (50) are the only rows flagged, giving two of ten records. In contrast, a policy using PERCENTILE.INC(...;0.75) with >= flags five rows because three records tie at 18. This comparison is a good reason to document the method, not to make the exclusive rule a universal business rule.

For a dynamic stock dashboard, store the chosen method and percentile in visible control cells so reviewers can reproduce the threshold. Use a second column containing absolute stock on hand; high weekly units sold may be normal if available stock is plentiful. Percentiles only analyze the supplied numerical distribution. They do not account for profit, supply lead times, seasonality, returns or future demand.

When comparing multiple weeks

Do not compare an exclusive percentile calculated from ten records in Week 1 with an inclusive percentile calculated from 50 records in Week 2 and call the difference a trend. Keep method, definition of a valid observation, date window and treatment of blank rows consistent. Where operational decisions depend on an unstable small dataset, consider reporting both raw observations and the percentile, plus sample size with COUNT(B2:B11).

• Endpoint fractions: Zoho’s explanation identifies #NUM! when the requested exclusive fraction is not strictly between 0 and 1. Because the argument description conflicts with that statement, explicitly include 0 and 1 in a native test plan rather than claiming they were actually tested here.
• Empty numeric input: Zoho documents #NUM! when number_list is empty. Confirm that source cells contain usable numbers, not just text that looks numeric.
• Extreme fractions in a small dataset: The standard exclusive rank formula may fall below 1 or above n, meaning interpolation is not possible. With ten observations, an independent model has no in-range rank for 0.05. Zoho’s help does not explicitly specify its error for every such interior fraction; validate this case natively rather than asserting Excel’s exact behaviour for Zoho.
• #VALUE!: The official generic error list mentions invalid argument data types. Numeric text imported from another system requires inspection; do not assume every coercion detail from Excel carries over.
• #NAME!: Ensure the dot and function name are typed correctly: PERCENTILE.EXC. Check local function availability in the target app or workbook format.
• #REF!: Repair deleted range references before changing function settings.
• #NUM! caused by 75 instead of 0.75: Convert a percent input correctly; typing 75 represents 7500% as a mathematical fraction.
• Duplicates, blanks and hidden rows: Ties can change how many rows cross a threshold. The checked Zoho article does not fully define text coercion, handling of hidden or filtered observations, or duplicate ranking at all edges. Mark these for real Zoho execution.
• Precision and rounding: A display such as 21.0 may use cell formatting. Do not round the source values or cutoff prematurely when using thresholds for flags.
• Very small samples: Mathematical interpolation cannot substitute for a reliable population estimate. State sample size and collection period alongside unusually high percentiles.

Zoho’s current English reference uses PERCENTILE.EXC(number_list; fraction). Microsoft Excel documents PERCENTILE.EXC(array,k) with strict 0<k<1, interpolated ranks and an additional out-of-interpolation #NUM! case. Excel’s specific calculation is useful for independent compatibility cross-checks, but is not proof of an identical Zoho error result for every input or platform. The most important in-application difference to validate is Zoho’s contradictory argument-domain wording, not to assume away the discrepancy.

Google Sheets and other programs offer statistical percentile calculations, but their exact syntax, names and error handling should be verified against their own up-to-date documentation before conversion. A conventional formula exported from Zoho to another product can also be affected by argument delimiters and local decimal punctuation.

Use PERCENTILE.INC if an inclusive 0-to-1 definition is required; use MEDIAN for a straightforward middle value; use MIN or MAX when you specifically need endpoints. QUARTILE.EXC requests numbered quartiles rather than arbitrary fractions. PERCENTRANK and variants answer a different question: the relative standing of a given observation, rather than the observation value at a selected percentile.

Release and platform notes

Zoho’s September 2026 What’s New announcement documents Excel-native formula syntax by default for offline files in beta Windows/macOS desktop apps. That does not prove historical availability of PERCENTILE.EXC, web/mobile parser parity or identical behaviour in all account types. This guide claims no first-supported version and does not treat Excel or Python arithmetic as a Zoho runtime test.

Does “exclusive” mean the formula ignores rows equal to the result? No. The name describes the percentile convention. A later comparison such as B2>cutoff or B2>=cutoff separately determines how tied records are classified.

Can PERCENTILE.EXC return a value that is not in the cells? Yes, interpolation can produce a fractional result between two observations. In our sample, 21 is not itself a weekly-units entry.

Why might an exclusive percentile fail on a tiny list? Under the standard method, some fractions would require interpolation outside the smallest or largest observed rank. That mathematical condition is distinct from the explicitly documented 0-or-1 argument-domain error; native Zoho testing is still required for individual extreme fractions.

Can I switch all existing PERCENTILE formulas to .EXC? Not automatically. They use different percentile definitions. Zoho recommends migrating legacy PERCENTILE toward .INC, not .EXC indiscriminately.

• Primary Zoho reference: PERCENTILE.EXC, checked 2026-10-12, including the documented exclusive bounds and contradictory inclusive argument description.
• Zoho comparisons: PERCENTILE.INC, PERCENTILE, both checked 2026-10-12.
• Cross-product reference: Microsoft PERCENTILE.EXC, checked 2026-10-12.
• Platform documentation: Zoho Sheet What’s New, September 2026 desktop-beta formula announcement, checked 2026-10-12.
• Testing matrix: Documentation checked; independent numerical predictions calculated; Zoho Sheet execution 0, platform-variant execution 0.