problems MAX'ing a range

[Bill Parker]

Hi

I've got a set of records (say, for April 2004) that I want to get the MAX
value for each day...

Worksheet 1:

1/4/2004 2
1/4/2004 4
1/4/2004 1
..
..
..
2/4/2004 10
2/4/2004 5
..
..
..
30/4/2004 6
30/4/2004 7

I need Worksheet 2 to end up like this...

1/4/2004 4
2/4/2004 10
..
..
..
30/4/2004 7

I've tried using

=MAX(IF('WkSheet1!A1:A3000=DATE(2004,4,1),Wksheet1!B1:B3000))

but it only seems to retunr the highest in the whole range (umm, 10 in the
eaxample above, I guess)

Can anyone help me out? I'm not very experienced at ranges so I'm having
difficulty understanding what I'm doing anyway...

Cheers

Bill

 

[Don Guillett]

Did you see my answer in worksheet functions. We prefer that you not
multipost or even cross post as one is usually sufficient.

 

[Guest]

Bill

If Worksheet 1 is as you show and Worksheet 2 contains a single date for each day of the month, you should be able to apply a MAX formula to each day on Sheet 2 pointing only to those cells related to a given day. For example
MAX(A1:A20) [where A1 through A20 are all the entries for 01/01/2004
MAX(A21:A35) [where A21 through A35 are all entries for 01/02/2004
etc, etc.