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

    jeffreyweir Active 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. bobhc

    bobhc Excel 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. jeffreyweir

    jeffreyweir Active 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 M

    Luke M Excel 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. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,085
    Correct, both of you :)
  6. shrivallabha

    shrivallabha Excel 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. jeffreyweir

    jeffreyweir Active 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. jeffreyweir

    jeffreyweir Active 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. ianamck

    ianamck Member

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

    jeffreyweir Active 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. shrivallabha

    shrivallabha Excel 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. Hui

    Hui Excel Ninja Staff 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. jeffreyweir

    jeffreyweir Active 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. alphamax

    alphamax New Member

    Messages:
    7
    Galen likes this.
  15. alphamax

    alphamax New 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. jeffreyweir

    jeffreyweir Active 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. Galen

    Galen New Member

    Messages:
    2
    Excellent! I figured it out finally.

Share This Page