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

 

[kevin9999]

Not sure what value you have in H2 that drives the values in the M column, but see if this gives you what you want. You apply the CF for each condition to the range $L$2:$M$51.

Book1
	L	M
2	10	0.00%
3	20	0.01%
4	30	0.01%
5	40	0.01%
6	50	0.01%
7	60	0.02%
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.03%
32	200	0.05%
33	300	0.08%
34	400	0.10%
35	500	0.13%
36	111000	27.75%
37	122000	30.50%
38	133000	33.25%
39	5	0.00%
40	1000	0.25%
41	2000	0.50%
42
43	4000	1.00%
44	5000	1.25%
45	6000	1.50%
46
47	8000	2.00%
48	9000	2.25%
49	10000	2.50%
50	11000	2.75%
51	12000	3.00%
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M51	Expression	=AND($L2<>"",$L2=MIN($L$2:$L$51))	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,3)	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,2)	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,1)	text	YES

 

[Peter_SSs]

the other 3000 records (each record has 50 entries as shown below

1. Where are those other records?
   
   
2. What should happen with your green/orange/red if two (or more) values in column L are equal highest or equal 2nd highest? For example, what cells should be coloured what colour with the following sample?

24 01 21.xlsm
	L
1
2	50
3	20
4	80
5	80
6	10
7	18
8	90
9	80
10
CF (2)

 

[mwvirk]

Not sure what value you have in H2 that drives the values in the M column, but see if this gives you what you want. You apply the CF for each condition to the range $L$2:$M$51.

Book1
	L	M
2	10	0.00%
3	20	0.01%
4	30	0.01%
5	40	0.01%
6	50	0.01%
7	60	0.02%
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.03%
32	200	0.05%
33	300	0.08%
34	400	0.10%
35	500	0.13%
36	111000	27.75%
37	122000	30.50%
38	133000	33.25%
39	5	0.00%
40	1000	0.25%
41	2000	0.50%
42
43	4000	1.00%
44	5000	1.25%
45	6000	1.50%
46
47	8000	2.00%
48	9000	2.25%
49	10000	2.50%
50	11000	2.75%
51	12000	3.00%
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M51	Expression	=AND($L2<>"",$L2=MIN($L$2:$L$51))	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,3)	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,2)	text	YES
L2:M51	Expression	=$L2=LARGE($L$2:$L$51,1)	text	YES

H2 has the total numbers whereas L2 has obtained numbers. based on these two cells, I'm calculating the average (%)

