MAX IF formula to find various highest values between a range of dates

[Smudge.Smith]

I'm trying to extract the highest recorded speed for each site on a particular day.
Eg:
1/10/13 Site 1 was 92mph
1/10/13 Site 2 was 45mph
1/10/13 Site 3 was 39mph

...and so on through the entire date range.

I thought I had found the answer here http://www.excelforum.com/excel-prog...-criteria.html but I can't make it work as the dates in the example are consecutive whereas mine are groups of the same date which are then consecutive.

Many thanks

[Fotis1991]

Perhaps an easy way is to create a table with dates ans sites and then use this ARRAY formula.

=MAX(IF($A$2:$A$10000=$E3;IF($B$2:$B$10000=F$2;$D$2:$D$10000)))

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[Smudge.Smith]

Thank you very much for your prompt reply. That is just what I'm looking for.
As an addition, is it possible to have the Site Number listed above the yellow columns as dynamic? I have several hundred sites and and would only need to report on those sites between the specified dates every time I run the report.

many thanks

[Fotis1991]

..As an addition, is it possible to have the Site Number listed above the yellow columns as dynamic?

Not sure that i get your goal. Can you pls explain a little more?

[Smudge.Smith]

Fotis1991


As XOR LX said, ideally I would just like a list of unique names being reported against. Instead of having a several hundred columns after Row 2 with the list of site names in, ideally I would just like the site names being reported against for that particular reporting period.
Eg. If during the month of August only Site 1 and Site 2 were visited, the table at F2 should only show Site 1 and 2.
If during the month of September, Site 376, Site 420 and Site 672 were visited, then I would only expect to see these three sites in the table.

[XOR LX]

Hi,

If I understand you correctly, you want a list of unique Site Numbers in row 2, correct?

If so, enter this array formula in F2 and copy to the right until you start to get blanks for the results:

=IFERROR(INDEX($B$2:$B$1693,SMALL(IF(FREQUENCY(MATCH($B$2:$B$1693,$B$2:$B$1693,0),ROW($B$2:$B$1693)-MIN(ROW($B$2:$B$1693))+1),ROW($B$2:$B$1693)-MIN(ROW($B$2:$B$1693))+1),COLUMNS($A:A))),"")

Regards

[Smudge.Smith]

Highest speed comaprison (Stacked chart)2.xls

XOR LX

Thanks for your response ... although I'm not sure that this result is what either of us had in mind

[XOR LX]

Apologies - didn't see you were on 2003. Amend to (in F2):

=IF(COLUMNS($A:A)>SUMPRODUCT(1/COUNTIF($B$2:$B$1693,$B$2:$B$1693)),"",INDEX($B$2:$B$1693,SMALL(IF(FREQUENCY(MATCH($B$2:$B$1693,$B$2:$B$1693,0),ROW($B$2:$B$1693)-MIN(ROW($B$2:$B$1693))+1),ROW($B$2:$B$1693)-MIN(ROW($B$2:$B$1693))+1),COLUMNS($A:A))))

Regards

[Smudge.Smith]

Thanks guys.

You each solved my problem by way of your contribution. Very fast and efficient too.

[Fotis1991]

You are welcome and thanks for the feed back.

[XOR LX]

I second that!