PlanMaker MODE.SNGL: Repeated Values, Ties and Transformation Checks
Use PlanMaker MODE.SNGL with complete batch-size data, documented tie and no-repeat rules, frequency summaries and transformed-value examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
MODE.SNGL returns the most frequent numeric value, choosing the smallest tied mode when several values occur equally often. With batch sizes 12,12,18,18,18,24,24,30, the answer is 18 because it appears three times.
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 MODE.SNGL reference and historical manual. Examples were independently calculated; no native PlanMaker test was performed.
Syntax and documented boundaries
MODE.SNGL(Number1 [, Number2, Number3 ...])
The PlanMaker 2026 entry shows a leading numeric argument followed by optional additional numbers. It returns #N/A when no value occurs more than once. Where several repeated values share the greatest frequency, it returns the smallest one.
Supply numeric measurements. The short reference does not fully specify logical values, imported strings, error cells or empty-only datasets. These examples use explicit numeric cells and direct arguments. Brackets mark optional syntax and are not typed; use your installation’s accepted separators.
Reconstruct a batch-size frequency summary
Create BatchSizes, with columns A:J and eleven rows available. Put Size in A1, then 12,12,18,18,18,24,24,30 in A2:A9.
In E1:F1 enter Candidate and Count. Put 12,18,24,30 in E2:E5 and use:
| Cell | Formula | Expected result |
|---|---|---|
| F2 | =COUNTIF($A$2:$A$9,"12") |
2 |
| F3 | =COUNTIF($A$2:$A$9,"18") |
3 |
| F4 | =COUNTIF($A$2:$A$9,"24") |
2 |
| F5 | =COUNTIF($A$2:$A$9,"30") |
1 |
In H2 enter =MODE.SNGL(A2,A3,A4,A5,A6,A7,A8,A9) for 18. In H3 use =COUNT(A2:A9) for 8, and in H4 use =SUM(F2:F5) for 8. In H5 enter =F3/H3 for 0.375, or 37.5%.
The most frequent size is not used by a majority of these batches. In H6 enter =MEDIAN(A2:A9) for 18, and in H7 enter =AVERAGE(A2:A9) for 19.5. The median happens to equal the mode here; that is not a general identity.
Advanced: ties and a reversed measurement scale
Copy BatchSizes to TiedBatchSizes and change A7 from 24 to 12. The new data is 12,12,18,18,18,12,24,30. The frequencies are 3,3,1,1, so H2 becomes 12, the smaller of the two tied modes. H6 remains 18, while H7 becomes 18.
The original H5 still measures the share of candidate eighteen, 3/8. In H8 enter =F2/H3 for the selected twelve’s share, also 3/8. Explicit candidate labels prevent a fixed frequency cell from being mistaken for a universal “mode count.”
On TiedBatchSizes, put Transformed in B1, enter =100-A2 in B2 and fill through B9. The transformed readings are 88,88,82,82,82,88,76,70. In J2 enter =MODE.SNGL(B2,B3,B4,B5,B6,B7,B8,B9) for 82.
In J3 use =100-H2 for 88. J2 and J3 differ: the negative scale reverses the ordering of the two tied candidates. The original selected smallest mode was twelve, but after transformation, the smallest tied mode corresponds to eighteen instead. A single selected mode is not always interchangeable with transforming that selected value.
For a positive-scale contrast, put =2*A2+1 in C2 and fill through C9. In J4 enter =MODE.SNGL(C2,C3,C4,C5,C6,C7,C8,C9) for 25; in J5 use =2*H2+1, also 25. This strictly increasing transformation preserves the tied candidates’ order for the displayed data.
Reproduce the vendor’s three cases
On a separate VendorSingleMode sheet, enter:
| Cell | Formula | Documented result |
|---|---|---|
| B2 | =MODE.SNGL(1,2,4,4,4,2,1) |
4 |
| B3 | =MODE.SNGL(1,2,4,4,2,1) |
1 |
| B4 | =MODE.SNGL(1,4,2) |
#N/A |
The second formula’s ones, twos and fours each occur twice. PlanMaker selects the smallest, one. The third has no repeat and therefore no qualifying mode under the documented rule. Distinguish that error from a valid repeated zero.
Version history and interpretation
The PlanMaker 2018 manual, page 587 already documents MODE.SNGL with these rules and examples. This establishes documentation by 2018; it does not prove the original release. The source’s Excel 2010 compatibility note concerns Excel, not a PlanMaker release number.
The companion MODE reference calls MODE.SNGL an identical supplementary function. The earlier MODE guide explains the older spelling and historical syntax difference. Neither current entry labels the earlier function retired.
COUNTIF makes all candidate frequencies visible, MEDIAN finds the middle and AVERAGE calculates the mean. MODE.SNGL’s one returned value is not a complete distribution, a guarantee of a unique most-common value or an automatic policy for choosing a standard batch size.