Conditional Formatting when between two values

Question

I am trying to figure out how to conditionally format a row based on the value in one column. The values can go from 0 - 100, I want 3 different conditions.

1. value < 90 = Formatting Red
2. value => 90 and < 100 = Formatting Orange
3. value = 100 = Formatting Green

I have figured out how to do 1 and 3, but getting the 2nd one has me stuck. For the first one I have a formula expression like this: "=$B5<90" and this works perfectly anything that is less than 90 is red.

I have tried for the 2nd one this: =$N20>90&$N20<100, but it does nothing no formatting is applied.

Solution

& is the concatenation operator in Excel, it doesn't work as AND, use one of below:

• =AND($N20>90,$N20<100)
• =($N20>90)*($N20<100)

Comments

[Mike]

I feel kind of dumb for not realizing that sooner. Thanks!