Find Smallest and Largest.

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.

You might try looking in the help index for SMALL

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dlbeiler" wrote in message
...
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.

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

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?

"ilia" wrote:

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

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

"dlbeiler" wrote in message
...
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.

Or 6 different colors if 6 different values. ie: three smallest the same =
color 34

Sub colorsmallest()
Set Rng = Range("p218")
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

"Don Guillett" wrote in message
...
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

"dlbeiler" wrote in message
...
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.