find the top 3 and lowest number using the condition formatting

[mwvirk]

Hi,
I am trying to find the top 3 numbers in a cell range and then the lowest number but it seems it's not working to get the lowest number or sometimes it's changing the condition formatting for the full range. Like if the cell value is 0 then it will be considered as the lowest number and marked as BLACK but if somehow the cell value is blank then it's still marked as BLACK.
I would appreciate it if someone could help me here no matter if you want to change the color of the blank value to some other color.

Please also note that I will be copying the same condition formatting for the other 3000 records (each record has 50 entries as shown below screenshot (1 record of 50 entries)
All cells will have numeric values only.

Election 2024.xlsx
	L	M
2	10	0.00%
3	20	0.00%
4	30	0.01%
5	40	0.01%
6	50	0.01%
7	60	0.01%
8	70	0.02%
9	80	0.02%
10	90	0.02%
11	90	0.02%
12	90	0.02%
13	90	0.02%
14	90	0.02%
15	3	0.00%
16	1	0.00%
17	90	0.02%
18	0	0.00%
19	90	0.02%
20	90	0.02%
21	90	0.02%
22	90	0.02%
23	3	0.00%
24	6	0.00%
25	1	0.00%
26	90	0.02%
27	90	0.02%
28	90	0.02%
29	90	0.02%
30	90	0.02%
31	100	0.02%
32	200	0.05%
33	300	0.07%
34	400	0.09%
35	500	0.11%
36	111,000	25.39%
37	122,000	27.90%
38	133,000	30.42%
39	5	0.00%
40	1,000	0.23%
41	2,000	0.46%
42
43	4,000	0.91%
44	5,000	1.14%
45	6,000	1.37%
46
47	8,000	1.83%
48	9,000	2.06%
49	10,000	2.29%
50	11,000	2.52%
51	12,000	2.74%
Election 2022 (MNA)

Cell Formulas
Range	Formula
M2:M51	M2	=IF(L2="","",L2/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L51	Expression	=OR(L2=MIN(L$2:L$51))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,1))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,2))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,3))	text	NO
M2:M51	Expression	=OR(M2=MIN(M$2:M$51))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,1))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,2))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,3))	text	NO

 

[Peter_SSs]

Thanks. Unfortunately I still don't fully understand. For example

1. Why is cell J110 highlighted when it does not have a duplicate party name?
   
   
2. There is a duplicate party name in K124 & K125. Why isn't anything in those rows highlighted?

 

[mwvirk]

Thanks. Unfortunately I still don't fully understand. For example

1. Why is cell J110 highlighted when it does not have a duplicate party name?
   
   
2. There is a duplicate party name in K124 & K125. Why isn't anything in those rows highlighted?

1. J110 is highlighted because there are other candidates with the same name
2. I think I missed to highlight K124 & K125
requirements are: if the party name is a duplicate then the candidate name must be highlighted, and if the candidate name is a duplicate the part name must be highlighted. similarly, if there are duplicate candidate name or party names then must be highlighted

 

[Peter_SSs]

Thanks for the clarifications. See if you prefer one of these.

Two helper columns and one CF rule for both columns ..

mwvirk.xlsm
	J	K						Q	R
1
2	Candidate Name 0001	Party Name A						FALSE	FALSE
3	Candidate Name 0002	Party Name B						FALSE	FALSE
4	Candidate Name 0003	Party Name C						FALSE	FALSE
5	Candidate Name 0004	Party Name D						FALSE	FALSE
6	Candidate Name 0005	Party Name E						FALSE	FALSE
7	Candidate Name 0006	Party Name F						FALSE	FALSE
8	Candidate Name 0007	Party Name G						FALSE	FALSE
9	Candidate Name 0008	Party Name H						FALSE	FALSE
10	Candidate Name 0009	Party Name I						FALSE	FALSE
11	Candidate Name 0010	Party Name J						FALSE	FALSE
12	Candidate Name 0011	Party Name K						FALSE	FALSE
13	Candidate Name 0012	Party Name L						FALSE	FALSE
14	Candidate Name 0013							FALSE	FALSE
15	Candidate Name 0014	Party Name N						FALSE	FALSE
16	Candidate Name 0015	Party Name O						FALSE	FALSE
17	Candidate Name 0016	Party Name P						FALSE	FALSE
18	Candidate Name 0017	Party Name Q						FALSE	FALSE
19	Candidate Name 0018	Party Name R						FALSE	FALSE
20		Party Name S						FALSE	FALSE
21	Candidate Name 0020	Party Name T						FALSE	FALSE
22	Candidate Name 0021	Party Name U						FALSE	FALSE
23	Candidate Name 0022	Party Name V						FALSE	FALSE
24	Candidate Name 0023	Party Name W						TRUE	TRUE
25	Candidate Name 0023	Party Name W						TRUE	TRUE
26	Candidate Name 0023	Party Name W						TRUE	TRUE
27	Candidate Name 0026	Party Name Z						FALSE	FALSE
28	Candidate Name 0027	Party Name AA						FALSE	FALSE
29	Candidate Name 0028	Party Name AB						FALSE	FALSE
30	Candidate Name 0029	Party Name AC						FALSE	FALSE
31	Candidate Name 0030	Party Name AD						FALSE	FALSE
32	Candidate Name 0031	Party Name AE						FALSE	FALSE
Sheet3

