asked on

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

zorvek (Kevin Jones)

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

Amick

Create a pivot table
Row Label = Date
Column Fields = Score 1 and Score 2
Data Items = Max Score 1 and Min Score 2

zorvek (Kevin Jones)

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

ASKER

@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?

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

brettr,

I am glad to see that you got a suitable answer.

I am not sure if you tried Amick's PivotTable suggestion or not, but a PT would have automatically extracted the distinct dates out of the source data for you, thus saving you step.

PT's may seem daunting at first if you've never used them, but once you get over that hurdle you will wonder how you ever got by without them :)

Patrick