brettr

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.

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.

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

Max (column G):

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

Min (column H):

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

Kevin

Create a pivot table

Row Label = Date

Column Fields = Score 1 and Score 2

Data Items = Max Score 1 and Min Score 2

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

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

Kevin

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?

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

membership

This solution is only available to members.

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

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

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

=MAX(IF(A1:A100=DATEVALUE(

Min:

=MIN(IF(A1:A100=DATEVALUE(

Kevin