date of minimum value

Topic

New Reply

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!

Reply | Quote

Replies

See the attached workbook:

Reply | Quote

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

Reply | Quote

thanks to both of you

Reply | Quote

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!

Great example that I looked at with great interest.  I am perplexed by one piece of the formula though.  Why the 3+Month rather than just Month?

thanks,

Mark Trevithick

Reply | Quote

Why the 3+Month rather than just Month?

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.

Reply | Quote

Great example that I looked at with great interest.  I am perplexed by one piece of the formula though.  Why the 3+Month rather than just Month?

thanks,

Mark Trevithick

I work in ‘water years’ which run from October through September, e.g. 10/1/2009 to 9/30/2010 is water year 2010.
It’s a way to shift by the necessary 3 months.

Reply | Quote