1. #1

    Two Variable Data Table - Regression

    I have tried checking excel forums and excel help documents, but have not found what I need.

    I have a two variable data table in excel and need to find the equation. All documents I have seen are only for knowing the equation and completing the table.

    Any help would be greatly appreciated.

    Thanks!

  2. #2
    Stood in the Fire Chaltione's Avatar
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    395
    I guess what you mean is that you have two columns (assuming data is in columns) and you want to find the formula that translates column A into column B?

    If this is the case, you can make a line graph and add a trend line (you have to guess which approximation based on the line) and then it should have the option of showing the equation.

  3. #3
    What Chaltione said. Scatter graph, trendline, display trendline equaton from options.


  4. #4
    Quote Originally Posted by Chaltione View Post
    I guess what you mean is that you have two columns (assuming data is in columns) and you want to find the formula that translates column A into column B?

    If this is the case, you can make a line graph and add a trend line (you have to guess which approximation based on the line) and then it should have the option of showing the equation.

    No, I have two variables x and y and need to calculate z.

  5. #5
    Stood in the Fire Chaltione's Avatar
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    395
    In that case, lock one variable (assuming you have enough data points or can measure freely) and regress across the other variable as if the first is a constant.

    example:
    x y z
    1 3 10
    2 5 12
    3 6 20
    1 2 8
    4 2 11
    1 4 9

    you pick the values where x=1 and regress for y.

  6. #6
    Quote Originally Posted by Chaltione View Post
    In that case, lock one variable (assuming you have enough data points or can measure freely) and regress across the other variable as if the first is a constant.

    example:
    x y z
    1 3 10
    2 5 12
    3 6 20
    1 2 8
    4 2 11
    1 4 9

    you pick the values where x=1 and regress for y.
    That equation will only be valid when x=1, whereas I need an equation where both x and y can vary.

  7. #7
    So you mean you need to find the equation of the plane z=f(x,y) and you have a set of points on the plane [(x1,y1,z1),(x2,y2,z2),......]?

  8. #8
    Quote Originally Posted by ccckkk View Post
    So you mean you need to find the equation of the plane z=f(x,y) and you have a set of points on the plane [(x1,y1,z1),(x2,y2,z2),......]?
    Yes, exactly

  9. #9
    And do you know if it's a flat plane or some curved surface or unknown?

  10. #10
    Quote Originally Posted by Discobeard View Post
    Yes, exactly
    It is unknown

    ---------- Post added 2012-05-03 at 01:45 PM ----------

    Quote Originally Posted by ccckkk View Post
    And do you know if it's a flat plane or some curved surface or unknown?
    Unknown, but from looking at the data it appears to be a curved surface

  11. #11
    I'm not sure how much help I can be (and may be away from pc shortly), anyway...

    Do you know if the curved plane has some functional form that is just as yet unknown to you, or is it just some random points on a curved surface (so a functional form potentially does not exist)?

  12. #12
    Not to necro this thread, but I did figure it out. There is an excel add-on you can enable that allows multivariable regressions with a little know-how. In my case the data was 2nd order polynomial for one variable and 3rd order polynomial for the other with a decent R2 value.

    It turns out there is a ton of info available on the web for "multivariable" regression whereas my searches were too specific in that I searched for "two variable" regression and there were no articles or discussions that linked the two.

    Thank you all for your help!

Posting Permissions

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