# Finding max score by day

I have four columns:

Date, Time, Score1, Score2

How do I get max for Score1 and min for Score2 by day?  Each day will have at least one max for Score1 and one low for Score2.  There are 50 rows per day.
zorvek (Kevin Jones)

Max:

=MAX(IF(A1:A100=DATEVALUE("5/1/2011"),C1:C100,""))

Min:

=MIN(IF(A1:A100=DATEVALUE("5/1/2011"),D1:D100,""))

Kevin
You can put the dates in column F and use these formulas to provide the max and min for each date:

Max (column G):

=MAX(IF(A\$1:A\$100=F1,C\$1:C\$100,""))

Min (column H):

=MIN(IF(A\$1:A\$100=F1,D\$1:D\$100,""))

Kevin
Create a pivot table
Row Label = Date
Column Fields = Score 1 and Score 2
Data Items = Max Score 1 and Min Score 2
All the above formulas are array formulas. Enter by pressing CTRL+SHIFT+ENTER.

Also note that you will have to edit the ranges for your situation.

Kevin
brettr

@zorvek:

Thanks.  That is working.

How can I remove duplicates from the date column?  After that, I should be able to copy/paste the array formula right?
zorvek (Kevin Jones)

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
brettr,