Conditional Formatting - Ignoring 0's using bottom 10% rule.

[SourNerd]

Hi Again.

I am Conditional formatting a range of data which firstly falls below the target 20. This is to be highlighted in Yellow.

Over this same data, I need to select the bottom 10% of data which excludes capturing 0's and #N/a's in red.

Can anyone pretty please help me create a formula which does this?

Please see example below:

0 yellow
5 red
10 yellow
15 yellow
20 yellow
25
30
35
40
45

So as you can see, the 0 is still highlighted in yellow to identify being less than 54, however not picked up in the bottom 10% of data.

Please please can anyone give me a few pointers.

 

[etaf]

bottom 10% of what - the total of the range - the highest number in the range , the count of entries ?

in the formula you are using to calculate the 10% just add a
AND ( cell <> 0, 10% calc )

 

[SourNerd]

bottom 10% of what - the total of the range - the highest number in the range , the count of entries ?

in the formula you are using to calculate the 10% just add a
AND ( cell <> 0, 10% calc )

Hi etaf

Thanks for your reply, it is the bottom 10% of the range of data which falls under a target. So for example the target is 45, I want to highlight all numbers which fall in the 10% of 45 range. I'm a little stuck with this and creating the percentage formula.

 

[pjmorris]

how do you define/identify/calculate the target?

 

[SourNerd]

how do you define/identify/calculate the target?

Hi there

The target is a set figure of 45 and will not change and it sits in a cell as a number.

Thanks.

 

[pjmorris]

Hi there

The target is a set figure of 45 and will not change and it sits in a cell as a number.

Thanks.

I presume you mean the target is 54 as stated in your first post otherwise the 5 should be in yellow since it is more than 10% of 45.

To answer the question you need the following Conditional Format formulas:

Enter: =A2<=20 as the first formula and format the fill in Yellow.

Then enter: =AND(A2>0, A2<=$D$1*10%) as the second formula and format the fill in Red. Note that $D$1 is the cell containing your target.

Hope this helps.

Peter