Conditional Formatting - Ignoring 0 and #N/A using bottom 10% rule.

[SourNerd]

Hi Everyone

I really hope you can help me. I have a range of data which is populated using a vlookup from another spreadsheet. Within this data I have numbers ranging from 0-54. The 0's appear as '-' because that's how they are fedback. I want to conditional format this set of data, to highlight in red the bottom 5% in the range. When I do it includes the 0's. I want this to work so it ignores the 0's and looks at the bottom 5% starting from the first number that isn't a zero., within the range.

I would thank a million times anyone that could help me as i've spent 2 weeks on this so far.

Cheers.

 

[oldbrewer]

[TABLE="width: 1038"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD]I really hope you can help me. I have a range of data which is populated using a vlookup from another spreadsheet. Within this data I have numbers ranging from 0-54. The 0's appear as '-' because that's how they are fedback. I want to conditional format this set of data, to highlight in red the bottom 5% in the range. When I do it includes the 0's. I want this to work so it ignores the 0's and looks at the bottom 5% starting from the first number that isn't a zero., within the range. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]<<<<<<[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]n/a[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]find the bottom 33% ignoring the - and the n/a[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]53[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]there are 21 numbers[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]52[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cell marked <<<<<< which is H2 =SMALL($E$2:$E$22,D2)[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD]n/a[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD]47[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SourNerd]

Thank you for your reply but I'm so confused lol, I do not understand which cells you are referencing with E D and H?

 

[oldbrewer]

col D is 1 2 3 4 5 etc etc
col E is list of numbers with n/a's and -'s
H is the list of numbers in ascending order

 

[SourNerd]

col D is 1 2 3 4 5 etc etc
col E is list of numbers with n/a's and -'s
H is the list of numbers in ascending order

Hi Brewer

Many thanks for your attention and time here. I still think the resolution you provided isn't working as hoped.

See below my Data. The conditional formatting is set first to capture figures under the target number 54 with a yellow colour. Then the same data is selected to highlight the bottom 40% in Red. As you can see, Amanda and Richard have 0, but are highlighted, I need some advice/guidance please on how the Conditional formatting can ignore the 0's.

I tried your formula but it doesn't like it. Any suggestions perhaps?
[TABLE="width: 128"]
<tbody>[TR]
[TD]Amanda[/TD]
[TD="align: right"]0 (RED)[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD="align: right"]0(RED)[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD="align: right"]5(RED)[/TD]
[/TR]
[TR]
[TD]Dennis[/TD]
[TD="align: right"]14(RED)[/TD]
[/TR]
[TR]
[TD]Trish[/TD]
[TD="align: right"]10(RED)[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]18(Yellow[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD="align: right"]33(Yellow[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD="align: right"]54(yellow[/TD]
[/TR]
[TR]
[TD]Emily[/TD]
[TD="align: right"]60.4[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD="align: right"]60.5[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Michael[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Craig[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]Krista[/TD]
[TD="align: right"]120[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

Again, greatly appreciated.