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
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?
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.
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.
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.
Philip: You can also create the conditions for one cell. Then select all four cells and choose to "merge" the highlight conditions.