1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

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

Discussion in 'Excel Challenges' started by jeffreyweir, Jul 23, 2013.

1. ### jeffreyweirActive Member

Messages:
1,085
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 (text):
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 (text):

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 (text):
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: Sep 10, 2015
2. ### bobhcExcel Ninja

Messages:
3,336
Good day Jeff

A mad scientist / concocted / experiments / children as test subject, Jeff you need to see someone with medical and counselling skills…Fast
3. ### jeffreyweirActive Member

Messages:
1,085
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.
4. ### Luke MExcel Ninja

Messages:
9,266
@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?
5. ### jeffreyweirActive Member

Messages:
1,085
Correct, both of you
6. ### shrivallabhaExcel Ninja

Messages:
1,598
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,))
7. ### jeffreyweirActive Member

Messages:
1,085
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})
8. ### jeffreyweirActive Member

Messages:
1,085
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}))
9. ### ianamckMember

Messages:
35
@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.
10. ### jeffreyweirActive Member

Messages:
1,085
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 ;-)
11. ### shrivallabhaExcel Ninja

Messages:
1,598
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.
12. ### HuiExcel NinjaStaff Member

Messages:
10,378
I posted 2 solutions at

http://chandoo.org/forums/topic/plotting-cost-from-a-table-between-known-points
jeffreyweir likes this.
13. ### jeffreyweirActive Member

Messages:
1,085
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 (vb):
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 (vb):
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 (vb):

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: Feb 16, 2016
14. ### alphamaxNew Member

Messages:
7
Galen likes this.
15. ### alphamaxNew Member

Messages:
7
189 characters
Code (vb):
=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))
jeffreyweir likes this.
16. ### jeffreyweirActive Member

Messages:
1,085
@alphamax Wow! I'll yet to digest this, but I just wanted to say...Wow!
Last edited: Feb 16, 2016
17. ### GalenNew Member

Messages:
2
Excellent! I figured it out finally.