Cell Formulas
Range	Formula
Q2:R32	Q2	=COUNTIF(INDEX(J:J,FLOOR(ROW()-2,50)+2):INDEX(J:J,FLOOR(ROW()-2,50)+51),J2)>1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:K151	Expression	=Q2	text	NO

.. or one helper column and a separate CF rule for each column

mwvirk.xlsm
	J	K						Q
1
2	Candidate Name 0001	Party Name A						00
3	Candidate Name 0002	Party Name B						00
4	Candidate Name 0003	Party Name C						00
5	Candidate Name 0004	Party Name D						00
6	Candidate Name 0005	Party Name E						00
7	Candidate Name 0006	Party Name F						00
8	Candidate Name 0007	Party Name G						00
9	Candidate Name 0008	Party Name H						00
10	Candidate Name 0009	Party Name I						00
11	Candidate Name 0010	Party Name J						00
12	Candidate Name 0011	Party Name K						00
13	Candidate Name 0012	Party Name L						00
14	Candidate Name 0013							00
15	Candidate Name 0014	Party Name N						00
16	Candidate Name 0015	Party Name O						00
17	Candidate Name 0016	Party Name P						00
18	Candidate Name 0017	Party Name Q						00
19	Candidate Name 0018	Party Name R						00
20		Party Name S						00
21	Candidate Name 0020	Party Name T						00
22	Candidate Name 0021	Party Name U						00
23	Candidate Name 0022	Party Name V						00
24	Candidate Name 0023	Party Name W						11
25	Candidate Name 0023	Party Name W						11
26	Candidate Name 0023	Party Name W						11
27	Candidate Name 0026	Party Name Z						00
28	Candidate Name 0027	Party Name AA						00
29	Candidate Name 0028	Party Name AB						00
30	Candidate Name 0029	Party Name AC						00
31	Candidate Name 0030	Party Name AD						00
32	Candidate Name 0031	Party Name AE						00
Sheet4

