Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!!! |
#2
|
|||
|
|||
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!!!! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Need conditional formatting formula to highlight top ten values i. | Excel Worksheet Functions | |||
Values w/conditional formatting | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Formatting cells in Excel for certain Values to appear certain Col | Excel Worksheet Functions |