Conditional formatting

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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
 
WOW ! Thanks Ron that looks brilliant will try it tonight

thanks
A

Ron Coderre said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top