3 highest values indicated using formatting
 

I'd like to highlight the 3 highest numbers in a column - a different color
for each high number. I figured I'd use conditional formatting and LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

Hi!

This is pretty tough and I couldn't figure out how to do it without the use
of a helper coulmn .....

Assume your values are in the range A1:A20.

In a helper column (I'll use column B) enter this formula with the key combo
of CTRL,SHIFT,ENTER in cell B2. Cell B1 MUST be empty:

=IF(ISERROR(MATCH(0,COUNTIF($B$1:B1,$A$1:$A$20),0) ),0,INDEX($A$1:$A$20,MATCH(0,COUNTIF($B$1:B1,$A$1: $A$20),0)))

Copy down to B21

This will extract the unique values from column A, A1:A20.

Now, you can base the cf on those extracted values.

Select the range A1:A20
Goto FormatConditional Formatting

Condition 1
Formula is: =A1=LARGE(B$2:B$21,1)
Select your desired format style

Click ADD

Condition 2
Formula is: =A1=LARGE(B$2:B$21,2)
Select your desired format style

Click ADD

Condition 3
Formula is: =A1=LARGE(B$2:B$21,3)
Select your desired format style

OK out

Also is there a limit on the length of a formula?

For a worksheet cell the max length is 1024 chars. For those formula boxes
in user forms like the one you will use for the cf, I think the length limit
is 255 chars.

Biff

"John" wrote in message
...
I'd like to highlight the 3 highest numbers in a column - a different
color
for each high number. I figured I'd use conditional formatting and LARGE,
but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

On Wed, 11 May 2005 20:53:02 -0700, "John"
wrote:

I'd like to highlight the 3 highest numbers in a column - a different color
for each high number. I figured I'd use conditional formatting and LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

What do you want to happen in the event of duplicate numbers?


--ron

Duplicate numbers should all have the same format, ie all the highest #'s are
red, the second highest #'s are all blue, etc...

"Ron Rosenfeld" wrote:

On Wed, 11 May 2005 20:53:02 -0700, "John"
wrote:

I'd like to highlight the 3 highest numbers in a column - a different color
for each high number. I figured I'd use conditional formatting and LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

What do you want to happen in the event of duplicate numbers?


--ron

Easiest solution I guess would be to get the 3 largest values in a sepaprate
area in worksheet and do conditional formatting by referenceing to it
say you insert these 3 largest values in Z1,z2 and z3 (assuming that your
data is in a1:a1000)

For largest on cell Z1 type
=Max(a1:a1000)

On Z2 (2nd largest )
=max(if(a1:a1000<z1,a1:a1000))
array entered (ctrl+shift+enter)

On Z3 (for 3rd largest)
=max(if(a1:a1000<z2,a1:a1000))
array entered (ctrl+shift+enter)

"John" wrote in message
...
Duplicate numbers should all have the same format, ie all the highest #'s
are
red, the second highest #'s are all blue, etc...

"Ron Rosenfeld" wrote:

On Wed, 11 May 2005 20:53:02 -0700, "John"

wrote:

I'd like to highlight the 3 highest numbers in a column - a different
color
for each high number. I figured I'd use conditional formatting and
LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

What do you want to happen in the event of duplicate numbers?


--ron

Much easier than extracting all the uniques! Why didn't I think of that? :-)

Biff

"N Harkawat" wrote in message
...
Easiest solution I guess would be to get the 3 largest values in a
sepaprate area in worksheet and do conditional formatting by referenceing
to it
say you insert these 3 largest values in Z1,z2 and z3 (assuming that your
data is in a1:a1000)

For largest on cell Z1 type
=Max(a1:a1000)

On Z2 (2nd largest )
=max(if(a1:a1000<z1,a1:a1000))
array entered (ctrl+shift+enter)

On Z3 (for 3rd largest)
=max(if(a1:a1000<z2,a1:a1000))
array entered (ctrl+shift+enter)

"John" wrote in message
...
Duplicate numbers should all have the same format, ie all the highest #'s
are
red, the second highest #'s are all blue, etc...

"Ron Rosenfeld" wrote:

On Wed, 11 May 2005 20:53:02 -0700, "John"

wrote:

I'd like to highlight the 3 highest numbers in a column - a different
color
for each high number. I figured I'd use conditional formatting and
LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

What do you want to happen in the event of duplicate numbers?


--ron

On Thu, 12 May 2005 06:47:31 -0700, "John"
wrote:

Duplicate numbers should all have the same format, ie all the highest #'s are
red, the second highest #'s are all blue, etc...

See N Harkawat's solution later in this thread.
--ron

Hey thanks, worked great!!!

"N Harkawat" wrote:

Easiest solution I guess would be to get the 3 largest values in a sepaprate
area in worksheet and do conditional formatting by referenceing to it
say you insert these 3 largest values in Z1,z2 and z3 (assuming that your
data is in a1:a1000)

For largest on cell Z1 type
=Max(a1:a1000)

On Z2 (2nd largest )
=max(if(a1:a1000<z1,a1:a1000))
array entered (ctrl+shift+enter)

On Z3 (for 3rd largest)
=max(if(a1:a1000<z2,a1:a1000))
array entered (ctrl+shift+enter)

"John" wrote in message
...
Duplicate numbers should all have the same format, ie all the highest #'s
are
red, the second highest #'s are all blue, etc...

"Ron Rosenfeld" wrote:

On Wed, 11 May 2005 20:53:02 -0700, "John"

wrote:

I'd like to highlight the 3 highest numbers in a column - a different
color
for each high number. I figured I'd use conditional formatting and
LARGE, but
there are duplicate numbers, which should all be highlighted.

Also is there a limit on the length of a formula?

Thanks!!!!

What do you want to happen in the event of duplicate numbers?


--ron