How to come up with a formula for converting a set of numbers?

Question

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?

Answer 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.