Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
I have three columns of information:
RATE 15 DAY 30 DAY 5.750% 0.375 0.500 5.875% -0.250 -0.125 6.000% -0.625 -0.500 6.125% -1.125 -1.000 6.250% -1.500 -1.375 6.375% -1.875 -1.750 6.500% -2.250 -2.125 6.625% -2.750 -2.625 6.750% -3.125 -3.000 6.875% -3.625 -3.500 I three columns are the result of formulas which find the data elsewhere (albeit simple references, i.e. =R3). I would like to find the price in the third column closest, but never greater than zero. and return this as my first result. The second result is the corresponding interest rate (from the first column) that goes with the matched price. I've tried VLOOKUP, but I can't sort the third column another way and I keep getting -3.50 as my matched price. Other suggestions for how to do this? I would really appreciate it. Karen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
Try this:
With your example data in Cells A1:C11 ......and assuming that Col_C is in descending order. D1: =INDEX(C2:C11,MATCH(0,C2:C11,-1)+1,1) returns -0.125 E1: =INDEX(A2:A11,MATCH(0,C2:C11,-1)+1,1) returns 0.05875 Does that help? *********** Regards, Ron "K. Westerman" wrote: I have three columns of information: RATE 15 DAY 30 DAY 5.750% 0.375 0.500 5.875% -0.250 -0.125 6.000% -0.625 -0.500 6.125% -1.125 -1.000 6.250% -1.500 -1.375 6.375% -1.875 -1.750 6.500% -2.250 -2.125 6.625% -2.750 -2.625 6.750% -3.125 -3.000 6.875% -3.625 -3.500 I three columns are the result of formulas which find the data elsewhere (albeit simple references, i.e. =R3). I would like to find the price in the third column closest, but never greater than zero. and return this as my first result. The second result is the corresponding interest rate (from the first column) that goes with the matched price. I've tried VLOOKUP, but I can't sort the third column another way and I keep getting -3.50 as my matched price. Other suggestions for how to do this? I would really appreciate it. Karen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
Thanks so much for your reply. Some work and some don't. Here's an example
of one that doesn't work: Col f Col g Col h RATE 15 DAY 30 DAY 6.250% -0.500 -0.375 6.375% -0.875 -0.750 6.500% -1.125 -1.000 6.625% -1.375 -1.250 6.750% -1.625 -1.500 6.875% -1.750 -1.625 =INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1) This returns #N/A. Have I missed something here? Also, I would love to know how the formula breaks down; I don't completely understand the reason you do INDEX and then MATCH and what the -1, and +1,1 mean. Karen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
To avoid a much more complicated formula, just put these values at the top of
the table (just under the labels). The formula needs a value that is greater than zero. (If you don't want to see them....hide the row) RATE 15 Day 30 Day 1000.00% 999999 999999 Does that fix the situation? *********** Regards, Ron "K. Westerman" wrote: Thanks so much for your reply. Some work and some don't. Here's an example of one that doesn't work: Col f Col g Col h RATE 15 DAY 30 DAY 6.250% -0.500 -0.375 6.375% -0.875 -0.750 6.500% -1.125 -1.000 6.625% -1.375 -1.250 6.750% -1.625 -1.500 6.875% -1.750 -1.625 =INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1) This returns #N/A. Have I missed something here? Also, I would love to know how the formula breaks down; I don't completely understand the reason you do INDEX and then MATCH and what the -1, and +1,1 mean. Karen |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
Yes, that worked beautifully. Thank you very much,
Karen "Ron Coderre" wrote: To avoid a much more complicated formula, just put these values at the top of the table (just under the labels). The formula needs a value that is greater than zero. (If you don't want to see them....hide the row) RATE 15 Day 30 Day 1000.00% 999999 999999 Does that fix the situation? *********** Regards, Ron "K. Westerman" wrote: Thanks so much for your reply. Some work and some don't. Here's an example of one that doesn't work: Col f Col g Col h RATE 15 DAY 30 DAY 6.250% -0.500 -0.375 6.375% -0.875 -0.750 6.500% -1.125 -1.000 6.625% -1.375 -1.250 6.750% -1.625 -1.500 6.875% -1.750 -1.625 =INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1) This returns #N/A. Have I missed something here? Also, I would love to know how the formula breaks down; I don't completely understand the reason you do INDEX and then MATCH and what the -1, and +1,1 mean. Karen |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kaen
Thanks for the feedback, Karen....I'm glad I could help.
*********** Regards, Ron "K. Westerman" wrote: Yes, that worked beautifully. Thank you very much, Karen "Ron Coderre" wrote: To avoid a much more complicated formula, just put these values at the top of the table (just under the labels). The formula needs a value that is greater than zero. (If you don't want to see them....hide the row) RATE 15 Day 30 Day 1000.00% 999999 999999 Does that fix the situation? *********** Regards, Ron "K. Westerman" wrote: Thanks so much for your reply. Some work and some don't. Here's an example of one that doesn't work: Col f Col g Col h RATE 15 DAY 30 DAY 6.250% -0.500 -0.375 6.375% -0.875 -0.750 6.500% -1.125 -1.000 6.625% -1.375 -1.250 6.750% -1.625 -1.500 6.875% -1.750 -1.625 =INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1) This returns #N/A. Have I missed something here? Also, I would love to know how the formula breaks down; I don't completely understand the reason you do INDEX and then MATCH and what the -1, and +1,1 mean. Karen |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
One more small thing
Hi Ron,
I'm back. In some instances, there is a result of "0", but the formula is picking up the next lesser matching item, which is less than zero. I'd like it to pick the one that is closest to, but not larger than zero. Is it possible to tweak this? If I understood the -1, +1, 1, I could take a crack at it... Thanks so much. Here's the example: Col y Col z Col aa 1000.00% 999999 999999 5.63% 0.250 0.375 5.75% -0.125 0.000 5.88% -0.375 -0.250 6.00% -0.750 -0.625 6.13% -1.000 -0.875 6.25% -1.250 -1.125 6.38% -1.500 -1.375 6.50% -1.750 -1.625 =INDEX(Y32:Y40,MATCH(0,AA32:AA40,-1)+1,1) Thanks, Karen |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still need a little help
I'm still having the problem I mention below. I'm getting, in some
instances, the next lesser matching item when I want the one that is zero. Can you please help me? If there's a more complicated way, I'm willing to tackle it if you are. Thanks so much. "K. Westerman" wrote: Hi Ron, I'm back. In some instances, there is a result of "0", but the formula is picking up the next lesser matching item, which is less than zero. I'd like it to pick the one that is closest to, but not larger than zero. Is it possible to tweak this? If I understood the -1, +1, 1, I could take a crack at it... Thanks so much. Here's the example. Col y Col z Col aa 1000.00% 999999 999999 5.63% 0.250 0.375 5.75% -0.125 0.000 5.88% -0.375 -0.250 6.00% -0.750 -0.625 6.13% -1.000 -0.875 6.25% -1.250 -1.125 6.38% -1.500 -1.375 6.50% -1.750 -1.625 =INDEX(Y32:Y40,MATCH(0,AA32:AA40,-1)+1,1) with a result of 5.88%. Thanks, Karen |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still need a little help
Try this
=INDEX($F$20:$F$25,MATCH(SMALL($H$20:$H$25,COUNTIF ($H$20:$H$25,"<=0")),$H$20:$H$25,0)) -- Regards, Peo Sjoblom (No private emails please) "K. Westerman" wrote in message ... I'm still having the problem I mention below. I'm getting, in some instances, the next lesser matching item when I want the one that is zero. Can you please help me? If there's a more complicated way, I'm willing to tackle it if you are. Thanks so much. "K. Westerman" wrote: Hi Ron, I'm back. In some instances, there is a result of "0", but the formula is picking up the next lesser matching item, which is less than zero. I'd like it to pick the one that is closest to, but not larger than zero. Is it possible to tweak this? If I understood the -1, +1, 1, I could take a crack at it... Thanks so much. Here's the example. Col y Col z Col aa 1000.00% 999999 999999 5.63% 0.250 0.375 5.75% -0.125 0.000 5.88% -0.375 -0.250 6.00% -0.750 -0.625 6.13% -1.000 -0.875 6.25% -1.250 -1.125 6.38% -1.500 -1.375 6.50% -1.750 -1.625 =INDEX(Y32:Y40,MATCH(0,AA32:AA40,-1)+1,1) with a result of 5.88%. Thanks, Karen |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still need a little help
Hello Peo, This worked like a charm. Thanks so much. Now it's my turn to answer some questions on the site. I always try to "give back" if I can... Karen "Peo Sjoblom" wrote: Try this =INDEX($F$20:$F$25,MATCH(SMALL($H$20:$H$25,COUNTIF ($H$20:$H$25,"<=0")),$H$20:$H$25,0)) -- Regards, Peo Sjoblom (No private emails please) "K. Westerman" wrote in message ... I'm still having the problem I mention below. I'm getting, in some instances, the next lesser matching item when I want the one that is zero. Can you please help me? If there's a more complicated way, I'm willing to tackle it if you are. Thanks so much. "K. Westerman" wrote: Hi Ron, I'm back. In some instances, there is a result of "0", but the formula is picking up the next lesser matching item, which is less than zero. I'd like it to pick the one that is closest to, but not larger than zero. Is it possible to tweak this? If I understood the -1, +1, 1, I could take a crack at it... Thanks so much. Here's the example. Col y Col z Col aa 1000.00% 999999 999999 5.63% 0.250 0.375 5.75% -0.125 0.000 5.88% -0.375 -0.250 6.00% -0.750 -0.625 6.13% -1.000 -0.875 6.25% -1.250 -1.125 6.38% -1.500 -1.375 6.50% -1.750 -1.625 =INDEX(Y32:Y40,MATCH(0,AA32:AA40,-1)+1,1) with a result of 5.88%. Thanks, Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|