Every tied record gets a different rank
Row position is not a tie breaker in this task. Equal scores must share the declared competition or averaged rank.
Rank records without sorting the source
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Keep A2:B7 in their original positions. Fill C2:C7 with descending competition ranks and D2:D7 with ascending competition ranks; ties share the first occupied position and leave gaps. E2:E7 and F2:F7 instead average the occupied tied positions in descending and ascending order. All six finite numeric scores participate, including zeroes and negative values; this exercise does not treat numeric text, Booleans or blanks as scores. I4 and I5 return maximum/minimum; I6 and I7 return kth largest/smallest using the integer H2 from 1 through 6, with duplicates occupying separate positions. Copy within each declared rank column only. Each summary formula has a separate job and must not be filled over. Source identifiers remain nonempty ASCII text and scores stay within ±1,000,000.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: C2, C3, C4, C5, C6, C7, D2, D3, D4, D5, D6, D7, E2, E3, E4, E5, E6, E7, F2, F3, F4, F5, F6, F7, I4, I5, I6, I7. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| C2 | — | No formula |
| C3 | — | No formula |
| C4 | — | No formula |
| C5 | — | No formula |
| C6 | — | No formula |
| C7 | — | No formula |
| D2 | — | No formula |
| D3 | — | No formula |
| D4 | — | No formula |
| D5 | — | No formula |
| D6 | — | No formula |
| D7 | — | No formula |
| E2 | — | No formula |
| E3 | — | No formula |
| E4 | — | No formula |
| E5 | — | No formula |
| E6 | — | No formula |
| E7 | — | No formula |
| F2 | — | No formula |
| F3 | — | No formula |
| F4 | — | No formula |
| F5 | — | No formula |
| F6 | — | No formula |
| F7 | — | No formula |
| I4 | — | No formula |
| I5 | — | No formula |
| I6 | — | No formula |
| I7 | — | No formula |
Change an actual source value
Keep all six records in their original positions. Scores are stored finite numbers within ±1,000,000; numeric text, Booleans and blanks stay unapplied. The k control is an integer 1–6. Repeated scores share competition rank, while averaged rank divides their occupied positions.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Compare both rank directions and averaged ties beside the untouched source, then select configurable kth extrema.
THE QUICK ANSWER01
Compare both rank directions and averaged ties beside the untouched source, then select configurable kth extrema.
ƒx
=RANK.EQ(B2,$B$2:$B$7,0)For the example data
C2 3D2 3E2 3.5F2 3.5C3 1D3 6E3 1F3 6C4 3D4 3E4 3.5F4 3.5C5 5D5 2E5 5F5 2C6 6D6 1E6 6F6 1C7 2D7 5E7 2F7 5I4 90I5 -5I6 80I7 0
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Competition and averaged ties answer different questions
Competition rank counts values strictly above or below the current observation, then adds one. Average rank assigns the mean of all positions occupied by the tied group. Both retain a result for every original record.
Sort a comparison copy only when explaining kth values
The assessment compares source scores directly and uses a separate ordered copy for extrema. The original worksheet rows stay in place. Zero and negative scores are observations in this bounded numeric population.
Fill has a declared scope
Each rank column locks the population while the current-row score moves. The four independent summary cells are outside those scopes; filling a MAX formula over a LARGE formula would destroy its meaning.
Read the full step-by-step walkthrough
- Keep the original record positions
Ranking writes adjacent results; it does not move the source records. Inspect the tied, zero and negative observations before choosing a direction.
- Rank higher scores first
Enter C2 and fill C2:C7. The current score reference moves down; the six-score comparison population stays locked. Tied scores share the first occupied position, so the next different score skips those positions.
- Reverse the direction explicitly
D2 uses ascending order. The smallest observation becomes rank 1. This does not mean reversing the rows or simply subtracting the descending rank when ties exist. Fill only D2:D7.
- Average the occupied tied positions
E and F use RANK.AVG. A tied pair occupying descending positions 3 and 4 receives 3.5, while RANK.EQ gives both rank 3. With six equal scores, every average rank is 3.5. Fill each column separately.
- Separate an extreme from its source position
I4/I5 return the largest/smallest numeric observation, not a record number or a sorted table. The source remains unchanged.
- Choose kth extrema including repeated values
H2 controls I6/I7. Repeated values occupy repeated sorted positions: the second-largest value can equal the maximum. Keep these summary formulas separate from rank-column filling.
Things to watch
The source is sorted to make ranks
Keep every source record in place and place rank results beside it.
k selects the kth distinct value
LARGE and SMALL count repeated observations. This task does not request distinct extrema.
About this example
Original bounded rank/horizontal lookup tasks; browser and current workbook checks are separate from native-client recalculation/rendering/edit-save certification.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.