Conditional MAX Statement?

I'm trying to find a maximum (positive) difference between a range of values, with certain constraints.

Column A contains the values; Column B contains the date on which those values were recorded:

Column A Column B

22 May 10 2007
19 May 17 2007
44 May 24 2007
17 May 21 2007

The constraint is that for any solution, the lower value must have been recorded at an earlier date than the higher value. Therefore, while this appears to work:

=(MAX(A1:A4)-(MIN(A1:A4)))

it returns a value of '27' the difference between '44' and '17'. What I want is to put in a conditional which prohibits this and instead comes up with '44-19', as this would satisfy my requirement that the higher value '44' occurred on a later date than the lower '19'--even though the difference is only 25 and is thus not truly the 'maximum difference'.

Is there any way to implement such a condition? I'm interested in a general solution--the fact that the conditional is assessed against a 'date' isn't all that critical per se. I just want to know if its possible to create a conditional MAX statement that uses a second column as the constraint in this way.

Thanks for any ideas!

I would suggest:

=MAX(A1:A4)-MIN(IF(B1:B4<INDEX(B1:B4,MATCH(MAX(A1:A4),A1:A4,0)),A1:A4))

Confirmed with Ctrl+Shift+Enter as it's an array formula.

What confuses me is in your example 17 is at an earlier date than 44 so that seems right.

Dom

Perhaps?

=MAX(A1:A4)-MIN(A1:INDEX(A1:A4,MATCH(MAX(A1:A4),A1:A4,0)))

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save