CONDITIONAL FORMULA with Percentage

[aacod]

I want a conditional formula to do as described below:

Sheet1

* B C D E
1 40 24 15 1
2 * * * *
3 * * * *
4 * * * *
5 * * * *
6 * * * *


Cell Formula in C1 =IF(ISNUMBER(B1),B1-(D1+E1), "")



1. Values in B1, D1 and E1 are entered manually.

2. There is a formula in C1: =IF(ISNUMBER(B1),B1-(D1+E1), "")

3. I need a CONDITIONAL FORMULA in E1 which does following:

4. The manually entered value in cell E1 ( '1' as above in E1) looks at the value in cell B1 ( '40' as above in B1) and if that value in E1 is

a.) = or > 90%, fill E1 with Green and fonts Red

b.) between 89% and 70% fill E1 with Yellow and fonts Red

c.) between 69% and 60% fill E1 with Red and fonts Black

d.) < than 60% fill E1 with Black and fonts White.

Hope This would help to find a solution.

In the example above, I need conditional formula in Cell E1: 1-(E1/B1)*100 that gives an output of 97.5%, so when the percentage is >90%, fills cell E1 with Green and red fonts.

HTH.

Thanks

Aacod

[CodeNinja]

To accomplish this you need 4 conditional formats. Follow these steps:
1- Highlight the range you wish to set the conditional formatting for
2- open the conditional formatting dialog box by typing alt then O then D
3- Click on New Rule
4- Click on "Use a formula to determine which cells to format"
5- Enter the formula
6- click on format and set the format you want for that formula

Repeat these steps 4 times using the following 4 formulas: (copy and paste them into the formula box)
=1-(E1/B1)>=0.9
=AND(1-(E1/B1)<0.9,1-(E1/B1)>=0.7)
=AND(1-(E1/B1)<0.7,1-(E1/B1)>=0.6)
=1-(E1/B1)<0.6

[aacod]

Thanks, CodeNinja. It works PERFECT.