digest
Short Summary
Main recommendation: To rank values across two non-adjacent columns, use RANK.AVG (average ties) or RANK.EQ (shared rank for ties), entering both ranges together as a multi-area reference in parentheses separated by a comma, locked with F4. The video explains that RANK, RANK.EQ and RANK.AVG exist because Microsoft added the dot-convention versions in 2010 and that the plain RANK function is only a compatibility version of RANK.EQ.
Key Points
- Rank a scoring list that spans two separate columns by passing both ranges as one reference, e.g., (range1, range2), instead of selecting just one.
- Use RANK.AVG when ties should average their ranks; in the video's example two tied scores at positions 14 and 15 both receive 14.5.
- Use RANK.EQ when tied values should share the same rank; in the second example the tied scores both receive 14, and the next rank skips to 16.
- The plain RANK function works exactly like RANK.EQ and is kept for compatibility with older spreadsheets.
- Other statistical functions added in 2010 with the dot convention include normal distribution and percentile functions.
Details Worth Noting
- Concrete instructions:
- Type the rank number cell reference first, then a comma, then an open parenthesis.
- Highlight the first range and press F4 to lock it, type a comma, highlight the second range and press F4 to lock it, then close the parenthesis.
- Enter 0 for the order argument to rank the largest value first (descending, e.g., Maximum Time Aloft); enter 1 to rank the smallest value first (ascending, e.g., Boomerang Fast Catch).
- Press Ctrl+Enter, double-click the fill handle to copy the formula down, then copy and paste values to a new location if needed.
- To fill both ranges at once, select the first range, hold Ctrl and select the second range, type the formula, and press Ctrl+Enter to populate all highlighted cells; the relative cell reference adjusts automatically.
- Example results: Tim ranked first with 76.48 seconds; Phil and Kip tied at 31.8 seconds.
- In the Fast Catch example, Alice's 18.3-second time ranked first, and Tim, Phil and Sue tied at 25.4 seconds for seventh place, so the next ranks skipped eight and nine, going straight to ten.
- The presenter notes that boomerang scoring uses RANK.AVG, which makes it the appropriate choice for that sport's tie handling; other ranking systems may differ.
Bottom Line
Use RANK.AVG for averaged ties and RANK.EQ for shared ties, with both ranges entered together and locked so the formula works across non-adjacent columns.
