VARA
Estimates variance based on a sample, including text and logical values. Unlike VAR, VARA treats text as 0 and TRUE as 1, FALSE as 0. Use when your sample data may include non-numeric entries.
VARA(value1, [value2], ...)Arguments
value1The first value or range of the sample. Text evaluates to 0, TRUE evaluates to 1, FALSE evaluates to 0.value2Additional values or ranges. Up to 255 arguments total.(optional)
=VARA(A1:A10)Calculates sample variance including any text (as 0) or logical values in A1:A10
=VARA(1,2,3,TRUE,FALSE)TRUE counts as 1 and FALSE counts as 0 in the variance calculation
=VARA(B2:B50)Variance of a mixed dataset where some entries may be text or logical
- •VARA includes text values (as 0) and logical values; VAR ignores them
- •Use VAR when you want to exclude non-numeric cells from the calculation
- •VARA divides by n-1 (sample variance); VARPA divides by n (population variance)
- •If your data is purely numeric, VARA and VAR return the same result
- •Forgetting that VARA treats text as 0 and TRUE/FALSE as 1/0, which can silently skew a variance calculation if a range includes stray text entries that were meant to be ignored entirely
- •Confusing VARA with VAR - VAR ignores text and logical values completely, while VARA includes them using the 0/1 conversion rules, and choosing the wrong one changes the result whenever non-numeric entries are present
- •Using VARA on a purely numeric range without realizing it's unnecessary there - VARA and VAR return identical results when there's no text or logical data to treat differently
VARIgnores text and logical values entirely rather than converting them to 0 or 1 like VARA, giving a different result whenever non-numeric entries are present.VARPACalculates population variance using the same text/logical-inclusive rules as VARA, rather than VARA's sample-based approach.STDEVATakes the square root of what VARA calculates, returning a value in the same units as the original data.How does VARA handle text values in the range?
It treats any text as 0, rather than ignoring it the way VAR does - this can noticeably shift the result if your data includes stray text entries you actually wanted excluded.
What's the difference between VARA and VAR?
VAR ignores text and logical values completely, calculating variance only from genuine numbers. VARA instead converts TRUE to 1, FALSE to 0, and any text to 0, including them in the calculation.
Do VARA and VAR ever give the same result?
Yes, whenever the range contains only numbers and no text or logical values - in that case there's nothing for VARA's special conversion rules to affect.
Need to translate a formula using VARA?
Use our translator to convert your complete formula