+ Reply to Thread
Results 1 to 6 of 6

non-linear result of regression data analysis

  1. #1
    Registered User
    Join Date
    06-21-2017
    Location
    Munich, Germany
    MS-Off Ver
    MS 2013
    Posts
    3

    non-linear result of regression data analysis

    Hi there,

    I have two sets of data:
    1. Concentration of a nutrient in water
    2. Cell densities of an organism taking up this nutrient

    The two sets are inversely related as cell densities increase with reduced nutrients. My goal is to define the relationship between these sets with a regression analysis. When I use the built in Excel regression data analysis tool, the result is a fairly well fitted model (R^2 = 0.89). Now, the provided residual plot gives an estimated model which is far from linear. This is good, but I don't understand how Excel calculates a higher-order plot with just two coefficients (intercept and slope)? Help is very much appreciated!

    Thanks folks!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,443

    Re: non-linear result of regression data analysis

    I am not sure I am statistically literate enough to comment, as I am not sure what you are trying to describe here (An example data set with regression output might be helpful). Or perhaps you are using terms differently than I tend to use them, I am not sure.

    When I run a regression on a random set of data, I get a "linear model" consisting of slope and intercept (y=mx+b). I do not expect the residuals (differences between the predicted y's and the measured y's) to be linear. Especially for random data, I expect the residuals to be randomly distributed above and below 0, which is what I get.

    What do you mean by "the residual plot gives and estimated model which is far from linear"? Where are you seeing "Excel calculate a higher-order plot with just two coefficients"?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: non-linear result of regression data analysis

    I think it is sometimes easier to fit the lines on a chart to start with. If you are saying the residuals are not random, ans so you want to introduce other factors, they polynomial regression

    http://www.statisticshowto.com/excel...le-regression/

  4. #4
    Registered User
    Join Date
    06-21-2017
    Location
    Munich, Germany
    MS-Off Ver
    MS 2013
    Posts
    3

    Re: non-linear result of regression data analysis

    @MrShorty
    Thanks for your reply. I'm sorry for my unclear description. I attched an Excel sheet with an example of my data and the output from the regression data analysis tool (I use a german version, but the layout should be the same as in the english version).
    My question is now, how Excel generates the estimated model data (marked in yellow) in the 'residuals' section. I plotted the estimated data and it is a pretty good fit for my measured data. However, I have no idea, how excel calculates it with only two coefficients (red).

    I hope it is a little bit better to understand now.

    Thanks a lot for your advice!
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,443

    Re: non-linear result of regression data analysis

    Short answer: The chart and the regression appear to be using different x values, so the chart does not actually match the regression.

    longer answer:
    how Excel generates the estimated model data (marked in yellow) in the 'residuals' section.
    The values in G30:G37 are calculated by y=mx+b. m is the slope of the line in H22, b is the y intercept in H21, and x are the values in C6:C14.

    The chart data is linked to another spreadsheet on your computer, so I cannot look to see exactly what the chart is using. It appears to be using the values in F29:F37 for the x values (not the same x values used in the regression or for calculating estimated cell density) and the values in G29:G27 for the "estimated" series Y values and D6:D14 for the measured cell densities. I am not sure why your regression tool did this, because when I run the regression tool, it uses C6:C14 as the x values for the chart.

  6. #6
    Registered User
    Join Date
    06-21-2017
    Location
    Munich, Germany
    MS-Off Ver
    MS 2013
    Posts
    3

    Re: non-linear result of regression data analysis

    Ah, now I get it! I mixed up the x-reference. Of course, when I use the concentration as x-axis, there is a linear relationship. Thank you so much!

+ 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. Macro for incorporating linear regression analysis
    By marcheese in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2014, 01:37 PM
  2. Linear Regression Analysis
    By HDLLC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2013, 01:30 PM
  3. non-linear regression: fitting data to a sigmoidal (psychophysical) curve
    By chiasmata in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 07-17-2013, 11:47 AM
  4. Replies: 3
    Last Post: 03-06-2011, 01:38 PM
  5. Creating linear regression using partial data
    By reinhardn in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-05-2007, 07:30 PM
  6. Recalculating a Regression Output in Data Analysis
    By karin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  7. Recalculating a Regression Output in Data Analysis
    By karin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM

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