• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 010 - Two-way interpolation.

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

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
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:

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:
Good day Jeff


A mad scientist / concocted / experiments / children as test subject, Jeff you need to see someone with medical and counselling skills…Fast
 
Note that we're looking for a proportional aka linear interpolation here. So if the experiment yielded a result of 7.22 for (x,y) values of (2,2) and 6.6 for (x,y) values of (2,4), then doing an interpolation on (x,y) values of (2,2.25) should give a result closer to 7.22 than 6.6, given that 2.25 is closer to the y value of 2 than 4.


And note that we're not
talking about a regression on all of x against all of y, because the relationship between x and y isn't linear/constant.
 
@Jeffrey

Based on your last comment, can we assume that all coordinates in 2nd table are bounded by the range in initial data? I.e., no coordinates of 10,10 will be used?
 
Formula in D25 which can be copied down and across:

=FORECAST(D$24,OFFSET(t,MATCH($C25,y),MATCH(D$24,x),,2),OFFSET(t,,MATCH(D$24,x),,2))-((FORECAST(D$24,OFFSET(t,MATCH($C25,y),MATCH(D$24,x),,2),OFFSET(t,,MATCH(D$24,x),,2))-FORECAST(D$24,OFFSET(t,MATCH($C25,y)+1,MATCH(D$24,x),,2),OFFSET(t,,MATCH(D$24,x),,2)))*($C25-INDEX(y,MATCH($C25,y)))/(INDEX(y,MATCH($C25,y)+1)-INDEX(y,MATCH($C25,y))))


Can be reduced if there's a way to convert 2 forecast results into a numerical array like below [bold part] then forecast concept will work and formula will reduce considerably.

=FORECAST($C26,(FORECAST(D$24,OFFSET(t,MATCH($C26,y),MATCH(D$24,x),,2),OFFSET(t,,MATCH(D$24,x),,2)),FORECAST(D$24,OFFSET(t,MATCH($C26,y)+1,MATCH(D$24,x),,2),OFFSET(t,,MATCH(D$24,x),,2))),OFFSET(t,MATCH($C26,y),,2,))
 
Brilliant stuff! How long did it take you? Took me hours and hours and hours to come up with this one, which is 4 characters longer than yours and must be array entered:

=TREND(INDEX(OFFSET(t,MATCH($C25,y),MATCH(D$24,x),2,2),1,),OFFSET(t,0,MATCH(D$24,x),,2),D$24)+(TREND(INDEX(OFFSET(t,MATCH($C25,y),MATCH(D$24,x),2,2),2,),OFFSET(t,0,MATCH(D$24,x),,2),D$24)-TREND(INDEX(OFFSET(t,MATCH($C25,y),MATCH(D$24,x),2,2),1,),OFFSET(t,0,MATCH(D$24,x),,2),D$24))*($C25-LOOKUP($C25,y))/SUM(OFFSET(t,MATCH($C25,y),,2)*{-1;1})
 
This can go shorter still. You can replace this bit at the end:

(INDEX(y,MATCH($C58,y)+1)-INDEX(y,MATCH($C58,y)))


...with this:

SUM(OFFSET(t,MATCH($C58,y),,2)*{-1;1})


...which takes it down to 327 characters of array-entered genius!

=FORECAST(D$57,OFFSET(t,MATCH($C58,y),MATCH(D$57,x),,2),OFFSET(t,,MATCH(D$57,x),,2))-((FORECAST(D$57,OFFSET(t,MATCH($C58,y),MATCH(D$57,x),,2),OFFSET(t,,MATCH(D$57,x),,2))-FORECAST(D$57,OFFSET(t,MATCH($C58,y)+1,MATCH(D$57,x),,2),OFFSET(t,,MATCH(D$57,x),,2)))*($C58-INDEX(y,MATCH($C58,y)))/SUM(OFFSET(t,MATCH($C58,y),,2)*{-1;1}))
 
@jeff


Is there any way I can stop seeing any challenges set by you, other than gouging my eyes out. I am always intrigued by the question and of course the answers. But I am truly in a mess when I go to bed dreaming of how I would even try to figure this out.
 
Ha! I was in your position just a few years ago. THere's STILL posts on Daily Dose of Excel that I avoid, for much the same reason ;-)
 
Thanks for the input.


Yesterday it was raining Cats and Dogs here in Mumbai. I had nothing much to do so worked on it.


It took me 2 continuous hours to work it out. I posted it because it was working though I was not entirely happy with it. I'd have been happier if the 2nd approach would have worked but it somehow didn't. I will most probably add some more hours to it to make it work the 2nd way.


Maybe bromide but the challenges are good as they force you to try something different.


I had used FORECAST formula few times so it was bit easy but I have not used OFFSET formula a lot in calculations. In fact usage in this formula exceeds the total cumulative use of OFFSET by me in the past ;). It is versatile formula and I am beginning to like it.


