Forum Discussion
IF Function
So if your data--person's name and time--are in adjacent cells, all organized as an Excel Table, you can use the relatively new SORT function to sort them into a ranked order, after which that SEQUENCE function could be used.
Do you have a sample of the spreadsheet you're going to be using, dummy names please so as not to violate privacy. If so, please post it.
Yes, I agree with mathetes.
If you don't yet have a rank formula, you can use one of the RANK functions, my best guess would be RANK.AVG(), to get a set of ranked values according to your times. For example (I set my formulas in B1:B10):
=RANK.AVG(A1,$A$1:$A$10,1)
Then, to apply a value to those ranks, you could use the SEQUENCE function. The below formula would assume you have 10 ranks to assign values to, and those ranks are in B1:B10 (I entered formula in C1, which spilled down to C10):
=SEQUENCE(10,,30,-3)
You could then spot-check ranked values in one of two ways: by original value (time) and rank (rank formula).
By time, assuming a time value is in cell F1 and formulas placed with the above assumptions:
=LOOKUP(F1,A1:A10,C1#)
By rank, assuming the value is in cell G1 and formulas placed with the above assumptions:
=LOOKUP(G1,B1:B10,C1#)
Note the "C1#" reference is slightly different from the rest, as we're saying "get the entire spilled range which starts in cell C1". This range would need to match the size of the other ranges referenced.
HTH