While there are similarities between these functions, there are some important things to consider before you use them. In this article, we\u2019ll explain:<\/p>\n
- \n
- What are variance functions in Excel and what are they used for?<\/a><\/li>\n
- How do variance functions work in Microsoft Excel?<\/a><\/li>\n
- Things to consider before using variance functions in Excel<\/a><\/li>\n
- How to calculate variance in Excel: A step by-step guide<\/a><\/li>\n<\/ol>\n
How do variance functions work in Excel? To help you, let\u2019s run through the basics.<\/p>\n
1. What are variance functions in Excel and what are they used for?<\/h2>\n
Variance works by determining the spread of values against the mean. If you have a set of exam results for a group of students, you might end up with wildly different values in two separate exams, but with the same average. By determining the variance, you can determine how well the group performed as a whole.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-variance-example.png\" "\\"An")
<\/p>\nYou can calculate this spread (the variance) using Excel\u2019s variance functions. As we\u2019ve mentioned, there are two main forms of variance that you can calculate in Excel: population variance<\/strong> and sample variance<\/strong>. In this context, population<\/strong> is the entire set of data, rather than a sample<\/strong> (or smaller subset) of it.<\/p>\n
To calculate these values, you can use one of six variance functions in Excel. For sample variance, you can use the VAR, VAR.S<\/strong> or VARA<\/strong> functions. VAR<\/strong> is the original function, while VAR.S<\/strong> is the newer replacement, offering some speed enhancements over the original.<\/p>\n
VAR and VAR.S only support numerical values, but if you want to use text strings or logical tests for a sample set, you\u2019ll need to use VARA<\/strong> instead.<\/p>\n
For population variance, you\u2019ll need to use the VARP, VAR.P<\/strong> or VARPA<\/strong> functions. As with the sample variance functions, VARP<\/strong> is the original, while VAR.P<\/strong> is the newer (and recommended) replacement, with both functions working only with numerical values. To work with text strings or logicals, use VARPA<\/strong> instead.<\/p>\n
If you\u2019re thinking that this sounds a lot like standard deviation<\/a>, that\u2019s because it (almost) is. Standard deviation calculates, on average, how far your values are from the mean. Variation is simply the standard deviation value squared, which gives you an idea of how far all of your numbers are spread from the average.<\/p>\n
2. How do variance functions work in Excel?<\/h2>\n
As we\u2019ve already mentioned, there are six variance functions that you can use in Excel, split into two categories to deal with either population or sample variation.<\/p>\n
These are:<\/p>\n
- \n
- VAR, VAR.S<\/strong> or VARA<\/strong> for sample variance,<\/strong> and<\/li>\n
- VARP, VAR.P<\/strong> or VARPA<\/strong> for population variance<\/strong>.<\/li>\n<\/ul>\n
Of these six, two functions (VAR<\/strong> and VARP<\/strong>) are considered outdated, having been replaced with VAR.S<\/strong> and VAR.P<\/strong>. These are interchangeable for the time being, but could be removed from Excel in the future.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-variance-numbers-example.png\" "\\"An")
<\/p>\nFour of these functions (VAR, VARP, VAR.S<\/strong> and VAR.P<\/strong>)focus on numerical<\/strong> data. That means that, if you\u2019re a budding data analyst<\/a> trying to work out the variance from a set of numbers using either a sample of a data set or the entire data set, you\u2019d want to use these functions.<\/p>\n
Should your data be mixed, however, you\u2019ll need to use VARA<\/strong> or VARPA<\/strong> instead. These functions support text, numbers, and logical values (TRUE, FALSE<\/strong>, 1<\/strong> or 0<\/strong>).<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-variance-text-example.png\" "\\"An")
<\/p>\nBy support, we mean that text strings and logical results are converted to the numerical equivalent, where a text string is counted as a 0<\/strong> (or FALSE<\/strong>). Logical values are counted as their numerical equivalent (0<\/strong> for FALSE<\/strong> or 1<\/strong> for TRUE<\/strong>). This can have an impact on your overall results, so choose your functions carefully.<\/p>\n
If you want to create a formula using any of these variance functions, you\u2019ll need to use a set structure. The structure remains the same for each six functions:<\/p>\n
- \n
- =VAR(value1,<\/strong>value2,<\/em><\/strong> \u2026)<\/strong><\/li>\n
- =VAR.S(value1,<\/strong>value2<\/em><\/strong>, \u2026)<\/strong><\/li>\n
- =VARP(value1,<\/strong>value2<\/em><\/strong>, \u2026)<\/strong><\/li>\n
- =VAR.P(value1,<\/strong>value2,<\/em><\/strong> \u2026)<\/strong><\/li>\n
- =VARA(value1,<\/strong>value2,<\/em><\/strong> \u2026)<\/strong><\/li>\n
- =VARPA(value1,<\/strong>value2, **<\/em><\/strong>\u2026)**<\/li>\n<\/ul>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-var-example.png\" "\\"An")
<\/p>\nThe only required argument in a variance formula using these functions is the reference to the data you use (value1<\/strong>). This can be referenced as a range of cells or as values directly (where value1<\/strong> is your first value, value2<\/strong> is your second value, etc).<\/p>\n
Only one value (value1<\/strong>) is required for a variance function to work. For cell ranges, this is considered as a single value (value1<\/strong>) for the purpose of creating your formula.<\/p>\n
3. Things to consider before using variance functions in Excel<\/h2>\n
There are plenty of considerations to make before you decide to calculate variance in Excel using these functions. In particular, you\u2019ll need to consider:<\/p>\n
- \n
- While VAR and VAR.S are technically interchangeable, VAR.S<\/strong> is the replacement Excel function for sample data sets and should be used in the first instance. Likewise, VAR.P<\/strong> should be used over VARP for population data sets as the newer function.<\/li>\n
- VAR, VAR.S, VARP and VARP only support numerical<\/strong> values. Other values (text strings, logical values, etc.) are ignored and won\u2019t count towards your result.<\/li>\n
- If you want to count text or logical values as you calculate variance, you\u2019ll need to use VARA<\/strong> (for samples) or VARPA<\/strong> (for population sets).<\/li>\n
- You can use references to cell ranges (eg. =VAR.S(A1:D10)<\/strong>) in your variance formulas, or reference each value separately (eg. =VAR.S(1,2,3,4)<\/strong>).<\/li>\n
- If you reference each value separately, you can use up to 254 different values<\/strong>. This is an Excel limitation and can\u2019t be increased. If you require more, fill out your spreadsheet first, then use a reference to the cell range containing those cells instead.<\/li>\n
- Only a single<\/strong> argument (value1<\/strong>) is required, which can contain a single value or a reference to a range of cells. However, to calculate variance from a single value is redundant, so you\u2019ll need to use more arguments if you\u2019re typing these into your formula directly.<\/li>\n
- If you\u2019re adding a text string as a value in a variance formula, you\u2019ll need to reference it in another cell for the formula to work, as directly adding a text string as a value<\/strong> argument will cause a #VALUE<\/strong> error to appear.<\/li>\n<\/ul>\n
4. How to calculate variance in Excel: A step by-step guide<\/h2>\n
If you have a small sample from a larger data set, you can use the VAR<\/strong>, VAR.S<\/strong> or VARA<\/strong> functions to calculate the variance. If you\u2019re trying to calculate variance in Excel using the population data set (that is, the entire set of data, rather than the smaller sample), you can do this using VARP, VAR.P<\/strong> or VARPA<\/strong> instead.<\/p>\n
For the purpose of this guide, references to VAR<\/strong> and VAR.S<\/strong> are interchangeable. We\u2019ve used VAR.S<\/strong>, which is the newer and recommended function, but the older VAR<\/strong> can be used (for the time being) in older workbooks. If you can, however, use VAR.S<\/strong>.<\/p>\n
Likewise, references to VARP<\/strong> and VAR.P<\/strong> are also interchangeable, but you should use VAR.P<\/strong> in the first instance. VARA<\/strong> and VARPA<\/strong> remain available for all Excel users, regardless of the version used.<\/p>\n
Step 1: Select an empty cell<\/h3>\n
To insert a variance function into a new formula, start by opening the Excel workbook containing your data and selecting an empty cell. Alternatively, you can open a new workbook, making sure that the sheet containing your data remains open and minimized.<\/p>\n
With the cell selected, press the formula bar<\/strong> at the bottom of the ribbon bar until you see the blinking cursor.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-var-ribbon-bar.png\" "\\"An")
<\/p>\nWhen the blinking cursor is visible, you\u2019re ready to begin inserting your new formula.<\/p>\n
Step 2: Insert your data set directly or using cell references<\/h3>\n
As we explained earlier, all variance functions in Excel use the same structure to create new formulas. To insert a new variance function using a sample data set (a smaller sample of a larger population set), start by typing =VAR.S(<\/strong> or =VARA(<\/strong> into the formula bar at the top.<\/p>\n
If you\u2019re working with a population data set (the entire data set), type =VAR.P(<\/strong> or =VARPA(<\/strong> instead.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-var-insert-formula.png\" "\\"An")
<\/p>\nWith your formula opened, you\u2019ll need to insert your data next. Most users will likely prefer to reference data elsewhere in your current workbook (or in a minimized workbook) using a cell range.<\/p>\n
For instance, =VAR.S(C2:C10)<\/strong> or =VAR.P(C2:C20)<\/strong> completes the formula, using the numerical data in a cell range between cells C2<\/strong> and C10<\/strong> (for the sample set) or C2<\/strong> and C20<\/strong> (for the population set). Make sure to replace these references with your own.<\/p>\n
If you\u2019re working with data that contains numbers, text, and\/or logical values, =VARA(C2:C10)<\/strong> or =VARPA(C2:C20)<\/strong> will work best. Rather than ignoring text or logical values (as VAR, VAR.S, VARP or VAR.P would), the values in a VARA or VARPA formula will count towards your overall result.<\/p>\n
You could also reference each cell individually. For example =VARA(C2:C10)<\/strong> and =VARA(C2,C3,C4,C5,C6,C7,C8,C9,C10)<\/strong> will return the same result.<\/p>\n
If you\u2019re adding numerical values directly to your formula, you\u2019ll need to add the values one by one. Each value needs to be separated using commas. For instance, =VAR.S(1,2,3,4,5,6)<\/strong> or =VAR.P(1,2,3,4,5,6,7,8,9,10)<\/strong> will give you the variance between the numbers 1<\/strong> and 6<\/strong> (or 1<\/strong> and 10<\/strong> for a population set) directly.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-var-insert-numbers-example.png\" "\\"An")
<\/p>\nYou can also do the same with VARA<\/strong> or VARPA<\/strong>. For example, =VARA(1,2,TRUE,3)<\/strong> would work, with the TRUE<\/strong> value counting as its numerical equivalent (1<\/strong>). Likewise, =VARPA(1,2,TRUE,3,4,10,8)<\/strong> would count these values in the same way, with TRUE<\/strong> counting as 1<\/strong>.<\/p>\n
VARA and VARPA support text, but to use these, you\u2019ll need to use a cell reference or cell range. For instance, =VARA(1,2,TRUE,D5,3,4,10,8)<\/strong> or =VARA(D2:D9)<\/strong> would work, where D5<\/strong> contains text that counts as a FALSE<\/strong> value (0<\/strong>). If you try to add a text string directly, however, Excel will return a #VALUE<\/strong> error.<\/p>\n
![\"An](\"\/en\/wp-content\/uploads\/old-blog-uploads\/excel-var-insert-text-example.png\" "\\"An")
<\/p>\nIf you\u2019ve added a reference to a cell range, make sure to close your formula with a closing parenthesis afterwards, then press enter<\/strong> to view the results (or click on another empty cell). For values added directly, place a closing parenthesis after the final value is inserted into your formula.<\/p>\n
Final thoughts<\/h2>\n
By calculating the variance, you can learn a lot about the data you\u2019re working with. This makes the life of a typical data analyst even easier, allowing you to prove theories and hypotheses using a single Excel formula. Variance functions are among the many Excel formulas<\/a> that data analysts use on a regular basis to find results.<\/p>\n
Excel makes mathematical functions like variance and standard deviation easier to handle, especially for beginners. There are also scripts on the internet that make it a little easier, such as this variance calculator<\/a>\u2014but it’s way more rewarding to learn how to calculate variance on your own!<\/p>\n
If you\u2019re new to the field and you want to learn more, try this free, five-day introductory data analytics course<\/a>. And, if you\u2019re keen to get to grips with more Excel formulas, check out the following:<\/p>\n
- \n
- How to convert text to numbers in Excel<\/a><\/li>\n
- How to use the SUMIF function in Excel<\/a><\/li>\n
- How to use the VLOOKUP function in Excel<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
Calculating variance allows you to determine the spread of numbers in a data set against the mean. Learn how to calculate variance in Excel here.<\/p>\n","protected":false},"author":54,"featured_media":1488,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_lmt_disableupdate":"no","_lmt_disable":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-3830","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics"],"acf":{"homepage_category_featured":false},"modified_by":"Kirstie Sequitin","_links":{"self":[{"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/posts\/3830","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/users\/54"}],"replies":[{"embeddable":true,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/comments?post=3830"}],"version-history":[{"count":0,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/posts\/3830\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/media\/1488"}],"wp:attachment":[{"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/media?parent=3830"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/categories?post=3830"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/careerfoundry.inbearbeitung.de\/en\/wp-json\/wp\/v2\/tags?post=3830"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- How to use the SUMIF function in Excel<\/a><\/li>\n
- VAR, VAR.S, VARP and VARP only support numerical<\/strong> values. Other values (text strings, logical values, etc.) are ignored and won\u2019t count towards your result.<\/li>\n
- =VAR.S(value1,<\/strong>value2<\/em><\/strong>, \u2026)<\/strong><\/li>\n
- VARP, VAR.P<\/strong> or VARPA<\/strong> for population variance<\/strong>.<\/li>\n<\/ul>\n
- How do variance functions work in Microsoft Excel?<\/a><\/li>\n