Results 1 to 7 of 7
Thread: date of minimum value

20091111, 15:33 #1
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
I have data in 2 columns, with date in column C and values in column D. I am using the following array formula to determine the minimum for each water year. Column G has the water year reference.
{=MIN(IF(YEAR(DATE(YEAR($C$3:$C$333),(3+MONTH($C$3 :$C$333)),1))=G3,$D$3:$D$333))}
How do I
(1) Determine the date of each year’s minimum value?
(2) Determine the cell address of each year’s minimum value?
Thanks!

20091111, 15:51 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
See the attached workbook: [attachment=86456:FindDates.xls]

20091112, 06:00 #3
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
In follow Hans' table :
1] The Min value of each year ( Helper column ), H3 enter the array formula and copied down :
{=MIN(IF(TEXT($C$3:$C$47,"yyyy")=G3,$D$3:$D$47))}
2] The cell address ( Row number ) of each year’s minimum value, I3 enter the array formula and copied down :
{=MAX((TEXT($C$3:$C$47,"yyyy")=G3)*($D$3:$D$47=H3)*ROW($3 :$47))}
3] Date of each year’s minimum value, J3 enter the formula and copied down :
=INDEX(C:C,I3)
Regards
Bosco

20091116, 08:44 #4
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
thanks to both of you

20091207, 00:41 #5
 Join Date
 Dec 2009
 Location
 Woodbury, GA
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts

20091207, 01:28 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
The formula is about "water years" where the "water year" 2010 starts on October 1, 2009. So 3 months are added to a date to get the correct "water year". For example:
Calendar date August 20, 2007. Add 3 months to get November 20, 2007. The water year is 2007.
Calendar date October 13, 2008. Add 3 months to get January 13, 2009. The water year is 2009.

20100125, 14:29 #7
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts