ExcelBanter - I need the equivalent formula for this UDF

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - I need the equivalent formula for this UDF (https://www.excelbanter.com/excel-programming/309292-i-need-equivalent-formula-udf.html)

I need the equivalent formula for this UDF

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

I need the equivalent formula for this UDF

While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDE X(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(Ra teRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH (100,PriceRange,-1)),RateRange,-1)+1,0)))

"Don" wrote in message
...
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

I need the equivalent formula for this UDF

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

"Don" wrote in message
...
While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDE X(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(Ra teRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH (100,PriceRange,-1)),RateRange,-1)+1,0)))

"Don" wrote in message
...
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

I need the equivalent formula for this UDF

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(Ra teRange,MATCH(MyPrice,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH (MyPrice,PriceRange,-1)),RateRange,-1)+1,0)))

Array entered

"Don" wrote in message
...
P.S. The above formula is entered as an array

"Don" wrote in message
...
While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDE X(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(Ra teRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH (100,PriceRange,-1)),RateRange,-1)+1,0)))

"Don" wrote in message
...
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