changing conditional formatting based on a reference

2006
Current Quick Fin. Lev. Debt/Equity
SJM2.4311.530.25
CAG 1.62 0.51 2.63 0.68
KFT 0.79 0.39 1.95 0.25

Avg 1.205 0.45 2.29 0.465




Right now i have conditional formatting that will turn the background color red if below the average and green if it above the average. There will be times though, where having a lower value than average is better, for example turnover. I would like to find a way where if I were to put the word lower below the average (one cell under 1.205 ) then it would reverse the color formatting (originally formatted based on conditional formatting) so that green is displayed when the cell value is below the average. Please help and/or let me know if i need to be more clear on the concept.

Hi Altsean,

Select B12:B14 and create a new Conditional Formatting rule like so:

For green:
=IF(B$17="LOWER",B12<B$16,B12>B$16)

For red:
=IF(B$17="LOWER",B12>B$16,B12<B$16)

Only difference is the signs are reversed.