With Excel, in a series of rising pricebars, I'm trying to find the value of the lowest low in a pullback. I have to find this low value separate from price bars that preceded the pullback. The maximum number of bars I look back is 6, but in those 6 bars there may be lower lows before the pullback started. The pullback starts when one of the highs is lower than the previous high. I've attached a picture to clarify what I'm trying to do. Does anyone have any ideas?
I've attached a picture of a spreadsheet to clarify what I'm trying to do. If I could find the row position of the highest value in the last six rows, all I have to do is find the lowest low in that range. In other words, if I can find that the high point was 3 bars ago, all I have to do is find the lowest value in the last 3 bars.
=IF(AND(C3<C2,C2>=C1),MIN(B3:B7),0) column c = highs, column b = lows. assuming you place this in row 2, it means: if the next high is lower than this high, and this high is higher than or equal to the previous high, then find the minimum low of the next five lows, otherwise output is zero. not sure if this is what you mean.
sure. strangely, i remember posting this: =IF(AND(C4<C3,C3>=C2),MIN(B3:B7),0) and not what is posted in my response. the above formula matches the worded logic of that post. you could expand it further to match your desired criteria. for example, if you want to check if this high is equal to or higher than the previous two highs, and the next two highs are lower than this high, you would write: =IF(AND(C4<C3,C5<C3,C3>=C2,C3>=C1),MIN(B3:B7),0) this might be more towards the filter you are looking for. sorry that the last post was corrupted. edit: i see what is going on. i don't know if it happening on your browser, but the forum is chopping down the formula, as it is looking for < > tags. the first one reads (w/out the spaces): =IF ( AND ( C4 < C3 , C3 >= C2 ) , MIN ( B3 : B7 ) , 0 ) and the 2nd: =IF ( AND ( C4 < C3 , C5 < C3 , C3 >= C2 , C3 >= C1 ) , MIN ( B3 : B7 ) , 0 )