Conditional Fomatting with range values

[Closed]
Report
Posts
1
Registration date
Wednesday December 4, 2013
Status
Member
Last seen
December 5, 2013
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
Here is my scenario:

Column A has ranking from 1 to 5.
Column B has zone/ classification from 1 to 7.
Column C has ratings. These ratings are based on a range.
for example if ranking (A) is 2 and zone (b) is 5, the rating should be in between 3 and 3.9. If the ranking is 1 and zone is 2 the rating will be in between 1 and 1.9.

I would like to use conditional formating to highlight the rankings which are not in the range.

Please suggest.

1 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi Vikassova,

I'm guessing your first example is a typo and should be between 2 and 4.9 or else it doesn't make much sense.

Wouldn't it be better if users can't input a 'wrong' value?

Top menu > Data > Validate...
Choose the last option from the first dropdown list and enter this formula:
=AND(C1>A1,C1<B1)

Otherwise use this formula in CF:
=AND(C1<=A1,C1>=B1)

Best regards,
Trowa