The `VARA` function in Excel is used to calculate the variance of a sample dataset. It differs from the `VAR` function by including text and logical values in its calculation. Here’s how it works:
- Text is interpreted as 0.
- Logical TRUE is interpreted as 1.
- Logical FALSE is interpreted as 0.
Syntax
VARA(value1, [value2], ...)
- value1: This is the first value or reference. This argument is required.
- value2, …: These are additional values or references up to 254 arguments. These are optional.
Steps to Use
=VARA(A1:A5)
- Enter Your Data: Make sure your data is entered in a row, column, or an array.
- Apply the VARA Function:
- Click on the cell where you want the variance result to be displayed.
- Enter the formula using the syntax above. For example, if your dataset is in cells A1 to A5, you would enter:
- Including Logical Values and Text:
- If your data includes logical values or text, they will be automatically included in the variance calculation.
Example
Suppose you have the following data in cells A1 to A5:
- A1: Number 10
- A2: Number 20
- A3: TRUE (will be counted as 1)
- A4: “Text” (will be counted as 0)
- A5: Number 30
You would use the formula:
=VARA(A1:A5)
This will calculate the variance based on the numbers 10, 20, 1, 0, and 30.
Notes
- The `VARA` function should be used when you need to include logical values and text (calculated as zero) in your variance calculations.
- If you don’t need to include text or logical values, or if your dataset doesn’t contain them, consider using the `VAR.S` function for more accurate statistical variance calculations based only on numeric data.
By using the `VARA` function properly, you can ensure your variance calculations account for all data types in your dataset.
Unlock Your Potential
Excel
Access
Power BI
