convert a formatted color cell to a percentage in a different cell

Question

I have conditional formatting in a cell that ranges from 1-5. 1= green 2= green 3= green 4= yellow 5= red

I am trying to change the 1-5 in a different cell to reflect a certain percentage. 1= green and should be 95% 2= green and should be 90% 3= green and should be 85% 4= yellow and should be 80% 5= red and should be 75%

Answer 1

One helper column solves the issue:

How it works:

• Helper Column is D, so the formula in D1:

  =IF(AND($A1=1,$B1=95%),1,IF(AND($A1=2,$B1=90%),2,IF(AND($A1=3,$B1=85%),3,IF(AND($A1=4,$B1=80%),4,IF(AND($A1=5,$B1=75%),5,0)))))
  

• Reach to Conditional format then New Rule.

• For

Green =OR($D1=1,$D1=2,$D1=3)

Yellow =$D1=4

Red =$D1=5

N.B :

For clear visibility I've used different color than RED for 5 & 75%, you may alter it.

---

• Later on for neatness you may hide the Helper Column then your data look like shown below.

Adjust cell references in the formula as needed.