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.

Plotting Cost from a table between known points

Discussion in 'Ask an Excel Question' started by JakeF, Feb 21, 2013.

  1. JakeF

    JakeF New Member

    Messages:
    6
    Hi, first post here, hopefully you guys can help with something that has extended into a second day troubling me!


    I am trying to work out the cost of an item if it falls between the non-standard sizes below. (i.e if it is 0.35 in width and 0.7 in height) I have done this manually by plotting on a graph according to trend, but I will be doing this a number of times and wonoder if anyone knows of a formua I could use where I simpy have to enter Height and Width?

    Thanks in advance!

    [pre]
    Code (vb):
    A   B   C   D   E   F   G   H
    Width
    1       m   0.1 0.2 0.3 0.4 0.5 0.8 1
    2   Height  0.2 £340   £340   £340   £340   £340   £340   £340
    3       0.4 £340   £340   £340   £340   £340   £363   £455
    4       0.5 £340   £340   £340   £340   £340   £455   £568
    5       0.6 £340   £340   £340   £340   £340   £545   £680
    6       0.8 £340   £340   £340   £363   £455   £725   £908
    7       1   £340   £340   £340   £455   £568   £908   £1,133
    8       1.2 £340   £340   £410   £545   £680   £1,088 £1,360
    9       1.5 £340   £340   £510   £680   £850   £1,360 £1,700
    10      1.8 £340   £410   £613   £818   £1,020 £1,633 £2,038
    [/pre]
  2. JakeF

    JakeF New Member

    Messages:
    6
    http://i178.photobucket.com/albums/w257/JF3RGY/TABLE_zps513c1ea1.jpg


    Sorry, table did not make sense!
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    Hi Jake ,


    I have edited your post , by putting a backtick ` just before the start of your table , and another backtick at the end of your table.


    Narayan
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    Hi Jake ,


    This is to clarify your exact requirement ; suppose we consider the following segment of your table :

    [pre]
    Code (vb):
    A   B   C   D   E   F   G   H
    Width
    1       m   0.1 0.2 0.3 0.4 0.5 0.8 1
    2   Height  0.2 £340   £340   £340   £340   £340   £340   £340
    6       0.8 £340   £340   £340   £363   £455   £725   £908
    7       1   £340   £340   £340   £455   £568   £908   £1,133
    [/pre]
    Suppose a height of 0.8 , and a width of 0.45 is entered ; what should be the output ?


    Suppose a height of 0.9 , and a width of 0.45 is entered ; what should be the output ?


    Is it always a linear interpolation between the two points ?


    Narayan
  5. JakeF

    JakeF New Member

    Messages:
    6
    Thanks for sorting the table,


    If I understand your question: The output should aways be a cost.


    Suppose a height of 0.8 , and a width of 0.45 : Output would be somewhere between £363 and £455


    Suppose a height of 0.9 , and a width of 0.45 : Output would be somewhere between £363, £455, £455 and £568


    90% of the time the size I need to cost will not fit into either the length or height so will need interpolation between 4 points?


    Sorry if i misunderstood the question


    Thanks
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    Hi Jake ,


    Suppose for a height of 0.8 , and a width of 0.4 , the output is 363 , and for the same height , and a width of 0.5 , the output is 455 ; then using linear interpolation , the output for the same height , and a width of 0.45 , would be 409.


    Similarly , for a height of 0.9 , and a width of 0.45 , the output would be 460.25


    Can you confirm these values are correct ?


    Narayan
  7. JakeF

    JakeF New Member

    Messages:
    6
    Correct, although I only know the last value from plotting on a graph!


    This Looks promising....


    Jake
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,379
    Have a read of

    http://www.ozgrid.com/forum/showthread.php?t=64224

    There is a good bilinear interpolation function that seems to work
  9. JakeF

    JakeF New Member

    Messages:
    6
    Goodness, I'm going to have to spend some time to work out how to best fit that to my problem, there doesn't appear to be an example like mine with two drivers?


    It would be interesting to see how Narayan got to 460.25


    Thanks for the help thus far
  10. JakeF

    JakeF New Member

    Messages:
    6
    Sorry, just re-cliked that link again and got exactly what I wanted (diverted me somewhere else first time!?)


    Its brilliant, I want to try and get my head around how it actually works rather than just pasting though - perhaps for another day.


    Thanks guys
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    Hi Jake ,


    Let me know if you are still interested ; I can upload a sample workbook.


    Narayan
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,379
    There is a great article on Bi-Linear Interpolation at:

    http://en.wikipedia.org/wiki/Bilinear_interpolation


    This gives a formula solution to solve your problem


    Which implemented in Excel gives:

    =(INDEX(Data,PosR1,PosC1)*(ValC2-$B$13)/(ValC2-ValC1) + INDEX(Data,PosR1,PosC2)*($B$13-ValC1)/(ValC2-ValC1))*(ValR2-$B$12)/(ValR2-ValR1) + (INDEX(Data,PosR2,PosC1)*(ValC2-$B$13)/(ValC2-ValC1) + INDEX(Data,PosR2,PosC2)*($B$13-ValC1)/(ValC2-ValC1))*($B$12-ValR1)/(ValR2-ValR1)


    Where the following named Formulas are used

    Data: =Sheet1!$B$2:$H$10

    PosC1: =MATCH(Sheet1!$B$13,Sheet1!$B$1:$H$1,1)

    PosC2: =PosC1+1

    ValC1: =INDEX(Sheet1!$B$1:$H$1,,PosC1)

    ValC2: =INDEX(Sheet1!$B$1:$H$1,,PosC2)

    PosR1: =MATCH(Sheet1!$B$12,Sheet1!$A$2:$A$10,1)

    PosR2: =PosR1+1

    ValR1: =INDEX(Sheet1!$A$2:$A$10,PosR1,)

    ValR2: =INDEX(Sheet1!$A$2:$A$10,PosR2,)


    Whew!


    or


    You can download an example here

    https://www.dropbox.com/s/ncccfwss7hudj2j/Huis%20Bi-Linear%20Formula.xlsx


    ps: I will not be writing a Formula Forensics on how this works but for those interested it all uses Similar Triangles
  13. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,379
    I have updated this file so that there are only 3 references to the worksheet


    d: =Sheet1!$A$1:$H$10

    Row Input cell

    Column Input cell

    All the other Named Formulas listed above flow out of the Data Range d

    Which is highlighted by a Blue Outline and includes the data and the Rows/Columns Headers


    This is a great example of the advanced use of Named Formulas

    https://www.dropbox.com/s/h8oeibrhuwcxa1l/Huis Bi-Linear Formula 2.xlsx
    See file 2 posts below
    Last edited: May 19, 2017
  14. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,085
    Hui - can you post that workbook here? The above link doesn't seem to be working.
  15. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,379
    Hi Jeff

    I found it

    Only 1 year, 3 Months and 2 Days

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page