Conditional Formatting question using "use formula to determine which cells to format"

[woody186]

I am attempting to use conditional formatting using an if or statement while also including an and statement. Upon completion of the statement the selected cells aren't shading. I entered the formula in a separate cell to see if I could get it to work and it did. Below is the formula that I entered separately, excel added the asterisk before the "and" portion of the statement. Upon completion of the formula if the two criteria are met a true or a false is properly displayed.

=IF(OR(K5>100000,K5<-100000)*AND(OR(M5>10%,M5<-10%)),"true","false")

Below is the statement as it appears in the "Edit formatting rule" box. I have also entered it with the asterisk before "and" and the formula didn't work:

="if(OR(K5>100000,K5<-100000)and(or(M5>10%,M5<-10%)))

Below are the cells the formula applies to:

=$K$5:$K$175,$M$5:$M$175

The cells do not properly shade if the statement is true.

 

[etaf]

in conditional formatting the function will return true or false - so you dont need to show that IF with a TRUE FALSE result.

=OR(K5>100000,K5<-100000), AND(OR(M5>10%,M5<-10%))). as you have an OR () which is complete - so the , after will not be understood hence , why a * may have been applied
BUT missing a function , not making sense as a function
Not sure what you exactly want
as you have a nested OR In and AND - BUT only an OR

do you want

AND ( OR(K5>100000,K5<-100000) , OR(M5>10%,M5<-10%) )
so evaluate from within

If K5 is greater than 100,000 OR less than -100,000 - BUT nothing in between -100,000 and + 100,000
is that what you want for K5
so either of those must be TRUE
same for M5
M5 is greater than 10% OR less than -10% - BUT nothing in between -10% and + 10%
so either of those must be TRUE
In order for the AND to have both the ORs must be true
to return a TRUE for conditional formatting to work]

 

[woody186]

in conditional formatting the function will return true or false - so you dont need to show that IF with a TRUE FALSE result.

=OR(K5>100000,K5<-100000), AND(OR(M5>10%,M5<-10%))). as you have an OR () which is complete - so the , after will not be understood hence , why a * may have been applied
BUT missing a function , not making sense as a function
Not sure what you exactly want
as you have a nested OR In and AND - BUT only an OR

do you want

AND ( OR(K5>100000,K5<-100000) , OR(M5>10%,M5<-10%) )
so evaluate from within

If K5 is greater than 100,000 OR less than -100,000 - BUT nothing in between -100,000 and + 100,000
is that what you want for K5
so either of those must be TRUE
same for M5
M5 is greater than 10% OR less than -10% - BUT nothing in between -10% and + 10%
so either of those must be TRUE
In order for the AND to have both the ORs must be true
to return a TRUE for conditional formatting to work]

Yes that is what I am trying to accomplish. I removed the if from the formula and copied the formula you have above and it is being accepted but when the result is true the cells in column K and M are not shading.

 

[etaf]

if you want to colour columns K and M
then
select the range , K5:M1000
But because you have selected row 2 the formula has to include row 5
you also need to fix the columns with a $ so that do not move

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
K5:M1000 - extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
AND ( OR($K5>100000,$K5<-100000) , OR($M5>10%,$M5<-10%) )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

BUT as you have selected column K to M , that will highlight
K, L & M cells from row 5 where a match is

you can select the range in K and then use the (windows) control or (Mac) command key to select the same rows but in M
if you only want those 2 columns to be formatted