Conditional Formatting...

[sumospim]

Hi all, i wonder if someone could help me on the above?

I want to automatically place a colour in a cell for the following instances.

1. When a cell is between a certain range ( +/- 7.5% of a value) (CORE range)
2. When a cell is between +7.5% and 15% of a value (HIGH range)
3. When a cell is between -7.5% and -15% of a value (LOW range)
4. When a cell is > than 15% of a value (SUPERHIGH range)
5. When a cell is < than 15% of a value. (SUPERLOW range)

Any guidance would be greatly appreciated.

Best wishes

Simon

 

[alvin-chung]

Hi Simon, assume your cell is in A2 and the value is in B2 you can create new conditional formatting formula such as =ABS((A2-B2)/B2)<0.075) for your first condition; and similarly for the rest...

 

[sumospim]

HI Alvin, cheers for your response, I am getting errors in that formula.

Could you take a look at this link, may help you, help me I have put spreadsheet and explanation.

https://app.box.com/s/bzbi7d8anolbibulhg2w

 

[alvin-chung]

Hi, which range of cells you are trying to highlight?

 

[sumospim]

C10 - I10 (Row marked HCL)

Cheers

 

[alvin-chung]

But there's no value in those cells, probably you've replaced the values with the expected conditional formatting?

 

[sumospim]

Sorry, so..

The conditional format colour needs to go in cells C10-I10 but it refers to the value in C7

So C7 is in the range of low as per spreadsheet so C10 should automatically go to Low (yellow) and so on...

 

[alvin-chung]

No worries, just need clarification because the colour you show C10:I10 seems inaccurate with your criteria
For LOW range, try select C10:I10 and create new conditional formatting formula below will yellow fill

=AND(C7>=$L$5*0.075,C7<=$L$5*0.95)

You shall see C10, D10 and I10 highlighted as yellow?

 

[sumospim]

That makes perfect sense but how do i cover all the conditions accross the range C10:I10, so that all cells in that range change to meet the criteria as below:

So Core range is between 16960 - 19710
High range is between 19710 - 21085
Low range is between 16960 - 15584
Super high is > 21085
Super low is < 15584

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Thanks so much for your patience...

 

[alvin-chung]

Correction to earlier formula, please create and try all the Conditional Formatting below:
ps: some of your range are overlapping and I've tune that a bit (please change according to your criteria)

Super High

=C7>$L$5*1.15

High

=AND(C7>$L$5*1.075,C7<=$L$5*1.15)

Core

=AND(C7>=$L$5*0.925,C7<=$L$5*1.075)

Low

=AND(C7>=$L$5*0.75,C7<$L$5*0.925)

Super Low

=C7<$L$5*0.75