Excel VARP Function

The VARP function in Excel calculates the variance of an entire population of data, ignoring text and logical values.  This step by step tutorial will assist all levels of Excel users in the usage and syntax of the VARP function.  

Figure 1. Final result: Excel VARP function

Final formula: =VARP(B3:B9)

Syntax of the VARP Function

=VARP(number1,[number2],...])

• number1 – any number, array or cell reference representing a population whose variance we want to calculate
• Only number1 is required; succeeding numbers are optional

Setting up the Data

We have here column B containing a list of scores in B3:B7, an empty cell in B8 and a text string “text” in cell B9.  The VARP function ignores empty cells and text strings.  

In cell E2, we want to calculate the variance of the population by using the VARP function.  

Figure 2. Sample data to calculate population variance using VARP function

Calculate variance using VARP function

In order to calculate the variance of the entire population using VARP, let us follow these steps:  

Step 1.  Select cell E2

Step 2.  Enter the formula: =VARP(B3:B9)

Step 3.  Press Enter

The range B3:B9 contains our data.  Note that the VARP formula ignores the empty cell (B8) and the text we have entered in cell B9.    

As a result, our VARP formula calculates the variance in cell E2 which is 5.84.

Figure 3. Output: Calculate variance using VARP function

Notes:

• VARP function is used to calculate the variance if our data represents the entire population
• If our data is only a sample and does not represent the whole population, we can use either the VAR or VARA function
• VARP function ignores empty cells, text and logical values {TRUE; FALSE}
• Below table shows the comparison of VAR, VARA and VARP

Figure 4. Comparison: VAR, VARA and VARP

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.