interpolation

[atatari]

Dear Friends,

I am trying to obtain the interpolated value from column B and return the
corresponding value from column A. There are two issues: I have repeated
numbers in column B which in this case I want the lowest value from column A
to be used and also as you can see the column A is between 0-1.8 and B is in
percentage.
For example 80% or 8% or 5% from column B corresponds to what values from
Column A.

Thanks for your help

A B
0.046875 100.00%
0.09375 100.00%
0.421875 99.60%
1.03125 99.60%
1.078125 22.40%
1.125 18.40%
1.171875 15.60%
1.21875 13.60%
1.265625 12.40%
1.3125 11.20%
1.359375 10.00%
1.40625 9.20%
1.453125 8.80%
1.5 8.00%
1.546875 7.20%
1.59375 6.80%
1.640625 6.40%
1.6875 6.00%
1.734375 5.60%
1.78125 5.20%
1.828125 4.80%

 

[Niek Otten]

Are you sure you don't want to interpolate if the values in B are the same?
Anyway, if that is what you require:

Table in A1:B21, value to search for in C1.
In D1:
=MATCH(C1,B1:B21,-1)
In D2:
=D1+1
In E1:
=INDEX($A$1:$A$21,D1)
In E2:
=INDEX($A$1:$A$21,D2)
In F1:
=INDEX($B$1:$B$21,D1)
In F2:
=INDEX($B$1:$B$21,D2)
In G1:
=IF(F1=F2,E2,E1+(F1-C1)/(F1-F2)*(E2-E1))

 

[Niek Otten]

A bit late, but I just finished my UDF which, amongst other things, does
something in the area of your requirements.

If you're new to macros, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The code of the function:

' =========================================================================
Option Explicit
' =========================================================================

Function TableInterpol(ToFind As Double, Table As Range, ResultColumnNr As
Long, _
Optional SortDir, Optional KeyColumnNr)
' Niek Otten
' Works like Vlookup, but interpolates and has some options
' 1st argument: Key to look for. Numbers only!
' 2nd argument: Range to look in and get the result from. Numbers only!
' 3rd argument: Relative column number in the range to extract the result
from
' Optional 4th argument: defaults to 1: "Ascending"; anything else results
in Descending
' Optional 5th argument: Relative column number in the range to search the
key in,
' defaults to 1

Dim RowNrLow As Long
Dim RowNrHigh As Long
Dim ResultLow As Double
Dim ResultHigh As Double
Dim KeyFoundLow As Double
Dim KeyFoundHigh As Double

If IsMissing(SortDir) Then
SortDir = 1
Else
SortDir = -1
End If

If IsMissing(KeyColumnNr) Then
KeyColumnNr = 1
End If

RowNrLow = Application.WorksheetFunction.Match(ToFind, Intersect(Table,
Table.Cells(KeyColumnNr). _
EntireColumn), SortDir)
ResultLow = Table(RowNrLow, ResultColumnNr)

If ToFind = ResultLow Then
TableInterpol = Table(RowNrLow, ResultColumnNr) ' do not interpolate for
exact matches
Exit Function
End If

RowNrHigh = RowNrLow + 1
ResultHigh = Table(RowNrHigh, ResultColumnNr)
KeyFoundLow = Table(RowNrLow, KeyColumnNr)
KeyFoundHigh = Table(RowNrHigh, KeyColumnNr)
TableInterpol = ResultLow + (ToFind - KeyFoundLow) / (KeyFoundHigh -
KeyFoundLow) _
* (ResultHigh - ResultLow)

End Function
' =========================================================================

 

[Niek Otten]

Watch for line-wraps