Calculate variance in Excel

Contents
  1. Using Excel to perform regression analysis
  2. Calculate the sample or population variance
  3. Run regression analysis in Excel on Windows or MacOS
  4. Run regression analysis in Excel Online

Using Excel to perform regression analysis

Data analysis > Linear regression window open, ready to accept choices”
expand data = “300”

Excel provides functions for calculating variance and also supports add-ins that allow you to perform regression analysis.

The variance shows how much a set of numbers deviates from the mean. When comparing variance calculations, the higher the variance, the more widely distributed the numbers are in the data set. For example, a variance of 0 indicates that all numbers in the selected data set are the same. (Standard deviation is the square root of the variance and is also a measure of how spread out a set of data is.) You can perform variance on any set of numbers in Excel.

Regression analysis helps to understand the relationship between variables. It provides an analysis that mathematically determines whether and how one variable affects another in a statistically significant way. To perform a regression in Excel, you need two sets of numbers, one for the variable Y and one for the variable X. Usually, these numbers are entered in two adjacent columns.

The following variance functions work in Excel 2019, Excel 2016, and Excel 2010 on Windows; Excel 2016 and Excel 2011 on macOS; and Microsoft Excel on Android and iOS, as well as Office 365.

Calculate the sample or population variance

  1. In the cell where you want to calculate the variance, type: = VAR.S(

    The VAR.S function assumes that the data set is a sample and not the entire population.

  2. Then enter a range of cells, such as B2:B11. (If you want, you can click or tap to select a range of cells.)

  3. Then fill in:

The result is displayed in the cell. In the cell, the equation should look something like this: =VAR.S($B$2:$B$11)

If you are sure that you are working with a complete population data set, you can use the VAR.P function. It will look like this: =VAR.P($B$2:$B$11)

Run regression analysis in Excel on Windows or MacOS

To perform regressions on Windows or macOS systems, you must install the Analysis ToolPak add-in for Excel. The ToolPak works with Excel 2007 or later on Windows systems and Excel 2016 or later on MacOS systems.

In the latest versions of Microsoft Excel for Windows, type “add-in” into the search box in Excel, press Enter, and select the result with the gear wheel to the left of the word “Add-in” that appears. (For other versions of Excel on Windows, select File > Options > Add-ins. In the Manage box, select Excel Add-ins and start.) Then, check the box next to Analysis Toolkit, and then click OK.

For macOS versions of Excel, select Tools > Excel Add-ins. Then check the “Analysis Toolkit” box and click OK.

For more ways to install the analysis tools package, see the Microsoft help page “Download the analysis tools package in Excel”. Once installed, ToolPak gives you access to data analysis tools.

  1. Select tab Facts find the analysis area and select Data analysis

  2. Select regression from the list, then select Okay

    regression choice”
    class=”LazyLoad”
    data-click-tracked=”true” data-img-lightbox=”true” data-expand=”300″ id=”mntl-sc-block-image_1-0-44″ data-tracking-container=”true”

  3. In the Y Range field, type (or select) the range of cells that contain the variables Y. For example, this could be $B$2:$B$10.

  4. In the X Range Input field, type (or select) the range of cells containing the variables X. For example, it could be $A$2:$A$10.

  5. If desired, select the Labels check box or make other changes to the displayed regression calculation options.

  6. In the Output Options section, select the output location. More often than not, you’ll want to select the New Leaf Layer button and leave the box blank.

  7. Select Okay

  8. Your regression results appear in a new worksheet.

Run regression analysis in Excel Online

In a browser, including the Safari browser on iPad, you can run Linear Regression in Excel online using an add-on.

  1. Open the spreadsheet with your data in your browser.

  2. Select Insert Office Add-ins

  3. In the search box that opens, type “ XLMiner Analysis ToolPak ” and press the enter key.

  4. Select To add † Several possible data tools should appear on the right side of the screen.

  5. Select Linear Regression

  6. In the Y Range field, type (or select) the range of cells that contain the variables Y. For example, this could be $B$2:$B$10.

  7. In the X Range Input field, type (or select) the range of cells containing the variables X. For example, it could be $A$2:$A$10.

  8. If desired, select the Labels check box or make other changes to the displayed regression calculation options.

  9. In the Output Range box, select a cell far enough to the right or bottom of the worksheet to make sure it’s out of range from all the other data in the Excel document. For example, if all your data is in columns A through C, you can enter $F$1 in the output range field.

  10. Select Okay

  11. The results of your regression are displayed on your Excel sheet from the selected cell.

Leave a Reply

Your email address will not be published.