0
$\begingroup$

I have a set of values (W1, X1 and T1). Via some magic, the combination of these numbers result in a number X2.

The full spreadsheet is here, but the gist of the problem looks like this:

W1  X1          T1      X2 (Result)  2   51,900      9       36,400 2   37,600      24      27,400 3   92,800      17      55,500 3   80,200      10      46,300 4   94,800      9       49,000 4   71,900      20      38,700 

I am trying but failing to come up with a formula that where W1, X1 and T1 would produce X2. The result doesn't need to match X2 perfectly, but at least come close to the result.

How would I come up with a formula for deriving X2?

  • 1
    Don't you actually have the original spreadsheet that should have the formulas used? We can perhaps reverse engineer this and it might work in most cases, but may not be unique (i.e., we could have missed something).2012-12-18
  • 1
    You look at the formula in the cell that gives you X2.2012-12-18
  • 0
    There is no magic answer. After all, they could be random numbers. But looking at the whole sheet, the first think I see is that X2/X1 varies much less than either one. I would make a new column with that and see if I could divine some relation with the other two variables.2012-12-18
  • 0
    @Amzoti I do not have the original spreadsheet.2012-12-18
  • 0
    @RossMillikan I put together the spreadsheet off the numbers that were given to me. I am trying to figure out a formula of some type to do this in the future for a different set of numbers.2012-12-18
  • 0
    This is the sort of thing [regression](http://en.wikipedia.org/wiki/Regression_analysis) is good for.2012-12-18

1 Answers 1

1

There is no magic answer. After all, they could be random numbers. But looking at the whole sheet, the first thing I see is that X2/X1 is much less variable than each independently, so I would create a new column with that. There is an outliner on line 9, which I chose to delete-maybe you don't want to. Having done that, I find $$\frac {X2}{X1}= \begin {cases} 0.0022T1+0.6783 & W1=2\\ 0.0023T1+0.5588 & W1=3 \\0.0023T1+0.4919 & W1=4 \end {cases}$$

The constant terms seem almost linear in $W1$ so you can probably do something like $\frac {X2}{X1}=0.0023T1+0.6783 - 0.09(W1-2)$ I just did this by eye from the previous-you might want to be more careful.

  • 0
    Ross, I am not getting the right numbers. Let's take the first row where T1 is 9. Thus the right side of the equation would be 9 to power of 2 * 0.0126 + 0.7134 * 9 + 7.5947 = 15.035. The left side of the equation if X2/X1, thus 36,400 / 51,900 = 0.701. Am I missing something?2012-12-18
  • 0
    @AngryHacker: Looks like I didn't plot what I thought I did. Straight lines work pretty well. See my update.2012-12-18
  • 0
    Wow, that's amazingly close. Thank you so much. I'd love to learn this technique. Can you recommend a tutorial somewhere?2012-12-18
  • 0
    @AngryHacker: I think it is called "exploratory data analysis". Plotting is a powerful tool. I plotted what I said and noted the points were close to a straight line. Then Excel will give you a trendline and the equation for it.2012-12-18