Conditional formatting

[Al]

Hi
i have the following data items - 2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a, where
2c is the lowest and 5a is the highest.

i need to compare two cells and if the second cell is two or more items
higher than the first cell i need to format that cell green otherwise it
needs to be red.

Is this possible please - i cant work out how to do the comparision

Thanks
A

 

[Ron Coderre]

Try this:

Available values in descending order are:
5a, 5b, 5c, 4a, 4b, 4c, 3a, 3b, 3c, 2a, 2b, 2c

The below conditional formula converts those values to:
6.000, 5.667, 5.333, 5.000, 4.667, 4.333, 4.000, 3.667, 3.333, 3.000, 2.667,
2.333

After conversion, the CF below checks if the test cell difference is greater
than or equal to 0.667, then CF engages.

With values from your list in A1 and B1

Select B1
<format><conditional formatting>
Formula is:
=((LEFT(B1,1)+(100-CODE(RIGHT(B1,1)))/3)-(LEFT(A1,1)+(100-CODE(RIGHT(A1,1)))/3))>=2/3
Click the [fomat] button and set your format
Click the [OK] buttons...done

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Al]

WOW ! Thanks Ron that looks brilliant will try it tonight

thanks
A

Try this:

Available values in descending order are:
5a, 5b, 5c, 4a, 4b, 4c, 3a, 3b, 3c, 2a, 2b, 2c

The below conditional formula converts those values to:
6.000, 5.667, 5.333, 5.000, 4.667, 4.333, 4.000, 3.667, 3.333, 3.000,
2.667,
2.333

After conversion, the CF below checks if the test cell difference is
greater
than or equal to 0.667, then CF engages.

With values from your list in A1 and B1

Select B1
<format><conditional formatting>
Formula is:
=((LEFT(B1,1)+(100-CODE(RIGHT(B1,1)))/3)-(LEFT(A1,1)+(100-CODE(RIGHT(A1,1)))/3))>=2/3
Click the [fomat] button and set your format
Click the [OK] buttons...done

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Hi
i have the following data items - 2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a,
where
2c is the lowest and 5a is the highest.

i need to compare two cells and if the second cell is two or more items
higher than the first cell i need to format that cell green otherwise it
needs to be red.

Is this possible please - i cant work out how to do the comparision

Thanks
A

 

[mr_teacher]

Hi,

I am guessing from this query you're connected to a primary schoo
trying to do some sort of analysis on SAT's?

I'm trying to do something like that too if you are - if you are fanc
trading ideas give me a shout either on here or send me an email

Regards

Car