jeffreyweir
Active Member
Formula Challenge 010 - Two-way interpolation.
A mad scientist has concocted a secret formula that makes people better at Excel. The formula has two components: x and y.
He’s already done some experiments using his children as test subject, whereby he’s given them various dosages of x (across the top) and y (down the left), and measured the result (z).
He now wants to estimate what the likely z is given some other combinations of x and y, by doing a 2 way linear interpolation on his experimental results. (Due to some unfortunate fatalities, he’s run out of kids to use as guinea pigs).
Your challenge: Put a formula in this range that uses the numbers in the column to the left and row across the top to do a two way interpolation based on the values observed during the initial experiment (see the first table above).
* You should not use any additional named ranges or helper cells, although you may use these named ranges, which have already been set up in the sample workbook:
t - top left cell that is diagonal to the data/above the y values/to the right of the x values.
z - data
x - values across the top
y - values down the side
* You should not use any VBA.
* Your results should match the table below:
My formula is 342 characters long.
Challenge workbook at https://www.dropbox.com/s/tmy6sx8882s4l1w/Challenge 10_2 way interpolation.xlsb
A mad scientist has concocted a secret formula that makes people better at Excel. The formula has two components: x and y.
He’s already done some experiments using his children as test subject, whereby he’s given them various dosages of x (across the top) and y (down the left), and measured the result (z).
Code:
Dosage 2 5 8
2 7.22 6.95 7.18
4 6.06 6.20 6.61
9 4.10 4.86 5.62
He now wants to estimate what the likely z is given some other combinations of x and y, by doing a 2 way linear interpolation on his experimental results. (Due to some unfortunate fatalities, he’s run out of kids to use as guinea pigs).
Code:
Dosage 3 3.5 4 4.5 6 7
2.5
2.75
3.25
4
5
7.7
8
* You should not use any additional named ranges or helper cells, although you may use these named ranges, which have already been set up in the sample workbook:
t - top left cell that is diagonal to the data/above the y values/to the right of the x values.
z - data
x - values across the top
y - values down the side
* You should not use any VBA.
* Your results should match the table below:
Code:
Dosage 3.00 3.50 4.00 4.50 6.00 7.00
2.50 6.88 6.85 6.82 6.79 6.85 6.95
2.75 6.75 6.73 6.71 6.69 6.77 6.87
3.25 6.49 6.49 6.49 6.48 6.60 6.71
4.00 6.11 6.13 6.15 6.17 6.34 6.48
5.00 5.76 5.80 5.84 5.89 6.09 6.25
7.70 4.81 4.91 5.01 5.11 5.43 5.66
8.00 4.71 4.81 4.92 5.02 5.36 5.59
My formula is 342 characters long.
Challenge workbook at https://www.dropbox.com/s/tmy6sx8882s4l1w/Challenge 10_2 way interpolation.xlsb
Last edited: