Conditional Formatting to find highest value in a ROW if greater than n% to the second highest and also finding the lowest value in a ROW if n% lower

[joncoll]

Hi All,

Hope you're all well. I am trying to do the following, if possible. I have found a way around it using formaulas but can't do it using conditional formatting.

What I want to do is to find highest value in a ROW if greater than n% to the second highest (say green) and also finding the lowest value (red) in a ROW if n% lower than the second lowest value and then anthing that doesn't fit this criteria i want to make yellow.

Is this possible? I have been using MAXIF/MINIF & LARGE & SMALL combos but would like something that i could apply using conditional formatting entirely.

Any help would be greatly appreciated

Kind regards,

Jon

 

[oldbrewer]

[TABLE="width: 832"]
<colgroup><col span="13"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]say you want to highlight 12 becouse it is more than 5% bigger than 11[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]the 12 is highlighted red using this conditional formatting formula[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=LARGE(D1:K1,1)>1.05*LARGE(D1:K1,2)[/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]
[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]
[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]
[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]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joncoll]

Hi Oldbrewer!

Thanks for getting back to me. Total mistake on my part, I meant to say columns!

I have been playing around with it but cant get it to return if the highest two are 5% difference and the lowest 2 are 5% in the difference! See below the result I would like to get help on.
COL
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]15[/TD]
[TD="class: xl65, width: 64"]39[/TD]
[TD="class: xl64, width: 64"]46[/TD]
[TD="class: xl65, width: 64"]33[/TD]
[TD="class: xl65, width: 64"]13[/TD]
[TD="class: xl65, width: 64"]21[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]27[/TD]
[TD="class: xl64, width: 64"]45[/TD]
[TD="class: xl64, width: 64"]49[/TD]
[TD="class: xl64, width: 64"]39[/TD]
[TD="class: xl64, width: 64"]22[/TD]
[TD="class: xl64, width: 64"]32[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]30[/TD]
[TD="class: xl64, width: 64"]61[/TD]
[TD="class: xl64, width: 64"]47[/TD]
[TD="class: xl64, width: 64"]56[/TD]
[TD="class: xl64, width: 64"]25[/TD]
[TD="class: xl64, width: 64"]33[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]38[/TD]
[TD="class: xl64, width: 64"]61[/TD]
[TD="class: xl63, width: 64"]73[/TD]
[TD="class: xl63, width: 64"]72[/TD]
[TD="class: xl63, width: 64"]34[/TD]
[TD="class: xl63, width: 64"]46[/TD]
[/TR]
</tbody>[/TABLE]

Apologies for wasting your time but I will definately use your first comment in the future!

Thansk,

JC

 

[oldbrewer]

=or(....test for highest.... , test for lowest)

you will got none or one or 2 colored

 

[oldbrewer]

but if you only want coloring if both conditions are met =and(.... , .....)

 

[joncoll]

Thank you very much

 

[oldbrewer]

It suddenly dawned on me that this will color every cell - you need to check if any cell is equal to the largest or smallest value.

say the cell is A1

=and(or(A1=large(d1:k1,1),A1 =small(d1:k1,1))....test for highest.... , test for lowest) using =LARGE(D1:K1,1)>1.05*LARGE(D1:K1,2) and same for small 1 being less than small 2 * .95