Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Continuation to equivalent for formula - to Mr. Harlan Grove (and notonly...) | Excel Worksheet Functions | |||
EQUIVALENT for this FORMULA , please | Excel Worksheet Functions | |||
Maxif equivalent | Excel Worksheet Functions | |||
I need the equivalent of a "averageif" formula... | Excel Discussion (Misc queries) | |||
Formula equivalent of CurrentRegion | Excel Worksheet Functions |