PlanMaker PERCENTRANK.EXC: Exclusive Positions and Endpoint Examples
Understand PlanMaker exclusive percentile ranks, compare endpoint conventions and check a positive unit conversion with complete numeric inputs.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
PERCENTRANK.EXC expresses relative position on a scale strictly between 0 and 1. Unlike the inclusive version, SoftMaker’s nine-number example gives the minimum 0.1 and the maximum 0.9. “Exclusive” does not mean the minimum and maximum observations are removed from the input.
Availability and version history
- Introduced: Documented by the PlanMaker 2018 manual; the original introduction release and date are not established.
- Availability: Documented in PlanMaker 2026. This checked version is not a final supported-version endpoint.
- Scope: Current reference belongs to the PlanMaker 2026/NX manual family. FreeOffice and individual mobile-build availability are not established here.
- Checked: October 14, 2026, against the official PERCENTRANK.EXC reference and historical manual. Additional calculations are explicitly scoped models; no native PlanMaker test was performed.
Syntax and documented defaults
PERCENTRANK.EXC(Range, Number [, Digits])
The current reference requires a numeric range and a query value. A query absent from the observations is interpolated. The optional Digits defaults to 3, described as decimal places in current help.
The 2018 manual describes significant digits instead and says the count must be at least one. The wording does not prove a behavior-change date. Rounding versus truncation, fractional precision and the current invalid-count domain are not fully specified. Use positive integer counts as shown here, without assuming a rule from another spreadsheet.
Reproduce SoftMaker’s endpoint example
On VendorExclusive, enter 1,2,3,4,5,6,7,8,9 in A1:A9 without a header. Put these formulas in C1:C3:
| Cell | Formula | Documented result |
|---|---|---|
| C1 | =PERCENTRANK.EXC(A1:A9,1) |
0.1 |
| C2 | =PERCENTRANK.EXC(A1:A9,5) |
0.5 |
| C3 | =PERCENTRANK.EXC(A1:A9,9) |
0.9 |
Percentage formatting gives 10%, 50% and 90%. These are relative-position conventions, not a statement that 10% of the nine observations lie below their minimum. No observation in this dataset is below 1.
Compare exclusive and inclusive positions on a full dataset
Create PositionConventions, using A1:H12. Put Reading in A1 and enter 10,15,25,30,50,55,70,90,100 in A2:A10. Put Query, Exclusive, Inclusive and Difference in C1:F1, then enter the seven queries below.
In D2 enter =PERCENTRANK.EXC($A$2:$A$10,C2,4), E2 =PERCENTRANK.INC($A$2:$A$10,C2,4) and F2 =D2-E2. Fill D2:F2 through row 8.
| Row | C: Query | D: exclusive model | E: inclusive model | F: difference |
|---|---|---|---|---|
| 2 | 10 | 0.1 | 0 | 0.1 |
| 3 | 25 | 0.3 | 0.25 | 0.05 |
| 4 | 40 | 0.45 | 0.4375 | 0.0125 |
| 5 | 50 | 0.5 | 0.5 | 0 |
| 6 | 55 | 0.6 | 0.625 | -0.025 |
| 7 | 80 | 0.75 | 0.8125 | -0.0625 |
| 8 | 100 | 0.9 | 1 | -0.1 |
These broader outputs use independent linear-position models. For the j-th unique sorted observation, use j/(N+1) for the exclusive model and (j-1)/(N-1) for the inclusive model, interpolating between adjacent observations. They invert the respective position formulas in NIST’s percentile discussion. SoftMaker’s short reference documents interpolation and the three source examples but not its full calculation algorithm; the table is a model check, not native execution evidence.
For query 40, the fractional position is 4.5, halfway between 30 and 50. H2 =(4+(40-30)/(50-30))/10 gives 0.45. For query 80, position 7.5 gives 7.5/10 = 0.75. The largest differences between the two conventions appear toward the ends of this particular nine-value example.
Advanced: a change of positive measurement scale
Copy PositionConventions as DoubleReadings. Replace A2:A10 with 20,30,50,60,100,110,140,180,200 and C2:C8 with 20,50,80,100,110,160,200. Every input and query doubles.
Leave D:F unchanged. The independent expected columns remain exactly those in the table: multiplying both sides of each interpolation interval by two preserves the fractional position. Update H2 to =(4+(80-60)/(100-60))/10; it remains 0.45.
This check illustrates a positive unit conversion. Changing only the query, multiplying by a negative factor or changing the population is a different scenario. It also does not turn a percentile rank into a measurement in the original units.
Choosing a convention and recognizing uncertainty
Use the same convention when comparing reports. A median of 0.5 in this example does not make the endpoint conventions interchangeable. PERCENTRANK.INC and PERCENTRANK include 0 and 1; this function uses the exclusive convention.
The reference does not settle ties, all-equal ranges, singleton datasets, out-of-range queries, mixed types, exact error tokens or every precision boundary. The supplied complete, unique numeric data deliberately avoid those cases. All modeled fractions terminate within the requested four decimal places, so no unstated shortening rule is needed.
PERCENTILE.EXC accepts a fraction and returns a value, rather than ranking a query. RANK.EQ returns an ordinal position and has its own missing-query and tie rules. A similarly named function is not a universal substitute.
Confirm the target locale’s formula notation. SoftMaker’s note that Excel supports the dotted function from Excel 2010 is an Excel compatibility statement, not a PlanMaker introduction date or proof of historical cross-platform parity.