How do I highlight the 2nd and 3rd highest values in a ROW in Excel?

[ttt637]

I know how to find the min and max, but for the life of me I need to conditionally format some data output for work and can't for the life of me find out how to make the 2nd highest value AMBER and the 3rd highest ORANGE.

Example

============ and I want to look like this====

 

[footoo]

excel highlight second highest value - Google Search

 

[ttt637]

Tried that already, no bueno. Thats what I came here

 

[Fluff]

What exactly did you try?

 

[ttt637]

I had already tried the google search result that was posted. Did not work.

 

[Fluff]

What was the formula that you tried?

 

[AhoyNC]

Assuming your data is in range K4:N5.

• Highlight the range K4:N5
• Go to Conditional Formatting
• New Rule
• For 2nd highest enter the formula =K4=LARGE($K4:$N4,2)
• Choose your color and press OK

You will need to repeat the steps for the 3rd highest number changing the formula to: =K4=LARGE($K4:$N4,3)

Book1
	K	L	M	N
1
2
3
4	10	20	30	40
5	50	40	15	2
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K4:N5	Expression	=K4=LARGE($K4:$N4,2)	text	NO
K4:N5	Expression	=K4=LARGE($K4:$N4,3)	text	NO

 

[ttt637]

1st off thank you for the help and responses. I thought I could do this with the example above but I can't figure out the $'s and ":" separators.

Here is what I am looking at, I manualy filled in colors for demo, but since days are ranked differrent that nights was not sure how to seperate the cells in the formula

 

[bebo021999]

Book3
	A	B	C	D	E	F	G	H	I
1		day	night	day	night	day	night	day	night
2	DIG	216	181	225	233	268	270	271	215
3
4
5
6
7
8
9
10
11
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I200	Expression	=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),4)=B2)	text	NO
B2:I200	Expression	=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),3)=B2)	text	NO
B2:I200	Expression	=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),2)=B2)	text	NO
B2:I200	Expression	=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),1)=B2)	text	NO

 

[ttt637]

WOW, you guys are SMART!! I would never in a millon years firgure this one out. I will give it a try when I get home. TY