+ Reply to Thread
Results 1 to 5 of 5

Finding X and Y with respect to third variable if having two sets of X and Y

  1. #1
    Registered User
    Join Date
    02-17-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Finding X and Y with respect to third variable if having two sets of X and Y

    My problem

    At temperature 5 degrees the Y=1.00228594 for X=435 and Y=1.000986038 for X=449 and Y=0.999760292 for X=463

    At temperature 7 degrees the Y=1.002094781 for X=435 and Y=1.00079709 for X=449 and Y=0.999573015 for X =463

    For a new temperature of 9.6 degrees and at 16 degrees how to find the Y values for different X values

    What I did

    I find a polynomial eqn for the first set of X and Y values , then I find a polynomial eqn for the second set of X and Y values in excel. How can i find a polynomial for a different temp in excel
    Attached Files Attached Files

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

    Re: Finding X and Y with respect to third variable if having two sets of X and Y

    I'm going to start by saying, I doubt I (or anyone else here) can completely answer your question solely based on the information given. A few thoughts towards getting to a solution. I have to also say that, as an guy who works in a laboratory, I want to see this as experimental data, so a lot of my response is colored by that assumption. Basically I see two possible approaches that I would consider:

    1) Assume Y is a linear function of temperature and extrapolate accordingly. As far as programming this in Excel, it is probably easiest to use the =TREND() function on the given data using 5 and 7 as known x to find y(9.6) and y(16) For example, you could use =trend({1.00228594,1.002094781},{5,7},9.6) to find y(435,9.6). You can substitute references to cells containing the raw data if desired. Then, once you have the new set of y(9.6) and y(16), you can regress an equation to represent these new temperatures. In using this approach, we are assuming that the data at 5 and 7 are exact and that the trend in the data as a function of temperature is also exact, and that this trend is "constant" all the way out to 16 degrees. Depending on the nature and source of the data, that might be a lot to expect out of the data.

    2) Assume y is not dependent on temperature, and use some kind of average of the two data sets to represent all temperatures. I notice that the difference between 5 and 7 is about 0.02%. If this is some kind of experimental data, then it would have to be quite accurate in order to state that this difference is statistically significant. Of course, this is easy to do using the =average() function.

    I would note that the difference between the two approaches is about 0.1%, so, in the end, it might not really matter how you do it.

    I'm sure there are other ways to approach this problem, too. Basically, it comes down to whether or not you believe y should be dependent on temperature or not. If you do, and you believe that a linear trend with temperature is reasonable, then approach 1 may be best. If you believe y should be independent of temperature, then the 2nd approach may be more suitable. Or, if the error in the raw data is large enough, maybe it doesn't really matter because the data aren't accurate enough to tell us whether or not y is a function of temperature.

  3. #3
    Registered User
    Join Date
    02-17-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Finding X and Y with respect to third variable if having two sets of X and Y

    Thank You.
    You are right about the guess of experimental data. Solution 1 is probably the best one as these are experimental datas. As long as the lines are parallel to each other i think the trend method works fine. If the line are not parallel then what would be the solution?

    Thanks again.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,593

    Re: Finding X and Y with respect to third variable if having two sets of X and Y

    Solution 1 is probably the best one as these are experimental datas.
    I'll take your word for it. As I said, it seems to me that the uncertainty in y would have to be pretty small to say with any statistical certainty that there is a real trend with temperature here. But you know the data better than I.

    If the line are not parallel then what would be the solution?
    As with my previous response, I'm not sure any of us could say with any sense of certainty, because we don't know anything about the data. In the end, with only 2 Y's per X, I'm not sure there would be a different approach that could be justified, unless you knew (based on your knowledge of the physical, real world process being modeled) that the process should follow an exponential or logarithmic or other trend with temperature.

    What non-parallel curves suggest is that dy/dT is a function of x, and not independent of x. Is that a reasonable expectation?

  5. #5
    Registered User
    Join Date
    02-17-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Solved: Finding X and Y with respect to third variable if having two sets of X and Y

    solved file is attached

    Thanks again
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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