I need the equivalent formula for this UDF

[Don]

Hi,

This UDF looks down the range of prices in PriceRange until it finds MyPrice
(100 in this case) and then interpolates the correct yield in RateRange.

Function ParYield(MyPrice, PriceRange As Range, RateRange As Range)
For i = 1 To PriceRange.Rows.Count - 1
If MyPrice <= PriceRange.Cells(i) And MyPrice >= PriceRange.Cells(i + 1)
Then
ParYield = (RateRange.Cells(i + 1) + (RateRange.Cells(i) -
RateRange.Cells(i + 1)) * (MyPrice - PriceRange.Cells(i + 1)) /
(PriceRange.Cells(i) - PriceRange.Cells(i + 1)))
Exit Function
End If
Next i
End Function

Example:

RateRange PriceRange
5.094 102.175
5.055 101.7138
5.016 101.2528
4.984 100.7545
4.955 100.2406
4.927 99.7221
4.898 99.2096
4.88 98.6397
4.867 98.0446
4.854 97.4511

For MyPrice = 100, ParYield = 4.942

Would someone show me the equivalent worksheet formula for this UDF?

Thanks
Don

 

[Don]

While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1)+1,0)),TRANSPOSE(INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1)+1,0)))

 

[Don]

P.S. The above formula is entered as an array

While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1)+1,0)),TRANSPOSE(INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1)+1,0)))

 

[Don]

I forgot to make the 100 a variable (MyPrice)

=FORECAST(MyPrice,TRANSPOSE(INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(MyPrice,PriceRange,-1)),RateRange,-1),0):INDEX(RateRange,MATCH(INDEX(RateRange,MATCH(MyPrice,PriceRange,-1)),RateRange,-1)+1,0)),TRANSPOSE(INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(MyPrice,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH(MyPrice,PriceRange,-1)),RateRange,-1)+1,0)))

Array entered