Index Match, Looping IF, Descending Array . . . I'm in need of Help Debugging my Function

[Michael515]

Okay guys. Usually I am able to find some type of article that helps me with my excel issues but this time I cannot and that's why I am here. I think with your experitse you can help think outside the box on this one. Here is the problem.

[TABLE="width: 776"]
<colgroup><col><col><col span="8"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]1/11/1967[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]-3.20%[/TD]
[TD]Yes[/TD]
[TD="align: right"]2/15/1967[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/12/1967[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1/13/1967[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]-0.88%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]1/16/1967[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]-0.44%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]1/17/1967[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]0.45%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]1/18/1967[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]0.44%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]1/19/1967[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.58[/TD]
[TD="align: right"]1.10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]1/20/1967[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0.44%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]1/23/1967[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]1/24/1967[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]1.09%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]1/25/1967[/TD]
[TD="align: right"]4.62[/TD]
[TD="align: right"]4.62[/TD]
[TD="align: right"]4.62[/TD]
[TD="align: right"]4.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.62[/TD]
[TD="align: right"]-0.65%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]1/26/1967[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]-2.60%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]1/27/1967[/TD]
[TD="align: right"]4.47[/TD]
[TD="align: right"]4.47[/TD]
[TD="align: right"]4.47[/TD]
[TD="align: right"]4.47[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.47[/TD]
[TD="align: right"]-0.67%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]1/30/1967[/TD]
[TD="align: right"]4.48[/TD]
[TD="align: right"]4.48[/TD]
[TD="align: right"]4.48[/TD]
[TD="align: right"]4.48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.48[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]1/31/1967[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0.89%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]2/1/1967[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]2/2/1967[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.51[/TD]
[TD="align: right"]-0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]2/3/1967[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.52[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]2/6/1967[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]2/7/1967[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.70[/TD]
[TD="align: right"]0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]2/8/1967[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.54[/TD]
[TD="align: right"]0.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]2/9/1967[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.53[/TD]
[TD="align: right"]-0.22%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: right"]2/10/1967[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.59[/TD]
[TD="align: right"]1.32%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]2/14/1967[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]1.09%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: right"]2/15/1967[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.69[/TD]
[TD="align: right"]1.08%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is a table showing the returns on US Treasury Bills over the course of time from Yahoo Finance. In Column H, it calculates the percentage rise and drop of the adj close (column G) from the previous day. The adj close for 1/10/1967 was 4.69, so it dropped 3.02% to 4.54 the next day. Now what I am trying to calculate in column J (column I was just an IF statement that returned Yes if the drop was greater than 3%) is what the date is when the price returns to or exceeds the price before the drop, i.e. when does the adj close return to at least 4.69. What I used for this was =IF(I1="YES", INDEX(A2:$A$25, MATCH(G0, G2:$G$25,0 )), "") . With this equation it gives me the date shown 2/15/1967. As you may have realized already, the problem, is on 2/7/1967, the adj close surpassed 4.69 and reached 4.70, which is the date I want to see in cell J1 not 2/15/1967. The problem is that since the array in the match function is not in descending order, I cant make it so that if I place a -1 at the end, it will return the first greatest value. My question is simply: is there a way to get around the descending order requirement. I want a function that determines If I1=Yes, when is the next date that G0 (in this case 4.69, as it is not shown) is 4.69 or greater. The equation I have right now looks for exactly the 4.69 number, not it or anything greater (this poses a problem because in one instance later in the early 70s it returns a date 847 days into the future, when it should be only like a year into the future, but since it is looking for exactly that number and not it or anything greater, its causing problems). I want the answer in J1 to be 2/7/1967 not 2/15/1967. Anyone have any suggestions or ideas?

 

[Russell Hauf]

Hi Michael,

You should be able to do this with an array formula. This is what I have in cell J1:

{=IF(I1="YES",INDEX($A$1:$A$50,MIN(IF($G2:$G$50 >= G1,ROW($G2:$G$50),""))),"")}

If you don't know about Array formulas, just enter the text between the braces {}, and then hit Ctrl+Shift+Enter (the braces will be added by Excel).

Hope this helps,

 

[Michael515]

Hi Michael,

You should be able to do this with an array formula. This is what I have in cell J1:

{=IF(I1="YES",INDEX($A$1:$A$50,MIN(IF($G2:$G$50 >= G1,ROW($G2:$G$50),""))),"")}

If you don't know about Array formulas, just enter the text between the braces {}, and then hit Ctrl+Shift+Enter (the braces will be added by Excel).

Hope this helps,

I am trying to link the image to you, if the picture doesn't upload here is the link: View image: Treasury Research Help

As you can see 4.69 in cell J1255 is the number we are looking for, and when it appears again, however the answer the formula wants to give me is 2/24/1972, when it is clear that it should be 2/25/1967 at the bottom of the image. Again I want the formula to look for the first instant it is >= , just this case the first instance it is equal, but there are other cases where it is greater but not equal so Index Match doesn't work. Any ideas on what the problem is?

 

[Russell Hauf]

Interesting. Are your values exact values? So is the underlying value in the 4.69 cell just 4.69? Or could it be 4.688881, for example?

 

[Michael515]

As you can see I am trying to find when the previous day's price occurs again, when G1254, of 4.69, occurs again or is surpassed. Therefore column I is used to tell me whether or not there is a 3% or greater drop in price, and when there is, it becomes the trigger (the first part of the equation with the If statement) to find when the previous days price, the price before the drop, occurs again, which in the picture you see, should be 2/25/1967. The premise of this analysis is to see how long it takes for the 10 yr. Treasuries to return to the price they had before the 3% or greater drop. Thus in Column K, once we solve Column J, I will have a =DAYS360(A1255, J1255) to find that piece of data. Combined with the later occurrences, I can run a statistical analysis on the 192 drops of 3% or greater in the price of 10 yr Treasuries. I hope this explains everything I'm trying to do clearly.

 

[Michael515]

They are indeed exact values, downloaded straight from Yahoo Finance of ^TNX.

 

[Russell Hauf]

Ok, I think I know the issue. In the INDEX part of the formula, you need to have $A$1:$A$13130. It needs to remain static, as we are using the ROW function in the latter part of the formula. Let me know if that helps things.

 

[Michael515]

Unfortunately no, it now returns the #VALUE! error message as shown here: View image: Treasury Research Help 2

I really appreciate the help, do you think there may be anything else wrong with the function/data or another combination of functions/data we could use?

 

[Jerry Sullivan]

Hi Michael, In the image you linked to post #8, you're getting the #VALUE! error because the formula has not been array-entered with Ctrl-Shift-Enter.

If you enter the formula like that you'll see {curly braces} around the formula and the result 2/7/1967 should be displayed.

I'd suggest this modified version: In J1255 confirm with Ctrl-Shift-Enter (not just Enter)

=IF(I1255="YES",IF(MAX($G1255:$G$13130)<$G1254,NA(),INDEX($A1255:$A$13130,MIN(IF($G1255:$G$13130 >= $G1254,ROW($G1255:$G$13130)-ROW($G1255)+1,"")))),"")

 

[Michael515]

Thank you guys for all the help. I finally got the formula to work. I appreciate the time and effort!