Excel Correlation Analysis and Spurious Correlations Study Guide

Spurious Correlations and Analytical Principles

• Concept of Coincidental Correlations:

  • Statistical correlation between two variables does not automatically imply a causal relationship.
  • Unrelated datasets can exhibit strong parallel trends over time simply by coincidence ("things just happen").

• Internet Explorer vs. Murder Rate Example:

  • Time Period: $2006$ to $2011$.
  • Variables: Market share of Internet Explorer vs. per capita murder rate.
  • Observed Trend: Both Internet Explorer market share and per capita murder rates declined simultaneously during this timeframe.
  • Analysis: The decline in Internet Explorer market share was driven by general technological progression and changing user preferences toward alternative software, while the per capita murder rate decreased independently. The correlation between these two variables is entirely spurious.

• Margarine Consumption vs. Maine Divorce Rate Example:

  • Geographic Location: State of Maine.
  • Variables: Per capita margarine consumption vs. divorce rate in Maine.
  • Observed Trend: A decrease in per capita margarine consumption coincided with a decrease in the divorce rate.
  • Analysis: Lowering consumption of margarine does not cause a reduction in divorce rates; the occurrences are completely unrelated concurrent events.

Quantitative Analysis Using Excel Correlation Formula

• The Excel Correlation Function:

  • Formula Shortcut: $\text{CORREL}$
  • Primary Purpose: Evaluates and compares two sets of variables to identify the mathematical correlation coefficient between them.

• Formula Structure and Arguments:

  • An Excel formula requires specific input factors designated as arguments.
  • The $\text{CORREL}$ function requires exactly two arguments, corresponding to two distinct datasets or arrays of variables.
  • Syntax: $\text{CORREL}(\text{array1}, \text{array2})$
  • First Argument ($\text{array1}$): Represents the first dataset or current variable.
  • Second Argument ($\text{array2}$): Represents the second dataset or variable to be compared against the first.

• Column Data Selection and Ranges:

  • To execute the comparison, data is selected in parallel column blocks.
  • First Data Column Range ($\text{array1}$): $\text{A1:A15}$ (encompassing $15$ sequential data entries in Column A).
  • Second Data Column Range ($\text{array2}$): $\text{B1:B15}$ (encompassing $15$ sequential data entries in Column B).
  • Function Execution: The function measures the strength of the linear relationship between the data points in $\text{A1:A15}$ and $\text{B1:B15}$.

• Practical Implementation Context:

  • Real-time execution and course dataset materials are accessed via Blackboard.