I took only the following and it looks fine now: (I'm sharing the results in the next post with @Peter_SSs

L2:M51

Expression

=AND($L2<>"",$L2=MIN($L$2:$L$51))

text

YES

 

[mwvirk]

Where are those other records?

it's a long list (50x3000) so I'm taking only the single entry

What should happen with your green/orange/red if two (or more) values in column L are equal highest or equal 2nd highest? For example, what cells should be coloured what colour with the following sample?

your question is valid. if the top 2 have the same numbers then they will considered at #1 & #2 - in this case runner-up will be getting the 3rd position and there will be no 3rd highlighted record. please advise if you can have a better opinion. thanks.
(I'm highlighting the 3rd position in yellow FYR only)

Election 2024.xlsx
	L	M
2	10	0.00%
3	20	0.00%
4	30	0.00%
5	40	0.00%
6	50	0.00%
7	60	0.00%
8	70	0.00%
9	80	0.00%
10	144,000	5.76%
11	144,000	5.76%
12	90	0.00%
13	90	0.00%
14	90	0.00%
15	3	0.00%
16	1	0.00%
17	90	0.00%
18	0	0.00%
19	0	0.00%
20	90	0.00%
21	90	0.00%
22	90	0.00%
23	3	0.00%
24	6	0.00%
25	1	0.00%
26
27
28
29	90	0.00%
30	90	0.00%
31	555,555	22.21%
32	555,555	22.21%
33	444,444	17.77%
34	333,333	13.33%
35	222	0.01%
36	222	0.01%
37	122,000	4.88%
38	133,000	5.32%
39	5	0.00%
40	1,000	0.04%
41	2,000	0.08%
42
43	4,000	0.16%
44	5,000	0.20%
45	6,000	0.24%
46
47	8,000	0.32%
48	9,000	0.36%
49	10,000	0.40%
50	11,000	0.44%
51	12,000	0.48%
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	=AND($L2<>"",$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]

it's a long list (50x3000) so I'm taking only the single entry

That does not tell me where the other are. For example, the next record might be in L52:M101 or it might be in L102:M151 or it might be in N2:O51 or it might be in ...

I was asking out of interest since you said ...

I will be copying the same condition formatting for the other 3000 records

.. that is a great deal of copying!
So I was wondering how you were actually going to do that.

if the top 2 have having same numbers then they will considered at #1 & #2 - in this case runner-up will be getting the 3rd position and there will be no 3rd highlighted record.

That still is not very clear to me. Here is a sample of different scenarios, all using the same CF rules. Does this look like what you want. If not copy these scenario values and colour the cells manually how you would want them coloured, post those with XL2BB and add and written explanation you can.

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	10
7	18
8	42
9	75
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=3	text	NO
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=2	text	NO
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=1	text	NO

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	10
7	18
8	42
9	90
10
Sheet1 (2)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	90
5	70
6	10
7	18
8	42
9	90
10
Sheet1 (3)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	80
7	18
8	42
9	90
10
Sheet1 (4)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	80
7	18
8	42
9	70
10
Sheet1 (5)

 

[mwvirk]

That does not tell me where the other are. For example, the next record might be in L52:M101 or it might be in L102:M151 or it might be in N2:O51 or it might be in ...

I was asking out of interest since you said ...

.. that is a great deal of copying!
So I was wondering how you were actually going to do that.



That still is not very clear to me. Here is a sample of different scenarios, all using the same CF rules. Does this look like what you want. If not copy these scenario values and colour the cells manually how you would want them coloured, post those with XL2BB and add and written explanation you can.

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	10
7	18
8	42
9	75
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=3	text	NO
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=2	text	NO
L2:M51	Expression	=RANK($L2,$L$2:$L$51)=1	text	NO

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	10
7	18
8	42
9	90
10
Sheet1 (2)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	90
5	70
6	10
7	18
8	42
9	90
10
Sheet1 (3)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	80
7	18
8	42
9	90
10
Sheet1 (4)

mwvirk.xlsm
	L	M
1
2	50
3	90
4	80
5	70
6	80
7	18
8	42
9	70
10
Sheet1 (5)

let me explain.
INFORMATION
H2 contains a number value which is the total
then from H2, different values are distributed from L2:L51 (this is record #1)
based on the distributed values in L2:L51, i am checking in % in M2:M51
here 1 record is completed.

now again for the 2nd record, H52 contains a total number value, different values are distributed from L52:L101 (this is record #2)
based on the distributed values in L52:L101, i am checking in % in M52:M101
here the 2nd record is completed.

and then it will continue...

CHANGE
it was noticed that having 3,000 records in a single sheet was causing slowness. so I have split the 800 records in my initial sheet.

IGNORE THIS
having duplicated values in L:L is nearly impossible. I've asked this since you raised it. I was thinking about the same but again, I am not expecting the duplicate values in L:L - so we can ignore it.

REQUIREMENTS (seeking help)
#1 - finally, the main concern is, how I can copy the same condition formatting from L2:L51 to L51:L101 and so on.

#2 - 1 more thing, there might be blank cells that I want to highlight in YELLOW (still pending) and if the cell value is 0 then highlight BLACK (this was done) but I also want to find the number that is the lowest but shouldn't be 0 (next higher number than 0)

 

[mwvirk]

i am done this this part. please check the other issues. thanks
there might be blank cells that I want to highlight in YELLOW (still pending)

 

[Peter_SSs]

IGNORE THIS

OK, ignoring possibilities of equal highest etc.
For the moment also ignoring the blank/low score requirement and just looking at the top 3.
Can we use a helper column? If so,

1. Use a formula like I have in column O below and copy it down to the bottom of your data.
2. You can then hide that column if you want to remove the visual effect.
3. Select from L2 down to the bottom of the column M data.
4. Apply the very simple conditional formatting shown. It should apply the CF to each individual 50-row section.

Not that in my sample I have hidden some rows to keep the mini-sheet smaller

mwvirk.xlsm
	L	M	N	O
1
2	50			5
3	90			1
4	80			2
5	70			4
6	10			8
7	18			7
8	42			6
9	75			3
10				#N/A
11				#N/A
12				#N/A
13				#N/A
14				#N/A
48				#N/A
49				#N/A
50				#N/A
51				#N/A
52	30			6
53	8			9
54	16			8
55	70			3
56	62			4
57	44			5
58	28			7
59				#N/A
83				#N/A
84				#N/A
85	86			2
86				#N/A
87				#N/A
88				#N/A
89				#N/A
90				#N/A
91				#N/A
92	92			1
93				#N/A
94				#N/A
99				#N/A
100				#N/A
101				#N/A
Sheet1

Cell Formulas
Range	Formula
O2:O14,O48:O59,O83:O94,O99:O101	O2	=RANK($L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M101	Expression	=$O2=3	text	NO
L2:M101	Expression	=$O2=2	text	NO
L2:M101	Expression	=$O2=1	text	NO

 

[mwvirk]

finding the top 3 issue are also resolved. so we can ignore it. thanks.
I also want to find the number that is the lowest but shouldn't be 0 (next higher number than 0) could you please share some CF for this?