A:

Correlation measures the degree to which two variables move in relation to each other. High positive correlations mean that the two variables tend to increase together at the same time, while high negative correlations show that they move apart at the same time. There are several different kinds of correlation calculations in Microsoft Excel, but the most common is the CORREL function.

The CORREL function returns the correlation coefficient for two sets of data. Technically speaking, it actually returns the Pearson product-moment correlation coefficient. The range of possible values for CORREL rests between +1.0 (perfectly positively correlated) and -1.0 (perfectly negatively correlated). A return value of zero indicates that no correlation exists in the data.

Using CORREL in Excel

The Data Analysis Toolpak makes it simpler and faster to execute ratios and correlations in Excel. Those without the Toolpak use the standard CORREL function:

= CORREL (array 1, array 2)

There may be some rounding errors in pre-2003 versions of Excel, but those errors have been fixed in later editions.

The first step in calculating correlation is to have two data arrays. Arrays are sets of numeric values that can be assigned to one variable. For example, suppose someone wanted to find the correlation between height and weight among 30 students. Each measured height could become a value for Variable X and each corresponding weight could be a value for Variable Y.

In Excel, all Variable X values could be listed in Column B, descending from Row 2 to Row 31. All Variable Y values could be listed in Column C from Row 2 to Row 31. Every row would contain the height and weight of one student. In a separate cell, the following formula would be entered:

= CORREL (B2:B31, C2:C31)

This returns the Pearson correlation between the two data arrays. Excel only returns the statistical value of the correlation; it cannot interpret its significance or meaning. This is true for all financial operations in Excel.

RELATED FAQS
  1. How do I calculate correlation between market indicators and specific stocks?

    Discover how to calculate the correlation coefficient between market indicators and stock prices, a critical skill in technical ... Read Answer >>
  2. How do I find positive correlation in the stock market?

    Learn how positive correlation is found in the stock market, how correlation is calculated and how positive correlation is ... Read Answer >>
  3. Does a negative correlation between two stocks mean anything?

    Learn what the concept of negative correlation means, understand how it is generally calculated and see how it is used in ... Read Answer >>
  4. How does correlation affect the stock market?

    Learn about the role correlation plays in prudent stock market investing, and how the correlation coefficient is used to ... Read Answer >>
  5. What is the difference between a copay and a deductible?

    Learn how the correlation coefficient may be used to predict the relationship between the returns of two stocks, but also ... Read Answer >>
  6. How do fund managers use correlation to create portfolio diversity?

    Read about how contemporary investment fund managers use the concept of correlation to add diversification among assets in ... Read Answer >>
Related Articles
  1. Investing

    Correlation

    In the world of finance, correlation is a statistical measure of how two securities move in relation to each other.
  2. Financial Advisor

    4 Reasons Why Market Correlation Matters

    Learn about how correlation can be used to measure how broader markets move in relation to each other. See how correlation is used to manage risk.
  3. Investing

    Understanding the Oil & Gas Price Correlation

    Learn how the correlation between the commodity prices for natural gas and oil changed from 2004 to 2015 due to increased natural gas production.
  4. Investing

    Is the Stock Correlation Strategy Effective?

    The synchronized movement among stocks and markets in recent years is challenging diversification.
  5. Investing

    What's the Correlation Coefficient?

    The correlation coefficient is a measure of how closely two variables move in relation to one another. If one variable goes up by a certain amount, the correlation coefficient indicates which ...
  6. Trading

    Using Currency Correlations To Your Advantage

    Knowing the relationships between pairs can help control risk exposure and maximize profits.
  7. Investing

    Explaining Autocorrelation

    Autocorrelation is the measure of an internal correlation with a given time series.
  8. Trading

    Managing Currency Exposure In Your Portfolio

    The value of your investments is impacted by changes in global currency exchange rates. Find out how.
  9. Investing

    The Market Is Assessing Trump Implications

    Stocks are no longer moving in unison, and active fund managers are cheering.
  10. Investing

    Regression Basics For Business Analysis

    This tool is easy to use and can provide valuable information on financial analysis and forecasting. Find out how.
RELATED TERMS
  1. Correlation Coefficient

    A measure that determines the degree to which two variable's ...
  2. Positive Correlation

    A relationship between two variables in which both variables ...
  3. Benchmark For Correlation Values

    A benchmark or point of reference chosen by an investment fund ...
  4. Inverse Correlation

    A contrary relationship between two variables such that they ...
  5. Negative Correlation

    In statistics, a perfect negative correlation is a relationship ...
  6. Correlation

    In the world of finance, a statistical measure of how two securities ...
Hot Definitions
  1. Financial Statements

    Records that outline the financial activities of a business, an individual or any other entity. Financial statements are ...
  2. Operating Cost

    Expenses associated with the maintenance and administration of a business on a day-to-day basis.
  3. Money Market

    A segment of the financial market in which financial instruments with high liquidity and very short maturities are traded. ...
  4. Block (Bitcoin Block)

    Blocks are files where data pertaining to the Bitcoin network is permanently recorded.
  5. Fintech

    Fintech is a portmanteau of financial technology that describes an emerging financial services sector in the 21st century.
  6. Ex-Dividend

    A classification of trading shares when a declared dividend belongs to the seller rather than the buyer. A stock will be ...
Trading Center