Lookup High, Low Valaue Dates
Posted by Riki Phelan on August 19, 2001 11:46 PM
Hi - my problem is how am I able to return the date of high and low values from list of daily values over a year. My list headers are MONTH, DATE and PRICE.
The lists has 52 rows with a unit prices for every Friday of the year.
At the bottom of the list I'd like to extract the the HIGHEST and LOWEST values, and also the corresponding DATES for these values - depending on the month put into cell A60, eg "May". I can extract the values using DMAX/DMIN - but the dates are proving to be impossible! Can you offer any assistance? I'm lost for a formula...
Posted by Alix on August 20, 2001 4:07 AM
Hiya Riki
Try this
=INDEX($B$2:$C$53,MATCH(DMIN(A1:C53,C1,E1:E2),$C$2:$C$53,FALSE),1)
Where your data runs MONTH, DATE, PRICE in columns A to C, column headings in row 1 and data in rows 2 to 53. Criteria for month is in E1 to E2
HTH
Alix
Posted by Riki_Phelan on August 20, 2001 5:04 AM
Re: Lookup High, Low Value Dates
Brilliant! Thanks Alix!
Posted by Aladin Akyurek on August 20, 2001 6:17 AM
A small note though...
Alix & Riki,
If you have on the 1st Friday 4.20 as price and on the 4th Friday the same price and 4.20 happens to be the min of that month, you'll get the date corresponding to the first Friday. Probably not that important, nevertheless a point to watch.
Aladin
=============
Posted by Riki Phelan on August 20, 2001 7:13 AM
Re: A small note though...
Aladin & Alix
Tks Aladin - I have just realised this too, and it is a problem :( In my example, a low of $2.00 is recorded say in both January and again in March - and I select the lowest value in March, the date of the first occurence is returned, that being 19 Jan rather that a March date.
This is proving to be a real stumbling point for me. Any ideas would be greatly appreciated.
& Riki,
Posted by Aladin Akyurek on August 22, 2001 11:16 AM
Re: A small note though...
