Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a column in a google spreadsheet that's calculated as a function of a few other variables (columns).

Is there a function that I can use to find which of the variables has the most influence over the column? For example, if the value of my column changes with the square of col B but only linearly with col C, the function would return col B.

So given a table like the one on the left, I'd like to find a function that can compute the answer in yellow on the right (assuming that col B is the dominating variable):

enter image description here

share|improve this question
    
Could you please link to a spreadsheet with examples of what you are trying to do. I would have thought the best way to do this would be to look at the formula. –  Tom Horwood Oct 22 '13 at 4:01
    
Yes, looking at the formula would work. However, I'm looking for a way to figure it out automagically –  emmby Oct 22 '13 at 21:52
add comment

1 Answer

I think you'd have to run some test cases. You could write a script to copy the formula to a new sheet, put it in a lot of rows, then fill in the independent cells with a range of values, where there is a constant base, and each column has a row with a different base.

+----+----------+-----+---+---+---+
|    |    A     |  B  | C | D | E |
+----+----------+-----+---+---+---+
|  1 | A        | B   | C | D |   |
|  2 | equation | 1   | 0 | 0 | 0 |
|  3 | equation | 0   | 1 | 0 | 0 |
|  4 | equation | 0   | 0 | 1 | 0 |
|  5 | equation | 0   | 0 | 0 | 1 |
|  6 | equation | 2   | 0 | 0 | 0 |
|  7 | equation | 0   | 2 | 0 | 0 |
|  8 | ...      |     |   |   |   |
|  9 | equation | 100 | 0 | 0 | 0 |
| 10 | ...      |     |   |   |   |
| 11 | equation | 5   | 1 | 1 | 1 |
| 12 | equation | 1   | 5 | 1 | 1 |
| 13 | ...      |     |   |   |   |
+----+----------+-----+---+---+---+

Then compare the impacts for each case. This could be a simple comparison or you may need to create an advanced algorithm. It really depends on the elements and magnitudes you're dealing with. This comparison could be scripted to then fill in column G

share|improve this answer
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.