interpolation

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%

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))

--
Kind regards,

Niek Otten


"atatari" <atatari@discussions.microsoft.com> wrote in message
news:E3907E0C-4520-4E67-992F-8CE654A8E12A@microsoft.com...
> 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%
>

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
' =========================================================================


--
Kind regards,

Niek Otten

"atatari" <atatari@discussions.microsoft.com> wrote in message
news:E3907E0C-4520-4E67-992F-8CE654A8E12A@microsoft.com...
> 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%
>

Watch for line-wraps

--
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:uRVlGLLOGHA.2320@TK2MSFTNGP11.phx.gbl...
>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
> '
> =========================================================================
>
>
> --
> Kind regards,
>
> Niek Otten
>
> "atatari" <atatari@discussions.microsoft.com> wrote in message
> news:E3907E0C-4520-4E67-992F-8CE654A8E12A@microsoft.com...
>> 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%
>>
>
>