Calculate Sample or Population Variance
Excel provides functions to calculate variance and supports add-ins that enable regression analysis. Variance indicates how widely a set of numbers diverges from the average of the numbers. When comparing variance calculations, the higher the variance, the more widely distributed are the numbers in a data set. A variance of 0, for example, indicates that all the numbers in the selected data set are the same. (Standard deviation is the square root of the variance and also measures how to spread out a data set is.) You can run a variance on any set of numbers in Excel. The result displays in the cell. The equation should look something like: =VAR.S($B$2:$B$11) The VAR.S function assumes that the data set is a sample, not the entire population. If you are certain you are working with a complete population data set, you may alternatively use the VAR.P function. That would look like: =VAR.P($B$2:$B$11)
Run a Regression Analysis in Excel on Windows or macOS
Regression analysis helps you understand the relationship between variables. It provides analysis that mathematically identifies if and how one variable affects another in a statistically significant way. To run a regression in Excel, you need two sets of numbers, with one set that serves as the Y variable and the other as the X variable. Most often, these numbers are entered in two adjacent columns. To run regressions on Windows or macOS systems, you need to install the Analysis ToolPak add-in for Excel. The ToolPak works on Excel 2007 or newer on Windows systems and on Excel 2016 or newer on macOS systems. On recent versions of Microsoft Excel on Windows, type add-in in the search box in Excel and press enter. Then select the result with the gear to the left of the words Add-in that displays. (For other versions of Excel on Windows, select File > Options > Add-Ins. Then in the Manage box, choose Excel Add-ins and Go.) Next, select the check box next to Analysis ToolPak, then select OK. On macOS versions of Excel, select Tools > Excel Add-ins. Then select the check box next to Analysis ToolPak, and choose OK. For additional ways to install the Analysis ToolPak, follow Microsoft’s Load the Analysis ToolPak in Excel help page. Once installed, the ToolPak gives you access to data analysis tools. The regression results display in a new sheet.
Run a Regression Analysis in Excel Online
In a browser, including the Safari browser on an iPad, you can do a linear regression in Excel online with the help of an add-on. The regression results display in your Excel sheet, starting at the cell you selected.
