Highlighting the first and second lowest value excluding any zero entries

[BigKeyes13K]

I am attempting to highlight the lowest and second lowest value, separately, in a field of data, ignoring any entries of zero. The lowest value would be highlighted green, the second lowest value would be highlighted yellow so the formulas must be separate for each, and I am looking to highlight only that number. I am rather novice at this and I have been trying to piece together formulas from these forum groups, adding dollar signs however those work, just stabbing blindly until it works. My current attempt to accomplish this is as follows:

This has given me the following result.

The red is from a separate rule affecting the row so that's fine. It has identified the correct number for lowest and second lowest, but it is highlighting every number lower, not just that number and only that number. The green rule was given a higher priority just so it would show up at all. As I type it now occurs to me that I could create a higher priority rule to have no fill for any number that is zero, but I do kind of want to know the correct way to write this.

Thank You.

 

[awoohaw]

how does this work:

WorkBook1.xlsx
	A	B
1
2	26
3	22
4	8
5	6
6	29
7	11
8
9	24
10	21
11	5
12
13
Sheet16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=AGGREGATE(15,4,$A$2:A300,2)=A2	text	NO
A2:A11	Expression	=AGGREGATE(15,4,$A$2:A300,1)=A2	text	NO

 

[awoohaw]

how does this work:

WorkBook1.xlsx
	A	B
1
2	26
3	22
4	8
5	6
6	29
7	11
8
9	24
10	21
11	5
12
13
Sheet16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=AGGREGATE(15,4,$A$2:A300,2)=A2	text	NO
A2:A11	Expression	=AGGREGATE(15,4,$A$2:A300,1)=A2	text	NO

this includes zero, better version coming.

 

[awoohaw]

WorkBook1.xlsx
	A	B
1
2	26
3	22
4	8
5	6
6	29
7	0
8
9	24
10	21
11	5
12
Sheet16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=1/AGGREGATE(14,6,1/(--($A$2:A300<>0)*($A$2:A300)),2)=A2	text	NO
A2:A11	Expression	=1/AGGREGATE(14,6,1/(--($A$2:A300<>0)*($A$2:A300)),1)=A2	text	NO

The Large aggregate function works in this instance because the inverse of the maximum of the inverse of the minimum sequence get the largest inverses. then the inverse of that large gets the minimum value.
One of the attributes in the aggregate function is to ignore errors, so the Div/0 errors are removed.

 

[Peter_SSs]

Welcome to the MrExcel board!

Another take on using AGGREGATE

23 02 12.xlsm
	F
5	26
6	22
7	8
8	6
9	29
10	0
11
12	24
13	21
14	5
15
CF1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:F15	Expression	=F5=AGGREGATE(15,6,F$5:F$15,2+COUNTIF(F$5:F$15,0))	text	NO
F5:F15	Expression	=F5=AGGREGATE(15,6,F$5:F$15,1+COUNTIF(F$5:F$15,0))	text	NO

The lowest value would be highlighted green, the second lowest value would be highlighted yellow

I am unsure about your stated requirement though. Is data like below possible and, if so, what cell(s) should be highlighted what colour(s)?

23 02 12.xlsm
	F
5	26
6	0
7	5
8	5
9	29
10	0
11
12	6
13	6
14	5
15
CF2

 

[BigKeyes13K]

Welcome to the MrExcel board!

Another take on using AGGREGATE

23 02 12.xlsm
	F
5	26
6	22
7	8
8	6
9	29
10	0
11
12	24
13	21
14	5
15
CF1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:F15	Expression	=F5=AGGREGATE(15,6,F$5:F$15,2+COUNTIF(F$5:F$15,0))	text	NO
F5:F15	Expression	=F5=AGGREGATE(15,6,F$5:F$15,1+COUNTIF(F$5:F$15,0))	text	NO

I am unsure about your stated requirement though. Is data like below possible and, if so, what cell(s) should be highlighted what colour(s)?

23 02 12.xlsm
	F
5	26
6	0
7	5
8	5
9	29
10	0
11
12	6
13	6
14	5
15
CF2

