# Excel - Find Smallest and Largest.

Asked By dlbeile on 09-Oct-07 02:48 PM
```I have a column of random numbers and need to find the smallest, second
smallest, third smallest, third largest, second largest, largest and
highlight these numbers with different font colors.```

Don Guillett replied on 09-Oct-07 02:56 PM
```You might try looking in the help index for SMALL

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com```
ilia replied on 09-Oct-07 03:57 PM
```Use conditional formatting.  Unfortunately if you have Excel 2003 or
earlier, you can only set 3 rules, meaning 3 different colors.

Select the entire range, activate first cell.  My example is a list of
random numbers in cells A1:A42.  I select the entire range, and
activate A1.

Then, in conditional formatting dialog box use "Formula is" and type
in this:

=A1=SMALL(\$A\$1:\$A\$42,1)

This will return TRUE for the smallest number in range.  Select
Format, and choose a different font color.

For second smallest number, use this "Formula is" rule:

=A1=SMALL(\$A\$1:\$A\$42,2)

The opposite of this is the LARGE() function.  Largest number in range
will be found like this:

=A1=LARGE(\$A\$1:\$A\$42,1)

Once you've made up the rules, recalc a few times to ensure you are
getting the correct behavior.

On Oct 9, 2:48 pm, dlbeiler <dlbei...@>```
dlbeile replied on 10-Oct-07 01:30 AM
```Thank you for your response.  I am using Excel 2000 and am limited to 3
rules.  In that case, would it be possible to combine the smallest, second
smallest and third smallest into one rule and thus use the same font color to
identify all three?```
Don Guillett replied on 10-Oct-07 08:19 AM
```Try this idea for condition 1. Add small,2 in the middle.
=OR(O3=SMALL(\$O\$2:\$O\$8,1),O2=SMALL(\$O2:\$O8,3))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com```
Don Guillett replied on 10-Oct-07 09:16 AM
```Or 6 different colors if 6 different values. ie: three smallest the same =
color 34

Sub colorsmallest()
Set Rng = Range("p2:p18")
For Each c In Rng
Select Case c.Value
Case Application.Small(Rng, 1): mc = 34
Case Application.Small(Rng, 2): mc = 35
Case Application.Small(Rng, 3): mc = 36
Case Application.Large(Rng, 1): mc = 37
Case Application.Large(Rng, 2): mc = 38
Case Application.Large(Rng, 3): mc = 39
Case Else
mc = 0
End Select
If IsNumeric(c) Then c.Interior.ColorIndex = mc
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com```