Conditional formatting - Highlight top 3 in a range with value more than 100

[STGE2]

Hi All

I have this conditional formatting proglem that I just cannot solve. I have looked in a lot of forums without comming up with an answer.

Question:
I have a range A1:A50 containing numbers and a range B1 containing a number.
Now I want to highlight the 3 numbers in range A1:A50 that is closest to and biggger than B1.

Small eksample:
A | B
50 | 55
30 |
66 |
44 |
62 |
56 |
99 |
67 |

Thanks a lot in advance!!

Best regards
Stine

 

[WinteE]

Guess you need a macro to have this working.

 

[jbeaucaire]

This is a little hard to explain... we're going to create a NAMED FORMULA that is an array formula.

Assuming the values to check are in A1:A8:

1) Click on A1 (this is critical to do first)
2) Open the Insert > Name > Define window and create a named range with these settings:

Names in Workbook: SmallCheck
Refers To: =IF(A1>$B$1, OR(A1=SMALL(IF($A$1:$A$8>=$B$1, $A$1:$A$8), {1,2,3})))

3) Click OK to create the named range.
4) Now highlight A1:A8
5) Open the Conditional Formatting window and use these settings:

Formula Is: =SmallCheck
Format... Pattern tab: Red color

The end result is the same as you've shown:

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Courier New; text-align:right; ">50</td><td style="text-align:right; ">55</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Courier New; text-align:right; ">30</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#ff0000; font-weight:bold; font-family:Courier New; text-align:right; ">66</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Courier New; text-align:right; ">44</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#ff0000; font-family:Courier New; text-align:right; ">62</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#ff0000; font-family:Courier New; text-align:right; ">56</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Courier New; text-align:right; ">99</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Courier New; text-align:right; ">67</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A1</td><td >1. / Formula is =SmallCheck</td><td style="color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1"

 

[STGE2]

I was just about to write a macro for this but wanted to see what excel had to offer without macros.

jbeaucaire: Very clever and elegant solution! Have worked a bit with formulars in Named ranges but didn't think of this... worked perfect!!

Thanks both of you your fast replys

 

[p45cal]

jbeaucaire,
You beat me to it, (I'd been exploring RANK as well), but could you explain why you used the longer of the formulae below (I've used the shorter to define a Name and it gave the same result:

Excel Workbook
	A	B	C	D
1	50	55	FALSE	FALSE
2	30		FALSE	FALSE
3	66		TRUE	TRUE
4	44		FALSE	FALSE
5	62		TRUE	TRUE
6	56		TRUE	TRUE
7	99		FALSE	FALSE
8	67		FALSE	FALSE
Sheet

 

[jbeaucaire]

I dislike array formulas for the most part, so mine does a simple non-array test at the beginning to see if the number in the cell is even higher than the test cell or not, no need doing all those array calcs if the number can't possibly be one of the TRUE values.

 

[p45cal]

I dislike array formulas for the most part, so mine does a simple non-array test at the beginning to see if the number in the cell is even higher than the test cell or not, no need doing all those array calcs if the number can't possibly be one of the TRUE values.

Ff.. fffai.. ffair.. ffair…
(can't say fairer than that), thanks.

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">50</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">CF</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">FALSO</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">56</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #C5D9F1;;">66</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">44</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">55</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">25</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">22</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #C5D9F1;;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">           </td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">{=IF(<font color="Blue">A1>$E$1,IF(<font color="Red">A1<=SMALL(<font color="Green">IF(<font color="Purple">ListNB>$E$1,ListNB</font>),MIN(<font color="Purple">3,COUNT(<font color="Teal">IF(<font color="#FF00FF">ListNB>$E$1,ListNB</font>)</font>)</font>)</font>),1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">ListNB</th><td style="text-align:left">=Sheet1!$A$1:INDEX(<font color="Blue">Sheet1!$A:$A,MATCH(<font color="Red">3E+300,Sheet1!$A:$A,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[STGE2]

Thanks all!!!

Nice solutions and I acturly learned some new stuff about Excel so I'm happy happy happy!!!

I which you all a super splendid day!!!

Regards
Stine