Stuck on a conditional format formula

[Mr_Phil]

Thanks for looking. I'm frustrated that so far I am not getting it. I would like to conditionally format a cell to turn green/red if it is +/- more than x % .

MAIN!S2 = 100 it is the benchmark (google drive std hours between two addresses)
MAIN!R2 = 110 is the actual drive time
TABULATED ROUTE DATA!k15= 10% which is the adjustable variance up or down from the benchmark that is allowed. If k15= 10% and R2 = 91 thru 109 would be green. Anything outside of that is red. My boss wants two variables. So that there is a warning buffer (exceeding 10% in either way) at yellow and the driver is coached and warned. And if it is over 11% either way it is red and the driver is disciplined if called for. I told her I'd be happy to get reliable formatting with just one variable.

The picture is the rule I made before they decided they should be able to change the variable on a whim (to account for weather or other uncontrollables) so I am back at square one.
Any advice on method, technique, or just an expression of sympathy would be awesome LOL. Happy Holidays and Peace.

 

[MountainFog]

Use a formula to determine which cells to format:

Formatted as Green Applies to $R:$R:

=AND($R1>=($S1*(1-'TABULATED ROUTE DATA'!$K$15)),$R1<=($S1*(1+'TABULATED ROUTE DATA'!$K$15)))

Formatted as Red Applies to $R:$R:

=OR($R1>($S1*(1+'TABULATED ROUTE DATA'!$K$15)),$R1<($S1*(1-'TABULATED ROUTE DATA'!$K$15)))

 

[MountainFog]

And for what your Boss wants:

Use a formula to determine which cells to format:

Variable as cutoff between Green & Yellow: 'TABULATED ROUTE DATA'!$K$15
Variable as cutoff between Yellow & Red: 'TABULATED ROUTE DATA'!$K$16

Formatted as Green Applies to $R:$R:

=AND($R1>=($S1*(1-'TABULATED ROUTE DATA'!$K$15)),$R1<=($S1*(1+'TABULATED ROUTE DATA'!$K$15)))

Formatted as Yellow; Applies to $R:$R:

=AND($R1>=($S1*(1-'TABULATED ROUTE DATA'!$K$16)),$R1<($S1*(1-'TABULATED ROUTE DATA'!$K$15)))

Formatted as Yellow; Applies to $R:$R:

=AND($R1>($S1*(1+'TABULATED ROUTE DATA'!$K$15)),$R1<=($S1*(1+'TABULATED ROUTE DATA'!$K$16)))

Formatted as Red Applies to $R:$R:

=OR($R1>($S1*(1+'TABULATED ROUTE DATA'!$K$16)),$R1<($S1*(1-'TABULATED ROUTE DATA'!$K$16)))

 

[Mr_Phil]

And for what your Boss wants:

Use a formula to determine which cells to format:

Variable as cutoff between Green & Yellow: 'TABULATED ROUTE DATA'!$K$15
Variable as cutoff between Yellow & Red: 'TABULATED ROUTE DATA'!$K$16

Formatted as Green Applies to $R:$R:

=AND($R1>=($S1*(1-'TABULATED ROUTE DATA'!$K$15)),$R1<=($S1*(1+'TABULATED ROUTE DATA'!$K$15)))

Formatted as Yellow; Applies to $R:$R:

=AND($R1>=($S1*(1-'TABULATED ROUTE DATA'!$K$16)),$R1<($S1*(1-'TABULATED ROUTE DATA'!$K$15)))

Formatted as Yellow; Applies to $R:$R:

=AND($R1>($S1*(1+'TABULATED ROUTE DATA'!$K$15)),$R1<=($S1*(1+'TABULATED ROUTE DATA'!$K$16)))

Formatted as Red Applies to $R:$R:

=OR($R1>($S1*(1+'TABULATED ROUTE DATA'!$K$16)),$R1<($S1*(1-'TABULATED ROUTE DATA'!$K$16)))

View attachment 103966

Ok, wow. You are my new best friend. If you are ever in Lubbock Texas you can feel free to message me and lunch is on me for sure. Thank you so much.