The beauty of Excel is the ability to do numerous mathematical procedures or complex computations with the use of an existing Function. One of those functions is the VAR Function, which returns the sample variant of a range of numbers. This sample of numbers can go as high as 30. One can, for example, easy calculate the variance of the cost of a number of items purchased. Or, as another example, calculate the variance in the number of items that were gathered in a series of attempts. While a person can always try to compute for this manually, the Excel function allows you to get this value quickly by simply typing a formula in relation to which cells contain the necessary data.
The syntax for the VAR function is quite simple.
So for example, if one were to study the variance in the cost of seven items ($10.95, $11, $7.65, $14.32, $9.89, $19.30, $8.99) and these costs were listed in cells B2 to B5, once can use the formula =Var(B2,B3,B4,B5,B6,B7) to get the result 15.44898
- Quick Excel Tip! When entering a range of cells, rather than having to type them one by one, you can opt to type them with the use of a colon (:) as a range indicator. So in the example, the formula can instead be written as =Var(B2:B7)
- Are you using Excel 2010? If you are using Excel 2010, you would be happy to know that the VAR function has been replaced with the VAR.S function for improved accuracy. The VAR.S function also can work with up to number arguments of up to 254 values.
- What about Var.P? The Var.P function is best used when calculating the variance for an entire population.
- Need to add specific values to the formula other than those in the range? You can do this as well by adding it into the formula. So, using the example above of seven values ($10.95, $11, $7.65, $14.32, $9.89, $19.30, $8.99) if you needed to also add $20 and $15 to the sample group, you would write the formula as =Var(B2,B3,B4,B5,B6,B7,20,15) to get a result of 19.62606
- Common Problems with the VAR function. If the VAR function is returning an unpredictable and unexpected result, this is most likely because you were providing the function with representations of numbers in text form and not actual numerical values. Remember in Excel, the format of a cell can affect the way the cell’s contents will interact with a formula. If the text representation of numbers were part of an array, however, the function will ignore those numbers and still return a result based on the actual numbers.
If you happen to have made this mistake, you can rectify this real quick by translating and converting all the representations of the numbers in text form towards numerical values. An easy way to accomplish this is to select all the cells that you plan to alter and convert (these must be within a single column), click Data then select Text to Columns. Delimited should be selected by default. Click next and you will be shown a number of delimiter options. Be sure to leave all unselected then click next. There will be a list corresponding to Column Data Formats shown. Pick General and then press Finish! Your array should work now.
Another common error one can get when using the VAR function is the
#DIV/0! Error. This error occurs if fewer than two numerical values were
supplied to the function. A Variance cannot be properly calculated
unless there are at least two values in a sample set.