Ranking, ordering

**peri1224** · 01-30-2010, 12:58 AM

A number of columns (B to U) contain names, details and scores of players. The A column contains the ranking, e.g. 1 to 100.
If a player (row) is deleted, that ranking number is also gone. Is there a way to make the A column always maintain the integrity and completeness of the numbering, e.g. if rank (row) 16 is deleted, the range of B17:U100 moves one row up and the former 17 is now 16? And if a new row is inserted, the ranking numbers after the insertion move up one row and a new number is added at the end?

2.
There are 6 columns of scores, P to U. But out of these 6 scores, only the best 4 count for the ranking. The two worst (highest) scores are dropped. Is there a way to automate the process by selecting only the 4 best (lowest) numbers to be summed into the Total column (C), which in turn determines the rankings in the A column?*
And then can the whole range B2:U100 automatically be sorted according to the ranking in column (A)?

*It is possible that there are 3 or 4 bad scores, all with the same value. In that case only 2 could be discarded, and 1 or 2 would be summed into the ranking cell.

**DonkeyOte** · 01-30-2010, 03:59 AM

It would be best to post a sample file.

Question 1 - should happen automatically but physically deleting & inserting rows is generally not a great idea (IMO) - often leads to #REF! errors

Question 2 - you would be best served calculating the bottom 4 scores in a separate column - eg:

Please Login or Register  to view this content.

and ranking based on V17 rather than conducting the above within the Rank-esque formula

**peri1224** · 01-30-2010, 01:50 PM

Originally Posted by DonkeyOte

Question 1 - should happen automatically but physically deleting & inserting rows is generally not a great idea (IMO) - often leads to #REF! errors

Agree about the physical deleting or inserting, but if players come or leave, what else can you do?
And if you delete one, including the rank, it is not automatically corrected. But it would be nice if there was an automatic way to do that, besides deleting only cells B to U and shifting the other cells up, thus not touching the ranking numbers at all.
But this is the smaller part of the whole question.

Originally Posted by DonkeyOte

V17: =SUM(SMALL(P17:U17,{1,2,3,4}))

This formula is what I imagined should exist and it works nicely... if the ranks are in contiguous columns. But if the rank columns are interspersed with other columns that contain non-rank info, and the formula is adapted, e.g. =SUM(SMALL(e17,g17,i17,k17,m17,n17,{1,2,3,4})) he complains that there are too many arguments. Is there also a trick for that?

And before I forget, those rank cells that were not included in the ranking, i.e. the two highest ones, should be highlighted to make it immediately clear that these are the ones excluded. Doing this manually is quite tedious. What would the conditional formatting formula be for that one?

**DonkeyOte** · 01-30-2010, 02:58 PM

Originally Posted by D.O

It would be to best to post a sample file

A file speaks a dozen posts, particularly if certain key facts happen to have been omitted in the narrative (eg non-contiguous ranges)

Regards SMALL - the function accepts non-contiguous ranges:

Please Login or Register  to view this content.

(are you sure the reference to N17 is correct, one would expect O17)

**peri1224** · 01-31-2010, 03:29 AM

The additional parentheses work nicely for non-contiguous columns.

Sample file:
Non-attendance = 0 score = 25 ranking points, shaded grey.

Because I didn't have that nice SMALL formula yet, had to put the auxiliary P to U columns to facilitate manual scoring. But with the SMALL formula ok for non-contiguous columns, P:U can be done away with, and the total points calculated directly into the C column.

The highest scores are highlighted yellow (overriding the grey shading) and manually set to 0 to reflect the 4 lowest points. That shading can hopefully be automated, too.

Those players that leave for good have to be deleted manually (row), or new ones added. That disturbs the integrity of the A column.

**WHER** · 01-31-2010, 07:01 AM

Peri1224
To give you some extra inspiration, here's a (compacted example) version of of similar workbook that i use for ranking participants in a sports event. It has 2 seperate parts, a data entry part and a ranking part. In the full size version they are on seperate sheets, but to make the example easier to read i placed them one under the other on the same sheet. To "delete" or replace a player, clear the contents of the relevant part of that row in the data entry area (lightblue D4:M14), never delete a row!! In the yellow ranking area the gap will be filled automatically.
I specially modified the data entry range to be non-contigeous, in the original it's one contigeous range.

**peri1224** · 01-31-2010, 08:25 AM

Thank you for this sample. It is a bit complex for me and I have trouble following the formulas. Besides, I have already a data input area that won't fit in here. So unless I can't get the existing system fixed (almost there), I'd rather keep yours in reserve first. Cheers.

**peri1224** · 01-31-2010, 11:27 AM

The formula for calculating the 4 best out of 6 is ok now.
=SUM(SMALL((E3,G3,I3,K3,M3:O3),{1,2,3,4}))

Getting the formatting right is still open. Can do the grey shading with these 2:
=AND($D4=0,$E4=25) applies to $D$4
=AND($D4=0,$E4=25) applies to $E$4
copied down, and repeated for all 6 column pairs. Probably there is a way to do one formula for the whole range. But I don't know it.

The yellow shading for those scores (highest) that are ignored creates more problems.
When adapting the SMALL formula
=SMALL((E3,G3,I3,K3,M3:O3),{1,2,3,4})
in conditional formatting, he complains that :
You may not use unions, intersections or array constants for Conditional Formatting criteria.
Is there a way around that?

**peri1224** · 02-02-2010, 07:09 AM

Let's close this item, as the ranking and ordering portion is solved. And anyway this post seems to be dead now. A new one will be opened for the conditional formatting question.