Evan Treborn, played by Ashton Kutcher in the 2004 film The Butterfly Effect, is burdened with horrible headaches that cause him to black out and time travel to the past. His time travel leads him to explore difficult moments from his past and identify how the reality he lives in is shaped by the past. While Treborn is analyzing qualitative data to understand the relationship between one variable from his life and another, statisticians have a much more structured approach and the benefit of using quantitative data to further research how one variable is related to another. 

Introducing (even we admit this was a non-traditional introduction) our Scatter Plot Diagram for Excel, built to automate your Scatter Plot charting and calculations. 

If you find this template useful, you’ll also want to check out our other data analysis templates such as Box and Whisker Plot, Stratification Data and Panel Analysis. With these tools at your disposal, you will quickly reduce the time it takes to crunch numbers and more time analyzing the real world impact of what your dataset represents. Our library includes each of the Seven Basic Tools of Quality.

Let’s dive into how to use this template. 

Start in the Scatter Diagram sheet and input your independent variable in the horizontal x-axis input and the dependent variable in the vertical y-axis. Put a symbol where the x-axis value intersects with the y-axis value. 

If your data forms a line or a curve, you can draw the conclusion that there is a relationship between the two variables. You may want to use a regression or correlation analysis for further understanding of the relationship if those two traits are present. 

Divide points on the graph into four quadrants. If there are X points on the graph, count X/2 points from top to bottom and draw a horizontal line. Count X/2 points from left to right and draw a vertical line. Count the points in each quadrant. Do not count points on the line. 

Add the diagonally opposite quadrants. Find the smaller sum and the total points in all quadrants. 

Move to the Test Table sheet and look up the limit for N on the trend test table. If Q is less than the limit, the two variables are related. If Q is greater than or equal to the limit, the pattern could have occurred from random chance. 

And just like that, you are the mathematician Ashton Kutcher...

More Data Analysis Templates For You