PlanMaker VARA: Sample Variance with Text, Booleans and Grouped Data
Use VARA 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
VARA calculates sample 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.S.
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 VARA reference and historical entry. The examples were independently calculated; no native PlanMaker test was performed.
Syntax and input rules
VARA(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 sample denominator n − 1, where n is the number of included observations. The sum of squared deviations is divided by n − 1.
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 | =VARA(A2:A4,A5:A7) |
7.2 |
| F3 | =VARA(C2:C4,C5:C6) |
7.2 |
| F4 | =VAR.S(A2,A3) |
2 |
| F5 | =VARA(A2,A3,A7) |
2 |
| F6 | =VARA(A2,A3,A6) |
9.3333333333 |
| F7 | =VARA(A2,A3,A4) |
9.3333333333 |
| F8 | =VARA(2,2,3,4,1) |
1.3 |
| F9 | =STDEVA(A2:A4,A5:A7)^2 |
7.2 |
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 − 1 = 4 gives sample variance 7.2. 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: combine groups without losing between-group variation
Averaging group variances generally does not give the variance of their combined observations. On a separate GroupedReadings sheet, put 2, 4, 6 in A2:A4 and 8, 10, 12 in B2:B4.
| Cell | Formula | Expected result |
|---|---|---|
| D2 | =AVERAGEA(A2:A4) |
4 |
| D3 | =AVERAGEA(B2:B4) |
10 |
| D4 | =AVERAGEA(A2:A4,B2:B4) |
7 |
| D5 | =VARA(A2,A3:A4) |
4 |
| D6 | =VARA(B2,B3:B4) |
4 |
| D7 | =(D5+D6)/2 |
4 |
| D8 | =VARA(A2:A4,B2:B4) |
14 |
| D9 | =(3-1)*D5+(3-1)*D6 |
16 |
| D10 | =3*(D2-D4)^2+3*(D3-D4)^2 |
54 |
| D11 | =(D9+D10)/(6-1) |
14 |
The within-group squared-deviation total is 16. The group means are each 3 units from the combined mean, adding 54 between groups. Thus the combined total is 70, and its sample variance is 70/5 = 14.
These formulas use group counts of 3 because each displayed group contains exactly three included numbers. For mixed-type groups, first determine each group’s included count after applying the text/Boolean rules. Empty cells do not add observations, while ordinary text contributes a zero observation.
Troubleshooting and interpretation
- Unexpectedly different from VAR.S: 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 VARPA 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.