Cell Formulas
Range	Formula
Q2:Q32	Q2	=--(COUNTIF(INDEX(J:J,FLOOR(ROW()-2,50)+2):INDEX(J:J,FLOOR(ROW()-2,50)+51),J2)>1)&--(COUNTIF(INDEX(K:K,FLOOR(ROW()-2,50)+2):INDEX(K:K,FLOOR(ROW()-2,50)+51),K2)>1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K137	Expression	=RIGHT(Q2,1)="1"	text	NO
J2:J151	Expression	=LEFT(Q2,1)="1"	text	NO

 

[mwvirk]

Thanks a lot, Peter. 1 helping and 2 CF are better so I can have different colours for J & K
the issue seems resolved. I need to run some tests.
here I am sharing 1 full record.

R to Z are empty because I have some data to input manually.

Election 2024.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	318,150	3,954,792	9,000	3,945,792	1243.06%	Candidate Name 0001	Party Name A	10	0.00%	Candidate Name 0030	Party Name AD	Candidate Name 0034	Party Name AH			1122	1122	1122	1122	1122			37	6	00
3						Candidate Name 0002	Party Name B	20	0.00%														36	7	00
4						Candidate Name 0003	Party Name C	30	0.00%														35	8	00
5						Candidate Name 0004	Party Name D	40	0.00%														34	9	00
6						Candidate Name 0005	Party Name E	50	0.00%														33	10	00
7						Candidate Name 0006	Party Name F	0	0.00%														43	-2	00
8						Candidate Name 0007	Party Name G	70	0.00%														32	11	00
9						Candidate Name 0008	Party Name H																43	-2	00
10						Candidate Name 0009		144,000	3.65%														6	36	00
11							Party Name J	144,000	3.65%														6	36	00
12						Candidate Name 0011	Party Name K	90	0.00%														23	13	00
13						Candidate Name 0012	Party Name L	90	0.00%														23	13	00
14						Candidate Name 0013		90	0.00%														23	13	00
15						Candidate Name 0014	Party Name N	3	0.00%														39	3	00
16						Candidate Name 0015	Party Name O	88															31	12	00
17						Candidate Name 0016	Party Name P	90	0.00%														23	13	00
18						Candidate Name 0017	Party Name Q	99	0.00%														21	21	00
19						Candidate Name 0018	Party Name R	0	0.00%														43	-2	00
20							Party Name S	90	0.00%														23	13	00
21						Candidate Name 0020	Party Name T	90	0.00%														23	13	00
22						Candidate Name 0021	Party Name U																43	-2	00
23						Candidate Name 0022	Party Name V	3	0.00%														39	3	00
24						Candidate Name 0023	Party Name W	1	0.00%														42	1	11
25						Candidate Name 0023	Party Name W	2	0.00%														41	2	11
26						Candidate Name 0023	Party Name W	0	0.00%														43	-2	11
27						Candidate Name 0026	Party Name Z	99	0.00%														21	21	00
28						Candidate Name 0027	Party Name AA																43	-2	00
29						Candidate Name 0028	Party Name AB	90	0.00%														23	13	00
30						Candidate Name 0029	Party Name AC	90	0.00%														23	13	00
31						Candidate Name 0030	Party Name AD	999,999	25.34%														1	42	00
32						Candidate Name 0031	Party Name AE	666,666	16.90%														3	40	00
33						Candidate Name 0032	Party Name AF	444,444	11.26%														4	39	00
34						Candidate Name 0033	Party Name AG	333,333	8.45%														5	38	00
35						Candidate Name 0034	Party Name AH	888,888	22.53%														2	41	00
36						Candidate Name 0035	Party Name AI	222	0.01%														20	23	00
37						Candidate Name 0036	Party Name AJ	122,000	3.09%														9	34	00
38						Candidate Name 0037	Party Name AK	133,000	3.37%														8	35	00
39						Candidate Name 0038	Party Name AL	5	0.00%														38	5	00
40						Candidate Name 0039	Party Name AM	1,000	0.03%														19	24	00
41						Candidate Name 0040	Party Name AN	2,000	0.05%														18	25	00
42						Candidate Name 0041	Party Name AO																43	-2	00
43						Candidate Name 0042	Party Name AP	4,000	0.10%														17	26	00
44						Candidate Name 0043	Party Name AQ	5,000	0.13%														16	27	00
45						Candidate Name 0044	Party Name AR	6,000	0.15%														15	28	00
46						Candidate Name 0045	Party Name AS																43	-2	00
47						Candidate Name 0046	Party Name AT	8,000	0.20%														14	29	00
48						Candidate Name 0047	Party Name AU	9,000	0.23%														13	30	00
49						Candidate Name 0048	Party Name AV	10,000	0.25%														12	31	00
50						Candidate Name 0049	Party Name AW	11,000	0.28%														11	32	00
51						Candidate Name 0050	Party Name AX	12,000	0.30%														10	33	00
Election 2022 (MNA)

Cell Formulas
Range	Formula
F2	F2	=IF(H2="","",H2+G2)
H2	H2	=SUM($L$2:$L$51)
I2	I2	=IF(OR(E2="",F2=""),"",F2/E2)
N2	N2	=IFNA(INDEX($J$2:$J$51,MATCH(MAX($L$2:$L$51),$L$2:$L$51,0)),"")
O2	O2	=IFNA(INDEX($K$2:$K$51,MATCH(MAX($L$2:$L$51),$L$2:$L$51,0)),"")
P2	P2	=IFERROR(INDEX($J$2:$J$51,MATCH(LARGE($L$2:$L$51,2),$L$2:$L$51,0)),"")
Q2	Q2	=IFERROR(INDEX($K$2:$K$51,MATCH(LARGE($L$2:$L$51,2),$L$2:$L$51,0)),"")
AA2:AA51	AA2	=RANK($L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51))
AB2:AB51	AB2	=RANK(L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51),1)-COUNTIF(INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51),0)
AC2:AC51	AC2	=--(COUNTIF(INDEX(J:J,FLOOR(ROW()-2,50)+2):INDEX(J:J,FLOOR(ROW()-2,50)+51),J2)>1)&--(COUNTIF(INDEX(K:K,FLOOR(ROW()-2,50)+2):INDEX(K:K,FLOOR(ROW()-2,50)+51),K2)>1)
M17:M51,M2:M15	M2	=IF(L2="","",L2/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K13601	Expression	=RIGHT(AC2,1)="1"	text	NO
J2:J13601	Expression	=LEFT(AC2,1)="1"	text	NO
J2:M13601	Cell	contains a blank value	text	NO
L2:M13601	Expression	=AND($L2<>"",$L2=MIN($L2:$M13601))	text	NO
L2:M13601	Expression	=$AB2=1	text	NO
L2:M13601	Expression	=$AA2=3	text	NO
L2:M13601	Expression	=$AA2=2	text	NO
L2:M13601	Expression	=$AA2=1	text	NO
P2:Q51	Cell	does not contain a blank value	text	NO
N2:O51	Cell	does not contain a blank value	text	NO
I2:I51	Cell Value	<0.5	text	NO
I2:I51	Cell Value	between 1 and 0.5	text	NO
I2:I51	Cell Value	>1	text	NO
N2:Q51	Cell	contains a blank value	text	NO
F2:F51	Cell Value	>$E2	text	NO
F2:F51	Cell Value	>0	text	NO
H2:H51	Cell Value	>$E2	text	NO

 

[Peter_SSs]

the issue seems resolved.

Good news. Thanks for letting us know.
If you do come back with further questions, please use something other than black as the formatting colour while giving samples in the forum.