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?

  • 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
    @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