Ranking based on Conditional Formatting colour scheme

[madinekidd]

This might be very trivial but I can't for the life of me work it out.

8-12 cells affected, using default conditional formatting rules 'Format all cells based on their values' - Format style '3-Color Scale' and midpoint Percentile '50'. Maximum and minimum are left as 'Highest value' and 'Lowest Value'.

Highest value is Dark green, middle is yellow/amber and lowest value being red.

This is a gradient so if I have 8 records they will range from Dark Green to Dark red.

I want to be able to make a separate field that ranks them based on their conditional formatting colour code - not the value of the cell.

I don't know the correct colour scheme but hopefully the below table makes enough sense.

Dark Green (highest) - 10
Green -9
Light Green - 8
Green/amber gradient - 7
Lighter Green/amber gradient - 6
Yellow (midpoint) - 5
Dark yellow - 4
Light orange - 3
Orange - 2
Red - 1

Depending on the values there may be 2 values that are given Dark Green (highest) I want to give them a ranking or score value of 10 in another cell, then each other colour would have a different ranking. If you paste this data into excel, add the conditional format rules it would display row 1 and 6 as Dark green - which I want to be both being ranked as a 10 in a different cell.

It might be easier to visualise the colours for the example by having a list of 1-10 with the same formatting to see the colours matching to the below values.

Values	Rank/Score
70​	10
54​	5
60​	7
52​	4
54​	5
69​	10
64​	8
42​	1

Is this possible?

If any more info is required I can send it over

 

[awoohaw]

I'm not sure what you're getting at. Are you trying to place colors in a sequence of RGB or Pantone color scale so you pick your conditional order colors correctly?
when I first read your statement, I was thinking to use your conditional formulas in a long nested IF formula assigning the rank numbers that way.

How does 69 have the same rank as 70?

Can you post a worksheet with your conditional format rules?