Charlotte has attended:
Excel Intermediate course
Conditional Formatting
What is the best formula for output to: highlight a series of cells with various values if they fall 10% below fixed value in other cell, all other cells that are 15% above highlight in another colour, leave all others as they are?
RE: Conditional Formatting
Hi Charlotte,
Thank you for the forum question.
Best practice is to type the fixed value, 10%, and 15% in 3 different cells on the worksheet.
In my example I have the fixed value typed in F1. 10% in D1, and 15% in E1. My values I would like to highlight are in the range A1:A20
You need two rules.
Select all the values you would like to be highlighted.
Click Conditional Formatting and click New Rule.
Select the last option on the top "Use a formula to determine which cells to format".
In the "Format values where this formula is true" type:
=A2<$F$1*(1-$D$1) (this one highlights under 10%)
Click Format and choose a colour.
Do the same again but this time type the formula:
=A2>$F$1*(1+$E$1) (this one highlights over 15%)
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector