Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Maximum between Range of Dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Maximum between Range of Dates
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 On Jan 31, 11:16*am, "John Taylor" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Maximum between Range of Dates
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 "Pete_UK" wrote in message ... 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 On Jan 31, 11:16 am, "John Taylor" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimum and maximum date lookup | Excel Worksheet Functions | |||
Vlookup using dates in the lookup range | Excel Worksheet Functions | |||
maximum lookup functions | Excel Discussion (Misc queries) | |||
Lookup based on range of dates | Excel Worksheet Functions | |||
find maximum of two values in an array with same lookup value | Excel Discussion (Misc queries) |