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
Perhaps an easy way is to create a table with dates ans sites and then use this ARRAY formula.
Formula:
Please Login or Register to view this content.
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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
Not sure that i get your goal. Can you pls explain a little more?..As an addition, is it possible to have the Site Number listed above the yellow columns as dynamic?
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
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.
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
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
Thanks guys.
You each solved my problem by way of your contribution. Very fast and efficient too.
You are welcome and thanks for the feed back.
I second that!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks