PlanMaker VARPA: Population Variance, Mixed Inputs and Squared Units
Use VARPA in PlanMaker with complete mixed-type worksheets, independently checked results, documented argument rules and practical variance examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
VARPA calculates population variance while treating text as zero, TRUE as 1 and FALSE as 0. Empty cells are ignored. That makes a text status such as pending a real zero-valued observation, rather than a missing measurement.
Use it when those conversions match the meaning of your data. If text and logical values should be excluded from a referenced dataset, compare the documented numeric-only function, VAR.P.
Availability and version history
- Introduced: Documented by the PlanMaker 2010 manual; the original introduction release and date remain unknown.
- Availability: Documented in PlanMaker 2026. This identifies a documented version, not a last-working-version endpoint.
- Scope: The current entry belongs to the 2026/NX manual family; exact FreeOffice and individual mobile-build behavior is not established here.
- Checked: October 14, 2026, against the official VARPA reference and historical entry. The examples were independently calculated; no native PlanMaker test was performed.
Syntax and input rules
VARPA(Number1, Number2 [, Number3 ...])
The PlanMaker 2026 reference prints two leading required arguments. Each can be a value, a cell range or an array. The examples below supply two or more arguments, including two disjoint ranges where appropriate; they do not assume an undocumented single-range shorthand.
| Stored input | Contribution to this calculation |
|---|---|
| Number | The numeric value |
| TRUE | 1 |
| FALSE | 0 |
Text such as pending |
0, included in the count |
| Truly empty cell | Excluded from the count |
The calculation uses the population denominator n, where n is the number of included observations. The population model divides the sum of squared deviations by n. The reference describes VARPA as VARP with different text/logical handling; the linked numeric-only VAR.P entry explicitly states the n method.
Reproduce a mixed-input worksheet
Create a blank sheet named MixedSpread, with room through column H and row 9. Enter these values exactly; the notation leave empty is an instruction, not text to type.
| Row | Column A: original input | Column C: numeric reconstruction |
|---|---|---|
| 1 | Original | Mapped |
| 2 | 4 |
4 |
| 3 | 6 |
6 |
| 4 | pending |
0 |
| 5 | =TRUE() |
1 |
| 6 | =FALSE() |
0 |
| 7 | Leave empty | Leave empty |
Do not type quoted words "TRUE" or "FALSE" in A5:A6. The formulas produce logical values; text spellings would be evaluated as zero instead.
Place these formulas in the indicated cells. Decimal results are rounded to ten places where needed; retain full cell precision for later calculations.
| Cell | Formula | Expected result |
|---|---|---|
| F2 | =VARPA(A2:A4,A5:A7) |
5.76 |
| F3 | =VARPA(C2:C4,C5:C6) |
5.76 |
| F4 | =VAR.P(A2,A3) |
1 |
| F5 | =VARPA(A2,A3,A7) |
1 |
| F6 | =VARPA(A2,A3,A6) |
6.2222222222 |
| F7 | =VARPA(A2,A3,A4) |
6.2222222222 |
| F8 | =VARPA(2,2,3,4,1) |
1.04 |
| F9 | =STDEVPA(A2:A4,A5:A7)^2 |
5.76 |
F2 and F3 agree because the original inputs map to 4, 6, 0, 1, 0. F5 excludes the empty cell; F6 includes a FALSE zero and F7 includes a text zero. These comparisons keep the numeric inputs fixed, making the different treatment visible. F8 reconstructs the vendor’s example.
Check the result from the mapped values
In H2 enter =AVERAGEA(C2:C6) for the mean 2.2. In H3 enter =COUNT(C2:C6) for 5 observations. Put =(C2-$H$2)^2 in D2 and fill through D6. The squared deviations are 3.24, 14.44, 4.84, 1.44 and 4.84. In H4 enter =SUM(D2:D6) for their total, 28.8.
Dividing by 5 gives population variance 5.76. This reconstruction counts the two text/logical zeros but not A7. Merely applying a numeric format to pending does not make it a numeric measurement.
Advanced example: squared units and repeated observations
Variance has squared units. On a separate UnitScale sheet, put 20, 40, 60 in A2:A4, representing millimetres. Put =A2/10 in B2 and fill to B4 to obtain 2, 4, 6 centimetres.
| Cell | Formula | Expected result |
|---|---|---|
| D2 | =VARPA(A2,A3:A4) |
266.6666666667 |
| D3 | =VARPA(B2,B3:B4) |
2.6666666667 |
| D4 | =D2/D3 |
100 |
| D5 | =STDEVPA(B2,B3:B4) |
1.6329931619 |
D2 is measured in square millimetres and D3 in square centimetres. Dividing every measurement by 10 divides its variance by 100; standard deviation is divided by 10. A variance is not an average error expressed in the original measurement unit.
For a separate repetition check, enter 2, 4, 6, 2, 4, 6 in F2:F7. In H2 enter =VARPA(F2:F4,F5:F7): the result is still 2.6666666667. Every value has been duplicated equally, so the population distribution and variance are unchanged.
Compare =VARA(B2,B3:B4) in H3 with =VARA(F2:F4,F5:F7) in H4. Their results are 4 and 3.2. The squared-deviation total doubles from 8 to 16, but the sample denominator changes from 2 to 5. Repeated records can therefore change a sample statistic without adding independent information.
Troubleshooting and interpretation
- Unexpectedly different from VAR.P: Review stored text and Boolean values before changing formulas. Decide whether a missing measurement should really count as zero.
- Sample or complete population: Choose the population definition before calculating. Use VARA for the alternative denominator; changing the function does not make unrepresentative data representative.
- Empty, erroneous or imported inputs: The short entry does not establish exact error tokens for all-empty or insufficient sets, error-valued cells, numeric-text imports or formula-produced empty strings. The examples use explicit numbers, ordinary text, logical values and truly empty cells.
- Precision and portability: Display rounding is not an accuracy guarantee for extreme magnitudes. Recheck mixed-type handling and argument requirements when moving the formula to another spreadsheet product.