Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    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!

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

    Attached Files Attached Files

  3. #3
    2 Star Lounger
    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

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks to both of you

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jepalmer View Post
    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


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Mark Trevithick View Post
    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.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Mark Trevithick View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •