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

    max/min values and corresponding times (2003)

    I am trying to determine the maximum and minimum daily values and their times. The spreadsheet data is organized as follows:


    A B C D
    1 Date / Time DTW in MW11D elevation moving average
    2 6/26/2007 11:22 230.39 4776.098
    3 6/26/2007 11:37 230.39 4776.105
    4 6/26/2007 11:52 230.40 4776.086
    5 6/26/2007 12:07 230.41 4776.083
    6 6/26/2007 12:22 230.38 4776.112 4776.100
    7 6/26/2007 12:37 230.38 4776.115 4776.105
    8 6/26/2007 12:52 230.36 4776.131 4776.110
    9 6/26/2007 13:07 230.38 4776.108 4776.119
    10 6/26/2007 13:22 230.36 4776.129 4776.120
    11 6/26/2007 13:37 230.37 4776.116 4776.122
    12 6/26/2007 13:52 230.37 4776.125 4776.125
    13 6/26/2007 14:07 230.34 4776.147 4776.133
    14 6/26/2007 14:22 230.34 4776.150 4776.134


    H I J K L
    1 Date Min value min time Max value max time
    2 6/26/2007 4776.100 12:22:47 PM 4776.203 7:37:47 PM
    3 6/27/2007 4776.142 8:52:47 AM 4776.247 7:07:47 PM
    4 6/28/2007 4776.204 7:52:47 AM 4776.303 6:52:47 PM
    5 6/29/2007 4776.254 10:37:47 AM 4776.355 11:37:47 AM

    The formulae (thanks to a previous post) are:

    Min value: {=MIN(IF(INT($A$2:$A$953)=INT(H2),$D$6:$D$953,"")) }
    Min time: {=MIN(IF($D$6:$D$953=I2,$A$6:$A$953,""))}
    Max value: { =MAX(IF(INT($A$2:$A$953)=INT(H2),$D$6:$D$953,""))}
    Max time: { =MAX(IF($D$6:$D$953=K2,$A$6:$A$953,""))}

    My problem lies in that my max and min values are not unique and therefore the times may not correspond to the correct day. I tried the following, but I get an error result.

    Max time: {=MAX(IF(AND($D$6:$D$953=K2,INT($A$2:$A$953)=INT(H 2)),$A$2:$A$953,""))


    Suggestions?
    And Thanks!

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

    Re: max/min values and corresponding times (2003)

    Could you post a sample workbook (zipped if necessary)? I'm too lazy to reconstruct the worksheet from your post <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: max/min values and corresponding times (2003)

    here 'tis
    and thanks

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

    Re: max/min values and corresponding times (2003)

    Try this in I2 (it's an array formula so confirm with Ctrl+Shift+Enter):
    <code>
    =MIN(IF((INT($A$6:$A$953)=G2)*($D$6:$D$953=H2),$A$ 6:$A$953,""))
    </code>
    and this in K2:
    <code>
    =MAX(IF((INT($A$6:$A$953)=G2)*($D$6:$D$953=J2),$A$ 6:$A$953,""))
    </code>
    Fill down to row 11.

Posting Permissions

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