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
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:


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:


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


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.

Don Guillett
Microsoft MVP Excel
SalesAid Software
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
End Sub

Don Guillett
Microsoft MVP Excel
SalesAid Software