Previous Topic Next Topic

Conditional Fomatting with range values

Closed
vikassova Posts 1 Registration date Wednesday December 4, 2013 Status Member Last seen December 5, 2013 - Dec 5, 2013 at 12:03 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 5, 2013 at 11:02 AM
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 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 5, 2013 at 11:02 AM
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
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Excel Macro - Set and autofill a range?
KT -
excel987 -

28 responses
Excel if range of cell contain a text put a cell value in
diegomagum -
TrowaD -

11 responses