Interpolation within a data table
 

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

Hi,

You told us what you don't want, but what do you want?

Also, VLOOKUP would not work with the sample you showed us because the 6.70
and 6.60 are not in your table.

If you put those values as the first column of the lookup table then IF what
you want is to have all vaules over 6.60 use the resutls of the 6.70 row then
change your formula to

=VLOOKUP(ROUNDUP(H1,1),A1:E10,2,False)

In this case the value 6.67 is in H1 and the lookup table is in A1:E10 with
A1:A10 containing the lookup values

A B C D E
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

If this helps, please clickt the Yes button,

Cheers,
Shane Devenshire

"hellmouthpirate" wrote:

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

Try this starting in A1.

6.6 13657 178.3 70.2 67.79
6.7 13886 179.9 70.36 67.68


6.67 13817.3 179.42 70.312 67.713

A5 contains your calculated value. B5 contains the following formula which
is copied to C5, D5 and E5.

=(1-(($A$2-$A5)/($A$2-$A$1)))*(B$2-B$1)+B$1

I did change the value in D2 because it is lower than D1. If your data is
really variable like that, you could modify the formula to accommodate it.

HTH


"hellmouthpirate" wrote:

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

your explanation is vague
what are you trying to accomplish?

On 27 Lut, 14:42, hellmouthpirate
wrote:
I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a table.

For Example
* * * * * * * * * * A * * * * * * B * * * * * * C * * * * * *D
6.60 * * * * * 13657 * * * 178.3 * * *70.20 * * *67.79
6.70 * * * * * 13886 * * * 179.9 * * *70.16 * * *67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.

Yours hopefully
Simon

Interpolation within a data table
 

Hello Simon,

Values in A1:E4:
A B C D
6.6 13657 178.3 70.2 67.79
6.7 13886 179.9 70.16 67.68
6.67 13817.3 179.42 70.172 67.713

[Please note that title A with numbers 13657, 13886, ... resides in
columnB!]

Formula in B4:
=B2+(B3-B2)*($A4-$A2)/($A3-$A2)

Copy across C4:E4.

Regards,
Bernd

Interpolation within a data table
 

Maybe my explanation was not the best. I have a data table that i have
incorporated into Excel. An example is:

A B C D E
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function
and Excel returns all the values asspciated with that answer. I need those
values to continue with the calculation.

The problem I have now is what happens when my answer lies between 6.60 and
6.70, I need the interpolated values returned now. For example, for 6.67 the
releavnt answer from column B should be 13817.3. I would like excel to do
this automatically. I cannot write an interpolation formula as suggested
previously as there are 80 rows and 12 columns of data so it would take me
forever.

I have tried the TREND function but the answer it returns is incorrect. It
returns an answer in excess of 13886 which cannot possibly be right.


"hellmouthpirate" wrote:

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

Hi,

Assume your data above is arranged in range B5:F6. Enter 6.67 in cell B9
and use the following formula in cell C9

=TREND(C5:C6,B5:B6,B9). The answer is exactly as mentioned by you - 13817.3

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"hellmouthpirate" wrote in
message ...
Maybe my explanation was not the best. I have a data table that i have
incorporated into Excel. An example is:

A B C D E
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function
and Excel returns all the values asspciated with that answer. I need those
values to continue with the calculation.

The problem I have now is what happens when my answer lies between 6.60
and
6.70, I need the interpolated values returned now. For example, for 6.67
the
releavnt answer from column B should be 13817.3. I would like excel to do
this automatically. I cannot write an interpolation formula as suggested
previously as there are 80 rows and 12 columns of data so it would take me
forever.

I have tried the TREND function but the answer it returns is incorrect. It
returns an answer in excess of 13886 which cannot possibly be right.


"hellmouthpirate" wrote:

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a
table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding
values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

Thank you Ashish

The problem is my range of values. When I used the data directly above and
below my required value I did indeed get the same answer as you. The formula
though, used the first and last values in my table of data hence the
discrepancy as the relationship is not entirely linear through all the values.

I will keeping playing with it and see if I can solve it someo ther way.
Ideally what I want is for excel to look up the values from the table and
automatically select the values directly above and below my required answer
and interpolate as needed. As i mentioned before I have 85 rows of data and
12 columns.

Thank you for your efforts.

"Ashish Mathur" wrote:

Hi,

Assume your data above is arranged in range B5:F6. Enter 6.67 in cell B9
and use the following formula in cell C9

=TREND(C5:C6,B5:B6,B9). The answer is exactly as mentioned by you - 13817.3

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"hellmouthpirate" wrote in
message ...
Maybe my explanation was not the best. I have a data table that i have
incorporated into Excel. An example is:

A B C D E
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function
and Excel returns all the values asspciated with that answer. I need those
values to continue with the calculation.

The problem I have now is what happens when my answer lies between 6.60
and
6.70, I need the interpolated values returned now. For example, for 6.67
the
releavnt answer from column B should be 13817.3. I would like excel to do
this automatically. I cannot write an interpolation formula as suggested
previously as there are 80 rows and 12 columns of data so it would take me
forever.

I have tried the TREND function but the answer it returns is incorrect. It
returns an answer in excess of 13886 which cannot possibly be right.


"hellmouthpirate" wrote:

I will try to explain this the best I can.

I have to do a calcualtion.
The answer to that calculation will then be used to look up data in a
table.

For Example
A B C D
6.60 13657 178.3 70.20 67.79
6.70 13886 179.9 70.16 67.68

My calculated answer was 6.67.
How do I write a formula so that excel will return the corresponding
values
for A B C & D for 6.67.

I have tried but the VLOOKUP function but it keeps returning the lower
values which is not what I want.


Yours hopefully
Simon

Interpolation within a data table
 

Hello,

TREND is IMHO not the right way to go. If, for example, you would have
A values of -1,000,000 for the points 6.5 and 6.8 then TREND through
all points 6.5, ..., 6.8 would result in -493,126 for the three points
6.6, 6.67 and 6.7.

If I understood you correctly you were looking for an interpolation.
My formula is giving you exactly that.

An interpolation UDF you can find he
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd