Conditional formatting based on multiple rules

I have always worked with conditional formatting on a basic basis. However, I'm trying to work with more advanced rules and I'm having trouble definining how I should approach it.

Currently on my spreadsheet I have conditional formatting on B18, which is dependent on a tier - 1-5 which is calculated in cell E20.

So,

if E20 = 5 then condition formatting B18 0-104% = green, 104-105% = yellow, 105+ red
if E20 = 4 then condition formatting B18 0-119% = green, 119-120% = yellow, 120+ red
if E20 = 3 then condition formatting B18 0-124% = green, 124-125% = yellow, 125+ red
if E20 = 2 then condition formatting B18 0-129% = green, 129-130% = yellow, 130+ red
if E20 = 1 then condition formatting B18 0-134% = green, 134-135% = yellow, 135+ red

I really don't know how to set conditional formatting arrays, how exactly can I do this, I assume it has to be done with formulas, and not the conditional formatting GUI. Or does this need to be done in VBA? I really appreciate any help, I'm a little out of my experience here.

What version of excel do you use?

(Would be also nice to write it in profile)

Hi Pyrex238, I will try my best here because I am only on my tablepc without Excel. As far as I could understand the 1-5 tier values are on E20 and the % on B18, right? If so, please use formulas on the conditional formatting with the following (and in the order as shown below):

1) GREEN
Formula: =(B18<(114+((5-E20)*5)-INT(5/E20)*10)

2) YELLOW
Formula: =(B18=<(115+((5-E20)*5)-INT(5/E20)*10)

3) RED
Formula: =(B18=(115+((5-E20)*5)-INT(5/E20)*10)

In case the condition for the tier 5 is 114 instead of 104 (just wondering, because the difference between each tear was 5, but from tier #5 to #4 is 15) then please delete the INT(5/E20)*10 part.

One thing I am not very sure now is about the % value on the conditional formatting. In case the formulas above does not work try dividing all numbers by 100.

Try that and let me know in case of any issue. Tomorrow I will be back with my computer and can try before suggesting.

Good luck

Sorry, on the last formula (for the red guys) please change B18= to B18>

Sorry again
But replace INT(5/E20) by INT(E20/5). That will make it equal to 1 in case the tier is 5 and zero for the others.
Next time I will wait to test on Excel before posting

Pyrex238, after finally testing in my Excel, here are my final suggestions -- I hope

Option #1
GREEN: =(B18<=(1.14+((5-E20)*0.05)-INT(E20/5)*0.1))
YELLOW: =(B18<=(1.15+((5-E20)*0.05)-INT(E20/5)*0.1))
RED: =(B18>(1.15+((5-E20)*0.05)-INT(E20/5)*0.1))

Option #2
GREEN: =(B19<=(1.04+CHOOSE(E20,0.3,0.25,0.2,0.15,0)))
YELLOW: =(B19<=(1.05+CHOOSE(E20,0.3,0.25,0.2,0.15,0)))
RED: =(B19>(1.05+CHOOSE(E20,0.3,0.25,0.2,0.15,0)))

Good luck