MAX'ing problems...

  • Thread starter Thread starter Bill Parker
  • Start date Start date
B

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 return the highest in the whole range (umm, 10 in the
example 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
 
Try this array formula (use ctrl+shift+enter) to enter or edit. Here d1
contains your date. Copy down.
=MAX(IF($F$1:$F$4=d1,$G$1:$G$4))
 
It gives the same result.... that is, the maximum value found for the whole
month, not just for a specific day.

the data actually consists of about 2800 rows for the whole month with a
varying number of rows per day - so I can't see a way to specify the range -
I need to calculate it somehow.

Bill
 
Did you follow the instruction about entering as an ARRAY formula by using
CSE (hold down the control key, hold down the shift key, touch the enter
key) then you will see { formula }
={MAX(IF($F$1:$F$4=d1,$G$1:$G$4))}
 
Aah... the light dawns....

It's still not quite working for me (CSE gave me {=MAX((... - but wha tthe
hell, the principle is in there.

Many thanks, I'll plug away at this now until I can understand what I'm
doing wrong

Cheers

Bill
 
f1:f4 is the dates
d1 is a cell containg the date for the day you want
g1:g4 is the numbers to find the max of for the day requested.

={MAX(IF($F$1:$F$4=d1,$G$1:$G$4))}
 
Back
Top