Conditionally format the lowest number in a column only if there are no ties

[turtle44]

I am looking for a simple way to highlight the winner of a golf "skin", which is awarded to the lowest score on a hole - WITHOUT ties. If there are two players with the lowest score, no "skin" is won. I've found all kinds of help around highlighting using the MIN function, but as far as I can tell, it would highlight two (or more) scores that were tied for the lowest.

I think rank.avg displays a "1" if the score is the lowest without ties and a 1.5 if there are two low scores that are tied and a 2 if there are 3 low scores that are tied, etc. I believe I should be able to isolate the conditional formatting to only highlight a cell if the rank.avg of that cell among all the other cells in the column is equal to exactly 1...but I can't figure out how to do that. Maybe my formatting is off?

You can see a basic example in the table below. Player 1 should win a skin for the score on hole 1 (the only 3) in column B and that player's score has a rank.avg on hole 1. Hole 2 and 3 do not have an outright winner as there is no rank.avg that equals 1.

My first attempt at conditional formatting was this formula:
=B2="RANK.AVG(b$2:b$9,1)=1"

I tried another:
="RANK.AVG(h$20:h$27,1)=1"

I believe I simply don't know how to format the formula for conditional formatting...please help

	Hole 1 Score	rank.avg	Hole 2 Score	rank.avg	Hole 3 Score	rank.avg
Player 1	3​	1​	3​	1.5​	3​	2​
Player 2	4​	5​	3​	1.5​	3​	2​
Player 3	4​	5​	4​	5.5​	3​	2​
Player 4	4​	5​	4​	5.5​	4​	6​
Player 5	4​	5​	4​	5.5​	4​	6​
Player 6	4​	5​	4​	5.5​	4​	6​
Player 7	4​	5​	4​	5.5​	4​	6​
Player 8	4​	5​	4​	5.5​	4​	6​

	Hole 1 Score	rank.avg	Hole 2 Score	rank.avg	Hole 3 Score	rank.avg
Player 1	3	=RANK.AVG(B2,B$2:B$9,1)	3	=RANK.AVG(D2,D$2:D$9,1)	3	=RANK.AVG(F2,F$2:F$9,1)
Player 2	4	=RANK.AVG(B3,B$2:B$9,1)	3	=RANK.AVG(D3,D$2:D$9,1)	3	=RANK.AVG(F3,F$2:F$9,1)
Player 3	4	=RANK.AVG(B4,B$2:B$9,1)	4	=RANK.AVG(D4,D$2:D$9,1)	3	=RANK.AVG(F4,F$2:F$9,1)
Player 4	4	=RANK.AVG(B5,B$2:B$9,1)	4	=RANK.AVG(D5,D$2:D$9,1)	4	=RANK.AVG(F5,F$2:F$9,1)
Player 5	4	=RANK.AVG(B6,B$2:B$9,1)	4	=RANK.AVG(D6,D$2:D$9,1)	4	=RANK.AVG(F6,F$2:F$9,1)
Player 6	4	=RANK.AVG(B7,B$2:B$9,1)	4	=RANK.AVG(D7,D$2:D$9,1)	4	=RANK.AVG(F7,F$2:F$9,1)
Player 7	4	=RANK.AVG(B8,B$2:B$9,1)	4	=RANK.AVG(D8,D$2:D$9,1)	4	=RANK.AVG(F8,F$2:F$9,1)
Player 8	4	=RANK.AVG(B9,B$2:B$9,1)	4	=RANK.AVG(D9,D$2:D$9,1)	4	=RANK.AVG(F9,F$2:F$9,1)

 

[AlphaFrog]

Try using the MIN function and COUNTIF function

=AND(B2=MIN(B$2:B$9), COUNTIF(B$2:B$9, MIN(B$2:B$9))=1)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Am I correct in thinking that you have only added rank.avg columns to help identify the skins winner(s). If so, another option might look like this.

23 05 16.xlsm
	A	B	C	D
1		Hole 1 Score	Hole 2 Score	Hole 3 Score
2	Player 1	3	3	3
3	Player 2	4	3	3
4	Player 3	4	4	3
5	Player 4	4	4	4
6	Player 5	4	4	2
7	Player 6	4	4	4
8	Player 7	4	4	4
9	Player 8	4	4	4
Skins

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D9	Expression	=AND(B2=MIN(B$2:B$9),B2<>SMALL(B$2:B$9,2))	text	NO

 

[turtle44]

I apologize as my original table that I posted was meant to be explanatory (to show the avg.rank column), but is not how I want to design the spreadsheet. In reality, I want just a list of columns with the scores and I want to put a formula in the conditional formatting interface that would evaluate if the avg.rank of a particular cell in a particular column was 1 (indicating it was the only lowest score - no ties), and if so, then highlight the cell in a color of my choosing. I could not figure out any easier way than avg.rank=1 to identify the minimum value in a column AND guarantee that there are no ties.

I know I can figure avg.rank in another column, but I would prefer not to do that.

 

[Peter_SSs]

I could not figure out any easier way than avg.rank=1 to identify the minimum value in a column AND guarantee that there are no ties.

So, did you actually try conditional formatting with either of the formulas suggested? (they both work for me)

.. and what about the suggestion in the first paragraph of my previous post? Best to try and make it as easy as possible for helpers to give you the best help.