How do you calculate variance in Excel?

A:

Variance is a measurement of the spread between numbers in a data set. The variance measures how far each number in the set is from the mean.

Using a data set chart, we can observe what the linear relationship of the various data points, or numbers, is. We do this by drawing a !)  

When we calculate a variance, we are asking, "Given the relationship of all these data points, how much distance do we expect on the next data point?  This "distance" is called the error term, and it's what variance is measuring.  

By itself, variance is not often useful because it does not have a unit, which makes it hard to measure and compare.  However, the square root of variance is the standard deviation, and that is both practical as a measurement.  

Calculating Variance in Excel

Calculating variance in Excel is easy if you have the data set already entered into the software. In the example below, we will calculate the variance of the last 20 days of daily returns in the highly popular exchange-traded fund (ETF) named SPY, which invests in the S&P 500.

  1. The formula is =VAR.S(select data)

The reason you want to use VAR.S and not VAR.P (which is another formula offered) is that often you don't have the entire population of data to measure.  For example, if we had all returns in history of the SPY ETF in our table, we could use the population measurement VAR.P, but since we are only measuring the last 20 days to illustrate the concept, we will use VAR.S.  

 

As you can see, the calculated variance value of .000018674 tells us little about the data set, by itself.  If we went on to square root that value to get the standard deviation of returns, that would be more useful.  

New Articles:

searscredit cards irs installment agreement payment calculator td canada trust foreign exchange rates yellow cab taxi bayonne nj sear financial mastercard salaried employee vs hourly exchange rate from usd to rmb chase credit card fee navy federal no down payment mortgage how much is 100 dollars in euros compute payroll taxes fantasy trade simulator simple interest and compound interest formula wikipedia what does salaried non exempt mean united arab emirates emblem learn trading stocks online euro american dollar exchange us citizens need visa for brazil green dot moneypak walmart changing euros to pounds hrblock estimator how much does mcdonalds make annually steaz energy drinks can you get money back from moneygram navy federal operating hours conversion of jpy to usd aed in dollars how much is 1922 silver dollar worth bsba major in management accounting welch t test calculator latest news on iraq dinar chase rewards credit card
RELATED FAQS
  1. What is the difference between standard deviation and variance?

    Understand the difference between standard deviation and variance; learn how each is calculated and how these concepts are ... Read Answer >>
  2. Is variance good or bad for stock investors?

    Learn how high variance stocks are good for some investors and how diversified portfolios can reduce variance without compromising ... Read Answer >>
  3. What is price variance in cost accounting?

    Understand what price variance is in relation to cost accounting. Learn the most common way price variance arises and how ... Read Answer >>
  4. What types of assets lower portfolio variance?

    Learn what type of assets reduce portfolio variance and how modern portfolio theory uses correlation coefficients. Read Answer >>
  5. How is standard deviation used to determine risk?

    Understand the basics of calculation and interpretation of standard deviation, and how it is used to measure risk in the ... Read Answer >>
Related Articles
  1. Trading

    Exploring the Exponentially Weighted Moving Average

    Learn how to calculate a metric that improves on simple variance.
  2. Investing

    Computing Historical Volatility in Excel

    We examine how annualized historical volatility is computed from daily log returns, variance and standard deviation.
  3. Trading

    How To Convert Value At Risk To Different Time Periods

    Volatility is not the only way to measure risk. Learn about the "new science of risk management".
  4. Investing

    An Introduction to Value at Risk (VAR)

    Volatility is not the only way to measure risk. Learn about the "new science of risk management".
  5. Trading

    Improve Your Investing With Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  6. Trading

    The Linear Regression Of Time and Price

    This investment strategy can help investors be successful by identifying price trends while eliminating human bias.
  7. Personal Finance

    Backtesting Value-at-Risk (VaR): The Basics

    Learn how to test your VaR model for accuracy.
  8. Trading

    Trading With Gaussian Models Of Statistics

    The entire study of statistics originated from Gauss and allowed us to understand markets, prices and probabilities, among other applications.
RELATED TERMS
  1. Budget Variance

    A periodic measure used by governments, corporations or individuals ...
  2. Standard Deviation

    A measure of the dispersion of a set of data from its mean, calculated ...
  3. Mean-Variance Analysis

    The process of weighing risk against expected return. Mean variance ...
  4. Sales Price Variance

    The difference between the amount of money a business expects ...
  5. Efficiency Variance

    Efficiency variance is the difference between the theoretical ...
  6. Analysis Of Variance - ANOVA

    Analysis of variance (ANOVA) is a statistical analysis tool that ...

New Articles:

searscredit cards irs installment agreement payment calculator td canada trust foreign exchange rates yellow cab taxi bayonne nj sear financial mastercard salaried employee vs hourly exchange rate from usd to rmb chase credit card fee navy federal no down payment mortgage how much is 100 dollars in euros compute payroll taxes fantasy trade simulator simple interest and compound interest formula wikipedia what does salaried non exempt mean united arab emirates emblem learn trading stocks online euro american dollar exchange us citizens need visa for brazil green dot moneypak walmart changing euros to pounds hrblock estimator how much does mcdonalds make annually steaz energy drinks can you get money back from moneygram navy federal operating hours conversion of jpy to usd aed in dollars how much is 1922 silver dollar worth bsba major in management accounting welch t test calculator latest news on iraq dinar chase rewards credit card
Hot Definitions
  1. Moving Average - MA

    A moving average (MA) is a widely used indicator in technical analysis that helps smooth out price action by filtering out ...
  2. Stop Order

    A stop order is an order to buy or sell a security when its price increases past a particular point in order to limit losses ...
  3. Inflation

    The rate at which the general level of prices for goods and services is rising and, consequently, the purchasing power of ...
  4. Candlestick

    A chart that displays the high, low, opening and closing prices for a security for a single day. The wide part of the candlestick ...
  5. Indicator

    Indicators are statistics used to measure current conditions as well as to forecast financial or economic trends.
  6. Liability

    Liabilities are defined as a company's legal debts or obligations that arise during the course of business operations.
Trading Center