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%

 

[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.

i tried this formula for the runner-up but it didn't work:

=INDEX(J3:J13,MATCH(MAX(L3:L13),L3:L13,1))

 

[Gerald Higgins]

i tried this formula for the runner-up but it didn't work:

=INDEX(J3:J13,MATCH(MAX(L3:L13),L3:L13,1))

That's because it uses the MAX function, which is good for finding the MAXimum value.
You need to use the LARGE function instead.

 

[mwvirk]

That's because it uses the MAX function, which is good for finding the MAXimum value.
You need to use the LARGE function instead.

thanks for your reply.
you mean i have to change the formulas for the winner (name & party) and then runner-up and party?
if possible, may i request you to please share the correct formulas so i can close this task.
thanks

 

[Gerald Higgins]

Yes, that's what I mean.
An example of the LARGE function was given in post #3 in this thread, as part of a larger solution.
Another very similar example of the LARGE function was given by another user in post #2 in this thread.

 

[mwvirk]

I cannot fix the runner-up issue.
post #2 & #3 you are referring to only to find the max number. but here i need the name based on the higher number.
thank you once again for the kind support.

 

[Gerald Higgins]

post #2 & #3 you are referring to only to find the max number.

No, that's not correct.

Post #2 in this thread (by user kvsrinivasamurthy) was specifically about finding the 2nd largest number in a range.

And post #3 was about highlighting the largest and 2nd largest numbers in a range.
The part of that solution that dealt with the 2nd largest number was basically the same as the solution in post #2.

 

[mwvirk]

look... i need the name from Row 'J' in Row 'P' and party name from Row 'K' in Row 'Q' as a runner-up name and party
the one showing in Row 'N' & 'O' are correct but Row 'P' & 'Q' are wrong

formula in Row N =INDEX(J3:J13,MATCH(MAX(L3:L13),L3:L13,0))
formula in Row O =INDEX(K3:K13,MATCH(MAX(L3:L13),L3:L13,0))
formula in Row P =INDEX(J3:J13,MATCH(MAX(L3:L13),L3:L13,1))
formula in Row Q =INDEX(K3:K13,MATCH(MAX(L3:L13),L3:L13,1))

 

[Gerald Higgins]

So, for columns P and Q, replace the MAX part of your function, with the LARGE function that was suggested in posts 2 and 3.

 

[mwvirk]

finally it's DONE
you can see the formula.
for winner, i am still using MAX
and for the runner-up, i am using LARGE
Thanks a lot for the help.

 

[mwvirk]

I have completed the sheet and found that the entire record is showing the results as per requirements but when I copy the cells/values in the next record then it's not working properly:

error indicator in H N O P Q
in L, it's not marking the highest number in green
in L, it's not marking the lowest number in red
in N & O it's showing the winner candidate and party name but still showing an error indicator
in P & Q it's not showing the runner up candidate and party name and showing an error indicator

in short, all formulas, and condition checks are working only for the 1st record - Need help again, please. Thanks

Test-0001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party
2	NA-001	Province-1	Name-001	Area-001	318,150	100,085,257	500	100,084,757	31458.51%	Candidate Name 0001	Party Name AA	1,000	0.00%	Candidate Name 0013	Party Name AM	Candidate Name 0006	Party Name AF
3										Candidate Name 0002	Party Name AB	500	0.00%
4										Candidate Name 0003	Party Name AC	2	0.00%
5										Candidate Name 0004	Party Name AD	999	0.00%
6										Candidate Name 0005	Party Name AE	4	0.00%
7										Candidate Name 0006	Party Name AF	27,300	0.03%
8										Candidate Name 0007	Party Name AG	6	0.00%
9										Candidate Name 0008	Party Name AH	7	0.00%
10										Candidate Name 0009	Party Name AI	27,300	0.03%
11										Candidate Name 0010	Party Name AJ	10	0.00%
12										Candidate Name 0011	Party Name AK	11	0.00%
13										Candidate Name 0012	Party Name AL	12	0.00%
14										Candidate Name 0013	Party Name AM	99,999,999	99.92%
15										Candidate Name 0014	Party Name AN	14	0.00%
16										Candidate Name 0015	Party Name AO	15	0.00%
17										Candidate Name 0016	Party Name AP	16	0.00%
18										Candidate Name 0017	Party Name AQ	17	0.00%
19										Candidate Name 0018	Party Name AR	18	0.00%
20										Candidate Name 0019	Party Name AS	19	0.00%
21										Candidate Name 0020	Party Name AT	20	0.00%
22										Candidate Name 0021	Party Name AU	21	0.00%
23										Candidate Name 0022	Party Name AV	22	0.00%
24										Candidate Name 0023	Party Name AW	23	0.00%
25										Candidate Name 0024	Party Name AX	24	0.00%
26										Candidate Name 0025	Party Name AY	25	0.00%
27										Candidate Name 0026	Party Name AZ	26	0.00%
28										Candidate Name 0027	Party Name BA	27,300	0.03%
29										Candidate Name 0028	Party Name BB	28	0.00%
30										Candidate Name 0029	Party Name BC	9	0.00%
31										Candidate Name 0030	Party Name BD	10	0.00%
32	NA-002	Province-1	Name-002	Area-002	318,150	10,069,174	500	10,068,674	3164.91%	Candidate Name 0001	Party Name AA	1,000	0.00%	Candidate Name 0017	Party Name AQ	#N/A	#N/A
33										Candidate Name 0002	Party Name AB	500	0.00%
34										Candidate Name 0003	Party Name AC	2	0.00%
35										Candidate Name 0004	Party Name AD	33,333	0.03%
36										Candidate Name 0005	Party Name AE	44,444	0.04%
37										Candidate Name 0006	Party Name AF	27	0.00%
38										Candidate Name 0007	Party Name AG	6	0.00%
39										Candidate Name 0008	Party Name AH	7	0.00%
40										Candidate Name 0009	Party Name AI	22	0.00%
41										Candidate Name 0010	Party Name AJ	10	0.00%
42										Candidate Name 0011	Party Name AK	11	0.00%
43										Candidate Name 0012	Party Name AL	12	0.00%
44										Candidate Name 0013	Party Name AM	99	0.00%
45										Candidate Name 0014	Party Name AN	14	0.00%
46										Candidate Name 0015	Party Name AO	15	0.00%
47										Candidate Name 0016	Party Name AP	16	0.00%
48										Candidate Name 0017	Party Name AQ	9,988,888	9.98%
49										Candidate Name 0018	Party Name AR	18	0.00%
50										Candidate Name 0019	Party Name AS	19	0.00%
51										Candidate Name 0020	Party Name AT	20	0.00%
52										Candidate Name 0021	Party Name AU	21	0.00%
53										Candidate Name 0022	Party Name AV	22	0.00%
54										Candidate Name 0023	Party Name AW	23	0.00%
55										Candidate Name 0024	Party Name AX	24	0.00%
56										Candidate Name 0025	Party Name AY	25	0.00%
57										Candidate Name 0026	Party Name AZ	26	0.00%
58										Candidate Name 0027	Party Name BA	23	0.00%
59										Candidate Name 0028	Party Name BB	28	0.00%
60										Candidate Name 0029	Party Name BC	9	0.00%
61										Candidate Name 0030	Party Name BD	10	0.00%
62	NA-003	Province-1	Name-003	Area-003						Candidate Name 0001	Party Name AA
63										Candidate Name 0002	Party Name AB
64										Candidate Name 0003	Party Name AC
65										Candidate Name 0004	Party Name AD
66										Candidate Name 0005	Party Name AE
67										Candidate Name 0006	Party Name AF
68										Candidate Name 0007	Party Name AG
69										Candidate Name 0008	Party Name AH
70										Candidate Name 0009	Party Name AI
71										Candidate Name 0010	Party Name AJ
72										Candidate Name 0011	Party Name AK
73										Candidate Name 0012	Party Name AL
74										Candidate Name 0013	Party Name AM
75										Candidate Name 0014	Party Name AN
76										Candidate Name 0015	Party Name AO
77										Candidate Name 0016	Party Name AP
78										Candidate Name 0017	Party Name AQ
79										Candidate Name 0018	Party Name AR
80										Candidate Name 0019	Party Name AS
81										Candidate Name 0020	Party Name AT
82										Candidate Name 0021	Party Name AU
83										Candidate Name 0022	Party Name AV
84										Candidate Name 0023	Party Name AW
85										Candidate Name 0024	Party Name AX
86										Candidate Name 0025	Party Name AY
87										Candidate Name 0026	Party Name AZ
88										Candidate Name 0027	Party Name BA
89										Candidate Name 0028	Party Name BB
90										Candidate Name 0029	Party Name BC
91										Candidate Name 0030	Party Name BD
Election 2018 (MNA)

Cell Formulas
Range	Formula
H2,H32	H2	=SUM(L2:L31)
I2,I32	I2	=IF(E2="","",F2/E2)
M2:M61	M2	=IF(L2="","",L2/$H$2)
N2,N32	N2	=INDEX(J2:J31,MATCH(MAX(L2:L31),L2:L31,0))
O2,O32	O2	=INDEX(K2:K31,MATCH(MAX(L2:L31),L2:L31,0))
P2,P32	P2	=INDEX(J2:J31,MATCH(LARGE($L$2:$L$31,2),L2:L31,0))
Q2,Q32	Q2	=INDEX(K2:K31,MATCH(LARGE($L$2:$L$31,2),L2:L31,0))
F2,F32	F2	=H2+G2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,1))	text	NO
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,2))	text	NO
F32:F61	Cell Value	>$E$2	text	NO
F32:F61	Cell Value	>0	text	NO
H32:H61	Cell Value	>$E$2	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,1))	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,2))	text	NO
F2:F31	Cell Value	>$E$2	text	NO
F2:F31	Cell Value	>0	text	NO
H2:H31	Cell Value	>$E$2	text	NO