Results 1 to 4 of 4

20070713, 13:28 #1
 Join Date
 May 2002
 Posts
 413
 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!

20070713, 13:34 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20070713, 13:44 #3
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: max/min values and corresponding times (2003)
here 'tis
and thanks

20070713, 13:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.