+ Reply to Thread
Results 1 to 3 of 3

Regression vs. Scatterplot - 2 Stories?

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Regression vs. Scatterplot - 2 Stories?

    I'm trying to test correlation between 2 sets of data and ran both a simple linear regression in Excel and built a scatterplot. Though new to regression, I feel like my 2 outputs tell different stories.

    The regression (attached) has a Adjusted R Square of -0.0025, indicating almost no correlation. However, the scatterplot (also attached) looks like it shows some degree of negative correlation despite outliers. I'm confused why these look different and how to draw my conclusion about the usefulness of comparing these data.

    Any help appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Regression vs. Scatterplot - 2 Stories?

    One of the first things I noticed when I opened your file was that the x axis labels in your scatter chart ranged from 0 to 70 and were not displayed as percents like the values in column B. It took a bit to understand why. When I looked at your ranges for the data series, I notice that you included row 1 in the data series. In an Excel scatter chart, if there is a text string anywhere in the x values range (like the "x" in B1), the chart ignores the x values and simply uses count numbers. The alleged "correlation with outliers" that you are seeing is an artifact of not using the actual x values for the x values. If you will change the series ranges so they start in row 2 rather than row 1, you will see the correct scatter chart.

    (Aside, I have also observed that Excel might be the only spreadsheet that still behaves this way, when I open your sample file in LO Calc, the circled "cluster" disappears because Calc just ignores the text rather than ignoring the numbers).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Re: Regression vs. Scatterplot - 2 Stories?

    Thanks so much, @MrShorty! I didn't catch this and am impressed you did - seems like I'm always getting in trouble for using Excel for data analysis lol. I rebuilt excluding the labels, and it matched my regression much better.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Turning Numbers Into Stories On A Daily Basis :-)
    By Rhonda502 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-16-2020, 12:27 PM
  2. What are your favorite Excel VBA automation success stories?
    By GJL65 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2017, 04:38 PM
  3. Microsoft MVP Stories
    By benishiryo in forum The Water Cooler
    Replies: 37
    Last Post: 11-08-2013, 09:44 AM
  4. using excel to make stories
    By MMUStudent in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2012, 12:57 PM
  5. Workbook Horror Stories
    By Whizbang in forum The Water Cooler
    Replies: 0
    Last Post: 06-06-2011, 11:36 AM
  6. Top funny stories, that you know about casino games
    By bobbyfromby in forum Excel General
    Replies: 0
    Last Post: 08-12-2006, 04:33 PM
  7. Today() Now() and other stories!!!!
    By Cobbcouk in forum Excel General
    Replies: 7
    Last Post: 07-25-2006, 02:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1