It was the other challenge [prime] which has been so far little elusive to me. What I have posted also generates grid but it doesn't apply to small numbers testing.
 
I posted 2 solutions at

http://chandoo.org/forums/topic/plotting-cost-from-a-table-between-known-points
 
Here's another approach. I was interested to see if I use linear regression dynamically on the two x values, two y values, and four resulting z values to answer this challenge.


It's tricky: Given say an x value of 3 and a y value of 8, then the required layout that LINEST needs in order to do the regression is:

=LINEST(known_y's,[known_x's],[const],[stats])

=LINEST({6.06;4.11;6.20;4.86},{2,4;2,9;5,4;5,9})


...with that data really needing to be laid out like so:

Code:
x    y    z
2    4    6.06
2    9    4.10
5    4    6.20
5    9    4.86
But our data simply isn't laid out like that. Our X and Y arrays are natively 1*2 and 2*1 arrays, and our z array is a 2*2 array. So we have to do some very tricky array manipulations in order to turn our 1*2 arrays for X and Y into a combined 2*4 array. And we also need to change our 2*2 array of z into a 1*4 array.


But I love a challenge:

=LINEST(N(OFFSET(OFFSET(t,MATCH($C32,y),MATCH(D$25,x),2,2),MOD(ROW(OFFSET($A$1,,,4))-1,2),INT(ROW(OFFSET($A$1,,,4))/3),,)),N(OFFSET(t,{0,0;0,1;0,0;0,1}+{0,1;0,1;0,1;0,1}*(MATCH($C32,y)),{0,0;0,0;1,0;1,0}+{1,0;1,0;1,0;1,0}*(MATCH(D$25,x)))),TRUE,FALSE)


Here's what LINEST actually returns:

{-0.329226038616547,0.149502610190897,6.92224319824496}

...with the first two numbers being coefficients for our x and y variables, and the third being a constant.


So we need to multiply our x and y values at a particular point in our lookup table (and again I'm using an x value of 3 and a y value of 8) against the first two numbers, then add the third number.


You could do this using three INDEX formulas. But that would be long and boring...kind of like the Lord of the Rings films.


So instead we can use CHOOSE({1,2,3},$C32,D$25,1) to create an array of our required multipliers of {8,3,1} and multiply that against our LINEST output of {-0.329226038616547,0.149502610190897,6.92224319824496} and then SUM the result. (The $C32 points to the y value applicable at the particular table cell, and the D$25 points to the applicable x value.)


Like this:

=SUM(LINEST(N(OFFSET(OFFSET(t,MATCH($C32,y),MATCH(D$25,x),2,2),MOD(ROW(OFFSET($A$1,,,4))-1,2),INT(ROW(OFFSET($A$1,,,4))/3),,)),N(OFFSET(t,{0,0;0,1;0,0;0,1}+{0,1;0,1;0,1;0,1}*(MATCH($C32,y)),{0,0;0,0;1,0;1,0}+{1,0;1,0;1,0;1,0}*(MATCH(D$25,x)))),TRUE,FALSE)*CHOOSE({1,2,3},$C32,D$25,1))

=4.74


Hey, that's pretty close to our two way interpolation of 4.71


In fact, all the output is within a whisker:

Regression:

Code:
y/x    3      3.5    4      4.5    6      7
2.5    6.9    6.8    6.8    6.8    6.8    7.0
2.75   6.7    6.7    6.7    6.7    6.8    6.9
3.25   6.5    6.5    6.5    6.5    6.6    6.7
4      6.1    6.1    6.2    6.3    6.3    6.5
5      5.7    5.8    5.9    5.9    6.1    6.3
7.7    4.8    4.9    5.0    5.1    5.4    5.6
8      4.74   4.8    4.9    5.0    5.4    5.6
two way interpolation

Code:
y/x     3      3.5   4        4.5    6      7
2.5     6.9    6.8    6.8    6.8    6.9    6.9
2.75    6.7    6.7    6.7    6.7    6.8    6.9
3.25    6.5    6.5    6.5    6.5    6.6    6.7
4       6.1    6.1    6.2    6.2    6.3    6.5
5       5.8    5.8    5.8    5.9    6.1    6.3
7.7     4.8    4.9    5.0    5.1    5.4    5.7
8       4.71   4.8    4.9    5.0    5.4    5.6
 
Last edited:
189 characters
Code:
=SUM(({-1;1}*TREND({0;1},OFFSET(y,MATCH($C25,y,1)-1,0,2,1),$C25)+{1;0})*({-1,1}*TREND({0,1},OFFSET(x,0,MATCH(D$24,x,1)-1,1,2),D$24)+{1,0})*OFFSET(z,MATCH($C25,y,1)-1,MATCH(D$24,x,1)-1,2,2))
 
Back
Top