Excel conditional format question

Associate
Joined
18 Oct 2002
Posts
1,980
Location
Swindon. =)
SO i have something for work i'm trying to figure out, i'm no excel expert in any way.

I have a spreadsheet with measurements taken from other software.

What i have now works, which is the cell with the value will look up another cell to check if it's above or below the desired value, a simple red for bad green for good, this works okay.

What i want to know is if there is someway i can add a way to highlight the cell another way if it's within 10% say of the upper limit.

example
upper limit 53.63
ideal 58.13
lower 58.63

I.E
ZNHG0tH.jpg

Currently, above upper goes red, but i would like say orange if the value is say 58.53, is that possible?
 
Soldato
Joined
27 Dec 2009
Posts
2,727
Location
Gillingham, Kent
If you're doing it just by conditional formatting, then this works:
Add three rules, selecting Formula for each one and putting the max/min numbers in each one:
  • =answer cell=ideal result - colour green
  • =AND(answer cell<>ideal result , answer cell < maximum result , answer cell > minimum result) - colour amber
  • =OR(answer cell > maximum result , answer cell < minimum result) - colour red
X8KT7di.jpg

Or, if you'd prefer to go for within 10% of ideal rather than set boundaries, then amber would be:
=AND(answer cell<>ideal result , answer cell > ideal result * 0.9 , answer cell < ideal result * 1.1)

and red is:
=OR(answer cell >= ideal result * 1.1 , answer cell <= ideal result * 0.9)
 
Last edited:
Back
Top Bottom