# Thread: max/min values and corresponding times (2003)

1. ## 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. ## 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>

here 'tis
and thanks

4. ## 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
•