Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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?

share|improve this question
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). –  Amzoti Dec 18 '12 at 0:23
    
You look at the formula in the cell that gives you X2. –  Ross Millikan Dec 18 '12 at 0:24
    
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. –  Ross Millikan Dec 18 '12 at 0:26
    
@Amzoti I do not have the original spreadsheet. –  AngryHacker Dec 18 '12 at 0:36
    
@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. –  AngryHacker Dec 18 '12 at 0:36
show 1 more comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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? –  AngryHacker Dec 18 '12 at 17:31
    
@AngryHacker: Looks like I didn't plot what I thought I did. Straight lines work pretty well. See my update. –  Ross Millikan Dec 18 '12 at 17:52
    
Wow, that's amazingly close. Thank you so much. I'd love to learn this technique. Can you recommend a tutorial somewhere? –  AngryHacker Dec 18 '12 at 18:15
    
@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. –  Ross Millikan Dec 18 '12 at 18:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.