Your data option could be possible. If that was what I was working with then all 5s would be green and all 6s would be yellow.

I went with your option since it was a simple copy and paste and with my current data it seems to be working correctly after having removed my cheat to unfill all zero data.

Thank you.

 

[Peter_SSs]

Your data option could be possible. If that was what I was working with then all 5s would be green and all 6s would be yellow.

Try these Conditional Formatting formula rules then

For green

=F5=INDEX(SORT(UNIQUE(FILTER(F$5:F$15,F$5:F$15<>0))),1)

For yellow

=F5=INDEX(SORT(UNIQUE(FILTER(F$5:F$15,F$5:F$15<>0))),2)

(I have not posted as a Mini Sheet since the colours showed correctly in my actual worksheet, but incorrectly when I converted to Mini Sheet. I will try to find out why that happened)

 

[BigKeyes13K]

I thought I had tested your first formula and found it worked, but I just tested it again and it doesn't. The second (and third) formula you provided does work. I also discovered that my columns i ranked in descending order weren't working with duplicates. I figured out how to modify your formula to work in reverse order.

Thank you.

 

[Peter_SSs]

Glad you got it sorted, Thanks for letting us know.

 

[BigKeyes13K]

I'm trying to do the same thing with a row now. I'm trying to identify the first place and second place data in row 18. Row 17 is providing the data to provide the ratio result in row 18. The cells in row 18 are merged if that is affecting the formula. I thought that by inserting "false" into the sort command for "by column" it would sort the row, but it's made no difference on the results; it's just turning everything green. Is one of the other commands overriding the sort command function for by column or by row?

The formula in question are the first two on the list for the conditional formatting.

Washington County Weekly Active.xlsx
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
17	20	2	2	9	1	2	9	3	3	15	2	2	8	3	2	27	0	0
18	0.10			0.11			0.33			0.13			0.38			0.00
WASHINGTON

Cell Formulas
Range	Formula
M17:AD17	M17	=SUM(M6:M16)
M18,P18,S18,V18,Y18,AB18	M18	=N17/M17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M18:AD18	Expression	=M18=INDEX(SORT(UNIQUE(FILTER(M$18:AD$18,M$18:AD$18<>0)),,-1,FALSE),2)	text	NO
M18:AD18	Expression	=M18=INDEX(SORT(UNIQUE(FILTER(M$18:AD$18,M$18:AD$18<>0)),,-1,FALSE),1)	text	NO
C17:AD17,C20:K20,C25:G25,C6:C16,G6:G16,J6:J16	Cell	contains a blank value	text	NO
M17:O17,M3:O5	Expression	=$AW$5=1	text	NO
M17:O17,M3:O5	Expression	=$AW$5=2	text	NO
P17:R17,P3:R5	Expression	=$AW$6=1	text	NO
P17:R17,P3:R5	Expression	=$AW$6=2	text	NO
S17:U17,S3:U5	Expression	=$AW$7=1	text	NO
S17:U17,S3:U5	Expression	=$AW$7=2	text	NO
V17:X17,V3:X5	Expression	=$AW$8=1	text	NO
V17:X17,V3:X5	Expression	=$AW$8=2	text	NO
Y17:AA17,Y3:AA5	Expression	=$AW$9=1	text	NO
Y17:AA17,Y3:AA5	Expression	=$AW$9=2	text	NO
AB17:AD17,AB3:AD5	Expression	=$AW$10=1	text	NO
AB17:AD17,AB3:AD5	Expression	=$AW$10=2	text	NO
M17:O17,M3:O5	Expression	=$N$17=0	text	NO
P17:R17,P3:R5	Expression	=$Q$17=0	text	NO
S17:U17,S3:U5	Expression	=$T$17=0	text	NO
V17:X17,V3:X5	Expression	=$W$17=0	text	NO
Y17:AA17,Y3:AA5	Expression	=$Z$17=0	text	NO
AB17:AD17,AB3:AD5	Expression	=$AC$17=0	text	NO