condition formatting to find the highest and runner up numbers

[mwvirk]

i have numbers in cell A1 to A35
i am using the condition formatting 'TOP' (1) to find the highest number but i cannot find a way to find the runner-up number
please help. thanks

10
9
20
21
22
33 so here, it's the highest number and i can find it with TOP 1
1
0
6
32 and here is the next (runner up) number. how can i mark it.
22

118,000​	27.44%
50,000​	11.63%
40,000​	9.30%
0​	0.00%
50,000​	11.63%
16,100​	3.74%
118,001​	27.44%
5,000​	1.16%
8,000​	1.86%
5,000​	1.16%
20,000​	4.65%

 

[kvsrinivasamurthy]

To find second highest number

=Large($A$1:$A$35,2)

 

[Gerald Higgins]

If you want to format both the largest, and second largest values in exactly the same way, then you could use CF but choose the option to "Use a formula to determine which cells to format".

Then, if your data is in the range A1:A11, use a formula like this -
=OR(A1=MAX(A$1:A$11),A1=LARGE(A$1:A$11,2))

And apply a format as required.

If you want the highest and second highest values to be formated differently from each other, apply two conditional formats, one using the MAX part of the above (or your current format) and one using the LARGE part.

This may not work as you would like, if the MAX and second highest values might be duplicated.

 

[mwvirk]

To find second highest number

=Large($A$1:$A$35,2)

it change the colour of entire range

 

[mwvirk]

If you want to format both the largest, and second largest values in exactly the same way, then you could use CF but choose the option to "Use a formula to determine which cells to format".

Then, if your data is in the range A1:A11, use a formula like this -
=OR(A1=MAX(A$1:A$11),A1=LARGE(A$1:A$11,2))

And apply a format as required.

If you want the highest and second highest values to be formated differently from each other, apply two conditional formats, one using the MAX part of the above (or your current format) and one using the LARGE part.

This may not work as you would like, if the MAX and second highest values might be duplicated.

thank you. it worked but i want to mark #1 in green and #2 in red. but here it mark both in read

L Row	M Row
Votes	% Of Candidate Votes
118,000​	18.76%
50,000​	7.95%
119,000​	18.92%
120,000​	19.07%
50,000​	7.95%
16,100​	2.56%
118,001​	18.76%
5,000​	0.79%
8,000​	1.27%
5,000​	0.79%
20,000​	3.18%

 

[mwvirk]

If you want to format both the largest, and second largest values in exactly the same way, then you could use CF but choose the option to "Use a formula to determine which cells to format".

Then, if your data is in the range A1:A11, use a formula like this -
=OR(A1=MAX(A$1:A$11),A1=LARGE(A$1:A$11,2))

And apply a format as required.

If you want the highest and second highest values to be formated differently from each other, apply two conditional formats, one using the MAX part of the above (or your current format) and one using the LARGE part.

This may not work as you would like, if the MAX and second highest values might be duplicated.

OK.... it worked. i just apply another condition with green colour:
=OR(L3=MAX(L$3:L$13),L3=LARGE(L$3:L$13,1))

bundle of thanks and appreciate your prompt support

 

[mwvirk]

I need to ask another question here.

since the highest number is resolved and also the runner-up. how can I take the names of the persons who got the highest and next to the highest person?

I am splitting the sheet into 2 parts (i cannot install XL2BB on my office computer) sorry for this

A Row (merged cell A3 to A13) (values in this range is unique)	B Row (merged cell B3 to B13) (values in this range is NOT unique)	C Row (merged cell C3 to C13)	D Row (merged cell D3 to D13) (values in this range is unique)	E Row (merged cell E3 to E13)	F Row (merged cell F3 to F13)	G Row (merged cell G3 to G13)	H Row (merged cell H3 to H13)	I Row (merged cell I3 to I13)
NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes
NA-001	Area-1	NA-032 (Tribal Areas IV) City-1	City-1	318,150	245,602	500	245,102	77.20%

J Row	K Row	L Row	M Row	N Row	O Row	P Row	Q Row
Candidate Name	Party	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party
Candiate 001	Party 1	18,000​	7.34%
Candiate 002	Party 2	50,000​	20.40%
Candiate 003	Party 3	19,000​	7.75%
Candiate 004	Party 4	20,000​	8.16%
Candiate 005	Party 5	50,001​	20.40%
Candiate 006	Party 6	16,100​	6.57%
Candiate 007	Independent	18,001​	7.34%
Candiate 008	Party 7	21,000​	8.57%
Candiate 009	Independent	8,000​	3.26%
Candiate 010	Independent	5,000​	2.04%
Candiate 011	Party 8	20,000​	8.16%

 

[Gerald Higgins]

Something like this for the highest -

=index(J2:J100,match(MAX(L2:L100),L2:L100,0)

where col J has the names, and col L has the number of votes.

From this, and from previous posts in this thread, you should be able to work out how to show the name for the second highest number of votes.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: find the winner name from highest numbers
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mwvirk]

Something like this for the highest -

=index(J2:J100,match(MAX(L2:L100),L2:L100,0)

where col J has the names, and col L has the number of votes.

From this, and from previous posts in this thread, you should be able to work out how to show the name for the second highest number of votes.

thanks dude. i tried the

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: find the winner name from highest numbers
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

yes. sorry for the inconvenience. it was posted there and i found that there is no reply. i tried removing it but failed.