Selecting data in tables, if not given then interpolating

[Ken]

Hi, I'm setting up an excel sheet that needs to reference a table to get
values used in further formulas.

I have a formula that finds a value and you need to take that value and find
it in a column of data and output a certain value from that same column, but
from a different row.

I can use the HLOOKUP() but if the value is not exact, I would like to be
able to interpolate the table data to get an exact answer based on the linear
relationship that interpolating uses.

If im finding a value R, then I have a table like this:

R: 0 0.1 0.2 0.3
a: 1.0 1.07 1.15 1.23
b: 1.0 0.93 0.89 0.75

So if I come to a result where R=0.15, I would like to get the answer of
1.11, which I interpolated.

I have very little experience in any sort of programming, so I appreciate
any help.

 

[Ken]

Forgot to add, that value of 1.11 was for a. I have a, b and c rows in my
table. I need to be able to use the program to get an answer for a, b, and c
in different instances (basically have a part that selects which row to go
from like in HLOOKUP)

sorry for the confusion

 

[Rick]

I have a similar problem where I want to interpolate values between entries
on a table.

Have been looking through many help sites like this one and have concluded
that Excel can't interpolate.

I tried Excel's FORECAST() formula but this only seems to work if the values
in the table would make a straight line on a graph.

Option 1
It's theoretically possible to derive a mathematical equation from the data.
Try running Excel's chart wizard on your table to see if it produces a nice
graph.
If it's a continuous straight line, then you will be able to obtain a
formula for the graph.
Don't know how good your mathematics is, but if you don't know how to obtain
the formula, there are plenty of help sites on the internet.
Example of a mathematical formula for a straight line; y = 4x – 2
If your graph is a smooth continuous curve, it still may be possible to
derive an equation, you would end up with an equation something like; y =
4x^2 -2
Then you can just use Excel to plug in the number you already have and
obtain the exact equivalent value for the next row on your table

Option 2
If your graph is not straight, or is unusually shaped or irregular in some
other way, then probably it doesn't conform to an equation at all.
(or the equation is simply too complex for normal people to comprehend!)

Many industries (eg engineering, aviation, etc.) use tables like these, so I
will assume that (just like me) you have something like this.

The only way I found to do it was to instruct Excel to do exactly what I
would do if I was manually interpolating the tables.
I will copy below the way I processed your data to make Excel obtain the
same result of a=1.11 when R=0.15, just like you indicated in your posting.


CELL a B C D E
1 R 0 0.1 0.2 0.3
2 a 1 1.07 1.15 1.23
3 b 1 0.93 0.89 0.75

CELL A B C
5 Formula Value
Description
6 n/a 0.15
actual R
7 =HLOOKUP(B6,B1:E3,1,TRUE) 0.1 lower R
8 =B7+0.1 0.2 upper R
(assuming R in the table always increases by same amount, ie 0.1)
9 =B8-B7 0.1
difference 'R'
10 =B6-B7 0.05
difference 'actual R'
11 =HLOOKUP(B7,B1:E3,2,TRUE) 1.07 lower a
12 =HLOOKUP(B8,B1:E3,2,TRUE) 1.15 upper a
13 =B12-B11 0.08
difference 'a'
14 =B13/B9 0.8 variation
15 =B14*B10 0.04 variation x
difference
16 =B15+B11 1.11 actual
value for a


To get the values for b, c, etc. you should repeat once again the column of
formulas, and change the relevant cell values in order to interpolate the
next row.
I know it looks clunky and takes up loads of space on your spreadsheet, but
it's the only way I have found (so far!) for Excel to interpolate data.

For my tables, I copied the table on one worksheet, kept the calculations on
a seperate worksheet, and had a third worksheet which used the results
obtained. It's easy enough to cross-reference the sheets and it keeps the
calculations hidden away out of sight.

Hope this was a help.

 

[Lori]

Try these two formulas for a and b (with B6 as interpolation value):

=PERCENTILE(B1:E1,PERCENTRANK(B2:E2,B6,30))
=PERCENTILE(B1:E1,PERCENTRANK(-B3:E3,-B6,30))

The second formula needs to be Ctrl+Shift+Entered.

Note these formulas apply for increasing/decreasing data. Alternatives to
linear interpolation are better suited if no such relationship exists.