Highlight closest without going over

[jac508]

Hello! I am trying to create a scoring sheet for a price is right style of game. I want to be able to enter each person's guess in cells B2 through B19 and have it highlight the cell(s) that is/are closest without going over the actual price, which is found in cell B20. I know I need to create a new rule under conditional formatting, but I cannot figure out the formula. I've referenced a few other threads that address highlighting the closest cell, but I'm having trouble limiting it to be less than the reference cell. Any help is appreciated. Thank you!

 

[jasonb75]

Depends which version of excel you have (please update your Account details to show this).

Personally, I would use

=$B2=MAXIFS($B$2:$B$19,$B$2:$B$19,"<="&$B$20)

applied to B2:B19 as a CF rule.

 

[Fluff]

How about

+Fluff 1.xlsm
	B
1
2	25
3	28
4	50
5	43
6	27
7	38
8	21
9	20
10	26
11	43
12	32
13	47
14	50
15	38
16	37
17	43
18	35
19	29
20	34
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B19	Expression	=B2=MAX(IF($B$20-$B$2:$B$19>=0,$B$2:$B$19))	text	NO

 

[jac508]

Thank you, both!

 

[Fluff]

Glad we could help & thanks for the feedback.