Conditional formating Help needed

[Mldeuser]

Is it possible to have a conditional formating rule that looks at the value of cell e12 and if that value is greater than 5,000 or less than -5,000 and is greater than 5% or -5% when the value in cell e12 is divided by d12. and shades the cell

D12 = (130,000)
E12 = 8,870

Thank you

 

[dcardno]

You can test for absolute value (which is easier than two tests for >5K or <-5K), and also include compound tests.
You should include a conditional format for "formula is"

=(ABS($D12) >= 5000) * (ABS($E12 / $D12) > 5%)

and then set the formatting you want. The relative references assume that you want to copy this down the sheet for multiple rows.

 

[Mldeuser]

Thankyou for your help.

One last thing is there a way to account for cell D12 being zero. since this returns #DIV/0! instead of a number.

Thank you

 

[Jonmo1]

Thankyou for your help.

One last thing is there a way to account for cell D12 being zero. since this returns #DIV/0! instead of a number.

Thank you

That won't matter in conditional formatting..
Conditional formatting considers errors as False..
Or more accurately....NOT True.

Unless you want the Formatting applied in the case that D12 is 0, then you don't need to test for it.

 

[Mldeuser]

I would like formatting to account for D12 being 0 since this could mean that E12 is greater than 5,000

Thank you

 

[Mldeuser]

I made it work by adding a second conditional format rule

Thank you