I need the equivalent formula for this UDF

D

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
 
D

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

Don

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



Don said:
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)))
Click to expand...
 
D

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
 
Top