Finding max score by day

brettr
brettr used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Max:

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

Min:

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

Kevin
Top Expert 2008

Commented:
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

Commented:
Create a pivot table
Row Label = Date
Column Fields = Score 1 and Score 2
Data Items = Max Score 1 and Min Score 2
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Top Expert 2008

Commented:
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

Author

Commented:
@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?
Top Expert 2008
Commented:
The easiest way is to use the Advanced Filter feature. To start, copy the dates to a separate worksheet, follow the instructions below, and then copy the resulting unique dates to your max/min table.

The steps below illustrate how to filter a table's records so that only unique records are visible, where uniqueness is defined by one or more contiguous key columns. Additional steps are provided to remove the duplicate records from the table permanently. These steps are for Excel 2003 and earlier. For Excel 2007 see the section "Excel 2007" below.

To create a list of unique records based on one or more key columns, first sort the table so that the desired unique records appear first in each same key sequence. If more than one column is to be used to define each record's uniqueness but they are not adjacent to each other, move them such that they are in consecutive order. Follow these steps to hide the duplicate records.

Select the key columns including the headers and choose the menu command Data->Filter->Advanced Filter.

Check "Unique records only" and click OK.

The table can now be viewed or copied to another location. To remove the duplicates records from the table permanently, do the following steps.

Choose the menu command Edit->Office Clipboard to display the Office Clipboard.

Select the entire table excluding the header row and press CTRL+C to copy them to the clipboard.

Choose the menu command Data-Filter->Show All.

Select the entire table excluding the header row and press DELETE.

In the Office Clipboard click the dropdown next to the top entry and select Paste.

The resulting table is just the filtered records.

Excel 2007

To remove duplicates in Excel 2007 and later, select the table, navigate to the Data tab, find the Data Tools group, click Remove Duplicates, select the columns to use to identify duplicate rows, and click OK.

Kevin
Top Expert 2010

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial