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

Plotting Cost from a table between known points

J

JakeF

New Member
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: 
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]
 
http://i178.photobucket.com/albums/w257/JF3RGY/TABLE_zps513c1ea1.jpg


Sorry, table did not make sense!
 
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
 
Hi Jake ,


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

[pre]
Code: 
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
 
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
 
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
 
Correct, although I only know the last value from plotting on a graph!


This Looks promising....


Jake
 
Have a read of

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

There is a good bilinear interpolation function that seems to work
 
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
 
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
 
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
 
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:
You must log in or register to reply here.
Back
Top