Difference between value and max of a range as long as different cell value increases

[garethlittle]

I've been trying to figure this out on my own and have made a bit of progress but I've hit a bit of a wall and am a little confused about what function I should be concentrating on, SUMIF, OFFSET, MATCH etc. So I'd greatly appreciate it if someone could point me in the right direction.

What I'd like to achieve is to be able to see the difference in two prices until price reverses.

Untitled-1.jpg

When 1 (OPEN) is less than 3 (SMA20) and 2 (CLOSE) is more than 3
The difference between 4 (OPEN) and 5 (HIGH) is shown, 5 being the highest number beginning from the above condition until 6 (CLOSE) is less than 7 (SMA20)

What kind of functions should I narrow my search to in order to achieve this? 4 will always be directly under 1 but 5 won't always be in the same place, depending on whether prices reversed quickly or not. I know I can use =MAX but I'm struggling with the need for it to be a) conditional and b) different possible lengths of values.


Any help at all greatly appreciated.

Gareth

[XLent]

It's not really clear what your expected output is - i.e. are you comparing the High & Low for the row in which the Max High is found, or comparing the Max High to each of the Lows (within range), and aggregating the results?

As a starter, the below would isolate the Max High but without knowing the answer to the above, it's hard to provide the remainder....

Please Login or Register  to view this content.

[garethlittle]

Thanks for your quick reply, sorry my original message wasn't clear. I'm aiming to compare a specific OPEN value (B1774 in the picture) to what Excel would determine to be the highest HIGH value, again in the picture C1776.

But your solution gets me 99% of the way there, thank you so much! The only thing I need to amend to the formula is to then subtract the initial OPEN value from the HIGH so I can see the difference between the two, I think I can muddle through and work that out.

Thanks so much again,

Gareth