1

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

2
  • 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. 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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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