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]

			3	5	12	7	6	6	11	5
				say you want to highlight 12 becouse it is more than 5% bigger than 11
					the 12 is highlighted red using this conditional formatting formula
					=LARGE(D1:K1,1)>1.05*LARGE(D1:K1,2)

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

 

[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

15	39	46	33	13	21
27	45	49	39	22	32
30	61	47	56	25	33
38	61	73	72	34	46

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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