MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How Can I Get Numbers To Colour the Totals Of Four Columns By Rank?

Four of us play cards weekly and I keep records in a numbers spreadsheet.
The scores are entered and added up at the end of the weekly session.
I would like to automatically colour (highlight) those totals according to rank: 1st red, 2nd orange, 3rd green, 4th blue. either the ‘text’ colour or the background of the cells.

—–
Philip

### Comments: 5 Responses to “How Can I Get Numbers To Colour the Totals Of Four Columns By Rank?”

2 years ago

Well, I can think of an easy way that may not get you what you want. So say the scores are in the four cells: B21, C21, D21, E21.
Add a row 22 and put in B22 this formula:
RANK(B21,\$B21:\$E21)
The \$s are important, as you want to hold the columns steady from B to E as you copy and paste this formula into C22, D22 and E22.
The RANK function will give you the rank of each player. So you'll get something like 2, 4, 1, 3 in cells B22 to E22.
Then select those cells and add four Conditional Highlighting rules to them. One for if the cell value is equal to 1 (red), then 2 (orange), etc.
Now that gets you the rank number and a color in the cell below the totals. Hopefully this is good enough. If not, if you really need the score cell to be a color, it gets much more complex because cells can only get conditional highlighting rules that examine the value of the cell itself, not other cells. Do you need that?

Philip
2 years ago

Thanks very much.
It's about bedtime where I am, so I'm going to sleep on it and actually do it tomorrow morning and see how it works out.

Philip
2 years ago

So far, so good. I'm very pleased you were able to help me.
Caveat: I put the row of cells with the rankings over to the right rather than below. I find that less disruptive in the column of scores.

Philip
2 years ago

I just found an added advantage to having the RANK cells off to one side.
I can select those 4 columns and create the four conditional highlighting rules in one fell swoop for those columns in their entirety.

2 years ago

Philip: You can also create the conditions for one cell. Then select all four cells and choose to "merge" the highlight conditions.