Conditional format formula

srizki · Nov 28, 2005

I have some percentages adding-up to hundred, I want a formula to use in conditional format that when these percentages do not adds up to 100% the cell should highlighted, so far it is easy in some of those columns there are only zeros, that adds up to as you guess correctly zero, BUT those are not really zeros, they could be 0.00001 or something like that, so if I use the 100% formula it highlights the zeros too. For 100% I used the formula ==B110<>100% , or Cell value is not equal to 1 both work perfectly ( unless there is another one more sophisticated, I am sure you can make many).
My question is how can I put both conditions in conditional formula, remember the zeros may not me zero, so we can not say if value is not equal to zero etc.
Thanks

Aladin Akyurek · Nov 28, 2005

srizki said:
I have some percentages adding-up to hundred, I want a formula to use in conditional format that when these percentages do not adds up to 100% the cell should highlighted, so far it is easy in some of those columns there are only zeros, that adds up to as you guess correctly zero, BUT those are not really zeros, they could be 0.00001 or something like that, so if I use the 100% formula it highlights the zeros too. For 100% I used the formula ==B110<>100% , or Cell value is not equal to 1 both work perfectly ( unless there is another one more sophisticated, I am sure you can make many).
My question is how can I put both conditions in conditional formula, remember the zeros may not me zero, so we can not say if value is not equal to zero etc.
Thanks

Wouldn't

=($B110 >= 0)*($B110 < 1)

do what you want?

srizki · Nov 29, 2005

Thank you Aladin, it worked as I wanted, I changed the formula little bit as per my need, from 0 to 0.0001,
Can this equation be written using IF and OR.

I am using these formula.

=OR(ABS(D$110)>100%) condition 1

=(D$110>=0.0001)*(D$110<1) condition 2
On these cells.

SOM06.xls