aacod
03-01-2013, 10:16 AM
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
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