0

I'm banging my head against a wall trying to find a way to highlight a swimming time compared to results from past swim meets.

  • Column A is my swimmers time
  • Column B is the event
  • Column D is the time for a few places within that event
  • Column E-W is the time result for that place of that event per college conference (row 1 = conference)

Goal - Highlight the nearest place my swimmers time would put them in each conference.

Example - My swimmers 50 Free time of 24.85 would put them closest to 32nd place in the BIG12, 1st place in the SAAC and 9th place in the PCSC.

A copy of my data is below. Any help would be greatly appreciated!

https://docs.google.com/spreadsheets/d/1KNpf8Yq15q7ezrXnQMIB6V866K7GAFMLTkmOjBtpf2U/edit?usp=sharing

2
0

Try this formula :

=if(isna(query({$A$2:$B$64,indirect(address(2,vlookup(L$1,$R:$S,2,false ),1,1)&":"&address(counta($B:$B),vlookup(L$1,$R:$S,2,false ),1,1))}, "Select Col1 where Col2 = '"&$K2&"' and Col3 <= '"&$J2&"' order by Col3 desc limit 1 ",)),1,query({$A$2:$B$64,indirect(address(2,vlookup(L$1,$R:$S,2,false ),1,1)&":"&address(counta($B:$B),vlookup(L$1,$R:$S,2,false ),1,1))}, "Select Col1 where Col2 starts with '"&$K2&"' and Col3 <= '"&$J2&"' order by Col3 desc limit 1 ",))

Refer the screenshot below: copy the formula into cell L2, and copy it across to column P, and drag down for as many swimmer's times as are needed.

LOGIC

  • Conference Names/Columns Table

    • Columns S and T display the names of the conferences and the column number in which the conference results are found
    • =transpose(C1:G1) = Column names
    • =arrayformula(row(B2:B6)+1) = Column number of the relevant conference results (BIG12=C=3, SAAC=D=4, etc)
    • The column numbers are used to create dynamic ranges for the query
  • Query Range

    • $A$2:$B$64 = Place and Event
    • indirect(address(2,vlookup(L$1,$R:$S,2,false ),1,1)&":"&address(counta($B:$B),vlookup(L$1,$R:$S,2,false ),1,1)) = relevant Conference Times
      • INDIRECT() returns a range contained in a string
      • ADDRESS() returns a cell reference as a string; in this case two cells are joined by a colon &":"& to create a range
      • address(2,vlookup(L$1,$R:$S,2,false ),1,1)&":"&address(counta($B:$B),vlookup(L$1,$R:$S,2,false ),1,1) creates a dynamic range
      • part1= returns the starting cell of the relevant conference results by taking the conference name in L1:P1 and referencing the Conference Names/Columns Table to yield the column number.
      • part2= returns the ending cell of the relevant conference results by counting the number of rows in the Events column (B); the column number is the same as part1.
  • Query Criteria

    • Select Col1 where Col2 = '"&$K2&"' and Col3 <= '"&$J2&"' order by Col3 desc limit 1
    • Col1 = Place (Column A)
    • where Col2 = '"&$K2&"' and Col3 <= '"&$J2&"' combines matching the Conference Event to the swimmer's event (in Cell K2) AND the the conference time is less than or equal to the swimmer's time (in Cell J2).
  • IF(ISNA()...

    • Where the swimmer's time is less than a Conference first place time (such as 23.91 compared to SAAC first place of 24.46), the query returns #N/A. IF(ISNA() is used to assign a first place result in those cases.

Sample data

screenshot

Note: MVCC 50FREE last place time was an error; must be greater than other times in the race.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.