If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I cross reference information in a table?
I am writing a program in Excel and I want to be able to pull information
from a table, which is 15x11, in another worksheet. In the program,the user enters a value which will corresponds to a specific column in the table (so this is my x-value). Each column contain a series of increasing numbers. I want the program to interpolate within that specific column entered by the user, to another value that the program had calulated previously, then reference that to a value in the first column (y-value). Example: A1=4.5 B1=545 3.0 3.5 4.0 4.5 5.0 5.5 0 | 1 | 2 | 3 523 4 - - - 555 5 I want the program to return the value of 3.69. |
#2
|
|||
|
|||
How can I cross reference information in a table?
This is a bit wordy, but using the example table as definition (A1:G7):
=INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,,M ATCH(A1,Sheet1!B1:G1))))+ (A2- INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1))),MATCH(A1,Sheet1!B1:G1))) *(INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,, MATCH(A1,Sheet1!B1:G1)))+1) -INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1))))) /(INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,M ATCH(A1,Sheet1!B1:G1)))+1,MATCH(A1,Sheet1!B1:G1))- INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1))),MATCH(A1,Sheet1!B1:G1))) Follows linear interpolation formula of: Y = Ya+(X-Xa)*(Yb-Ya)/(Xb-Xa) -- Best Regards, Luke M "Eric" wrote in message ... I am writing a program in Excel and I want to be able to pull information from a table, which is 15x11, in another worksheet. In the program,the user enters a value which will corresponds to a specific column in the table (so this is my x-value). Each column contain a series of increasing numbers. I want the program to interpolate within that specific column entered by the user, to another value that the program had calulated previously, then reference that to a value in the first column (y-value). Example: A1=4.5 B1=545 3.0 3.5 4.0 4.5 5.0 5.5 0 | 1 | 2 | 3 523 4 - - - 555 5 I want the program to return the value of 3.69. |
Thread Tools | |
Display Modes | |
|
|