Auto Sort Rankings in a Different Table

I prefer to add a small amount based on the row number. While the chance of RANDBETWEEN giving the same number for two rows is so astronomically small it probably doesn't matter, the chance is zero with ROW.

The forums were just updated and I cannot post a screenshot so I'll try to describe my example document:

Table 1

C4:C13 are names

D4:D13 are the average scores

E4 = ROUND(D4,4)+ROW()/10^6 which makes the scores unique

Fill down from E4 to E13 to complete the column

Table 2

B4 =LARGE(Table 1::E$4:E$13,ROW()−3) puts the scores in rank order

C4 = LOOKUP(B4,Table 1::E$4:E$13,Table 1::C$4:C$13) looks up the names

D4 = LOOKUP(B4,Table 1::E$4:E$13,Table 1::D$4:D$13) looks up their actual scores

Fill down to row 13 to complete the columns

The formula you use in Table 1 to make the scores unique will depend on what your original "average score" values look like. I assumed they were small numbers with up to 4 decimal places. I also assumed you would not go past row 99 (so that ROW/10^6 will not interfere with the first 4 decimal places)

I prefer to add a small amount based on the row number. While the chance of RANDBETWEEN giving the same number for two rows is astronomically small, the chance is zero with ROW. However, with ROW, lower people in the table will always have a slight bit more added to their scores. If you really want it to be random who comes first when there are repeats in the scores, use RANDBETWEEN as suggested by Ian.

The forums were just updated and I cannot post a screenshot so I'll try to describe my example document:

Table 1

C4:C13 are names

D4:D13 are the average scores

E4 = ROUND(D4,4)+ROW()/10^6 which makes the scores unique

Fill down from E4 to E13 to complete the column

Table 2

B4 =LARGE(Table 1::E$4:E$13,ROW()−3) puts the scores in rank order

C4 = LOOKUP(B4,Table 1::E$4:E$13,Table 1::C$4:C$13) looks up the names

D4 = LOOKUP(B4,Table 1::E$4:E$13,Table 1::D$4:D$13) looks up their actual scores

Fill down to row 13 to complete the columns

The formula you use in Table 1 to make the scores unique will depend on what your original "average score" values look like. I assumed they were small numbers with up to 4 decimal places (but I rounded, just in case). I also assumed you would not go past row 99 (so that ROW/10^6 will not interfere with the first 4 decimal places).

Hi Macenrich,

Add a very small random number to each score. Say RANDBETWEEN(0.0001,0.00001)

Regards,

Ian.

Hi Barry,

My post would have been more clear with a screenshot but I was unable to post one. I did not rank anyone, I used the distinct/unique modified scores only to get all the names in the table and in order nd then I used LOOKUP to bring over each person's actual score. I did say "rank order" in my post but that was referring to rank order of the modified scores, which is a necessary step.

Hi Badunit,

My apologies. Didn't notice you'd already covered that in your solution.

I'll blame it on my rush to complete a reply (with screen shots) before the site went to "We'll be back soon" mode again.

As you noted, the ASC rebirth has not been without several sections of bumpy road. I doubt the ride is over yet. 🤕

Much activity in Community Operations (see "Lounge" item at top right of the thread).

Regards,

Barry

No worries. As I said, a screenshot would have made it more clear.

The issue with both methods presented above is that they misrepresent the ranking of one of a pair (or two of a trio) achieving the same score.

For a sample of four runners, with these times for their race: 15.000, 15:000, 15:010, AND 15:200,

NO runner finished before the first two runners. Both were in first place. Two runners finished ahead of the one with a time of 15:010, putting that runner in third place, and three runners finished ahead of the one with a time of 15:200, placing that runner in fourth place.

The table below uses the same calculations as used by Badunit to ensure that each runner has a distinct 'index value' to be found by the lookup (or match) formula, but retains the correct rank value, determined by the RANK formula.

MATCH and INDEX list the names in rank order, and conditional highlighting is used to highlight rows containing identical ranks.

Formulas:

Table 1 (left)

A: entered data.

B: entered data for the example. the formula used to calculate scores for your table.

C2: RANK(B2,B,largest-is-high)

Calculates the rank of each score within the set of scores in column B.

D2: C2+ROW()÷10^6

Adds a (tiny) amount based on the row to each rank, giving a distinct value in each row, and enabling MATCH or LOOKUP to pull a record for each person.

Table 2

A2: INDEX(Table 1::$C,MATCH(SMALL(Table 1::D,ROW()−1),Table 1::D,0),0)

B2: INDEX(Table 1::$A,MATCH(SMALL(Table 1::D,ROW()−1),Table 1::D,0),0)

The two formulas are identical except for the column reference in bold, telling Numbers which column on Table 1 contains the values to be copied to 'this cell'.

All formulas are entered in the indicated cell, then filled down to the bottom of their column.

The error triangles on Table two flag 'can't find' errors caused by MATCH looking for "nan" ('not a number'??). They can be avoided by:

• Making the table long enough to hold only the number of results to be returned, OR
• Wrapping the formula in these columns with IFERROR(formula,"")

Regards,

Barry

Hi Barry,

My post would have been more clear with a screenshot but I was unable to post one. I did not rank anyone, I used the distinct/unique modified scores only to get all the names in the table and in order nd then I used LOOKUP to bring over each person's actual score. I did say "rank order" in my post but that was referring to rank order of the modified scores, which is a necessary step.

BTW, is everyone else having as much trouble with the forums as I am the past few days?