Lookup Maximum between Range of Dates

[John Taylor]

Hello,

I would appreciate some help in developing two formulas to look up the
maximum value (Stock Market Index) between a range of dates, and show the
date and the matching Index value.

If the dates are in ascending order in Column A, and the indices are in
Column B, if I put the first date in cell J3 and the second date in cell J4,
what formulas will give me the maximum Index value between those two dates
and the date on which it was recorded?

Jumping the gun a bit, but if there a several dates with the same maximum is
it possible to have the formula return that figure and all the dates on
which they were recorded?

For example (dates are in dd/mm/yyyy format):

Col A Col B
02/01/2007 5670.2
03/01/2007 5649.3
04/01/2007 5584.8
05/01/2007 5572.0

12/09/2007 6220.3
13/09/2007 6230.6
14/09/2007 6306.8
17/09/2007 6271.4

27/12/2007 6350.9
28/12/2007 6339.9
31/12/2007 6339.8

If I enter 01/01/2007 in J3 and 31/12/2007 in J4, I would like the formulas
to return:

27/12/2007 and 6350.9

Any help will be most appreciated.

Regards,

John

 

[Pete_UK]

First of all, put this array* formula in (say) K5:

=MAX(IF((A$1:A$500>=J3)*(A$1:A$500<=J4),B$1:B$500,0))

I've assumed that your data occupies 500 rows - adjust as necessary.
This will find the largest between the two dates.

* As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than just the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Then you can put this formula in (say) J5:

=INDEX(A$1:A$500,MATCH(K5,B$1:B$500,0))

This will give you the date when the maximum in K5 occurred, so format
the cell as a date otherwise you will have a number approx 39400
displayed. If you have more than one date with the same maximum, this
formula will find the first of them.

Hope this helps.

Pete

 

[John Taylor]

Pete,

Thanks a million! Great help, your formulas do exactly what I need.

All my earlier attempts had failed, largely due to the fact (I think) that I
was making the problem a lot more complicated than it really was. It's
extremely useful to be able to get someone from "outside" to take a fresh
look at a problem.

Once again, thanks.

Regards,

John

First of all, put this array* formula in (say) K5:

=MAX(IF((A$1:A$500>=J3)*(A$1:A$500<=J4),B$1:B$500,0))

I've assumed that your data occupies 500 rows - adjust as necessary.
This will find the largest between the two dates.

* As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than just the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Then you can put this formula in (say) J5:

=INDEX(A$1:A$500,MATCH(K5,B$1:B$500,0))

This will give you the date when the maximum in K5 occurred, so format
the cell as a date otherwise you will have a number approx 39400
displayed. If you have more than one date with the same maximum, this
formula will find the first of them.

Hope this helps.

Pete