The `VARPA` function in Excel is used to calculate the variance based on an entire population, including logical values and text representations of numbers. This function is useful when you want to estimate the variance of a dataset that represents the whole population.
Here’s how to use the `VARPA` function:
Syntax
VARPA(value1, [value2], ...)
- value1, [value2], …: These are the arguments that represent the population. You can enter them directly as numbers, as cell references, or as ranges. You can have up to 255 arguments.
Key Points
- Logical Values: The function considers TRUE as 1 and FALSE as 0.
- Text: Text that can be interpreted as numbers is included in the calculation. For example, the text “123” will be considered as the number 123. If the text cannot be interpreted as a number, it is ignored.
- Population vs Sample: Use `VARPA` when you want to calculate variance for an entire population. If you’re calculating it for a sample, use `VARP`.
Example
Suppose you have a dataset consisting of:
- A1: 10
- A2: 20
- A3: TRUE (interpreted as 1)
- A4: “100” (interpreted as 100)
- A5: “some text” (ignored, as it cannot be interpreted as a number)
You want to calculate the variance of these values. You would use the `VARPA` function as follows:
=VARPA(A1:A5)
This formula considers the values as 10, 20, 1, and 100. “some text” is ignored. `VARPA` will then compute the variance based on these values.
Other Tips
- Double-check that the range or cells you are calculating include only the relevant data for your analysis.
- `VARPA` is available in Excel 2007 and later versions. For earlier versions, you might need to use a different method for calculating variance over a whole population that includes logical values and text numbers.
Unlock Your Potential
Excel
Access
Power BI
