Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    array to calculate maximum and date of maximum (2003)

    Edited by HansV to present data in table format

    I have data in the following format

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Monitor site</td><td>date</td><td>Water Level</td><td align=center>2</td><td>WE-CYC-815s</td><td align=right>10/4/2004</td><td align=right>69.08</td><td align=center>3</td><td>WE-CYC-815s</td><td align=right>10/18/2004</td><td align=right>66.65</td><td align=center>4</td><td>WE-CYC-815s</td><td align=right>11/5/2004</td><td align=right>65.58</td></table>
    And need to pull out the maximum water level for the following table.

    <table border=1><td></td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>K</td><td align=center>1</td><td>Lookup value</td><td>Monitor Well</td><td>Maximum Water Level (ft. NGVD)</td><td>Date</td><td align=center>2</td><td> WE-CYC-815s</td><td>815s (NGS)</td><td align=right>88.74</td><td align=right>8/1/2005</td><td align=center>3</td><td> WE-CYC-816s</td><td>816s (SR52S)</td><td align=right>77.14</td><td align=right>9/9/2004</td><td align=center>4</td><td> WE-CYC-821-s</td><td>821S</td><td align=right>75.68</td><td align=right>9/9/2004</td></table>
    I am using (for cell j3)

    {=MAX(IF($A$2:$A$7821=H3,$C$2:$C$7821,""))}

    To pull out the maximum value.

    What I can

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: array to calculate maximum and date of maximum (2003)

    In D2, enter the formula <code>=A2&"|"&C2</code> and fill down to D7821.
    Enter the following formula in K2:
    <code>
    =INDEX($B$2:$7821,MATCH(H2&"|"&J2,$D$2:$D$7821,0))
    </code>
    and fill down to K7821.

    Note: the character "|" is rather arbitrary, it can be replaced with another character that is unlikely to occur in columns A and C.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: array to calculate maximum and date of maximum (2003)

    Hi,

    In K2 you could use:
    =SUM(IF(($A$2:$A$7821=H2)*($C$2:$C$7821=J2),$B$2:$ B$7821,))/SUM(IF(($A$2:$A$7821=H2)*($C$2:$C$7821=J2),1,))
    entered as an array formula (<Ctrl-Shift-Enter>). Format the result as a date and copy down to K4.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: array to calculate maximum and date of maximum (2003)

    Thank you once again.

    The breadth of knowledge in this forum is amazing (and a great resource for people like me who think that Excel should be able to do something, but can't figure it out).

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: array to calculate maximum and date of maximum

    >>The breadth of knowledge in this forum is amazing...

    Not to mention the depth, too!
    I second your remarks.

Posting Permissions

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