# Solved excel conditional formatting mark 3 smallest in column

July 3, 2012 at 06:32:34
Specs: Windows 7
 =rank(ch1200,ch\$1200:ch\$1211) < 4 is the formula i am using for largest which i gleaned from this site.i have tried every variation in condition 2 for the smallest with no good luck. i am using =rank(ch1200,ch\$1200:ch\$1211) > 3 but must change the 3 as the number of entries varies from 4 to 12. please help

See More: excel conditional formatting mark 3 smallest in column

#1
July 3, 2012 at 08:30:01
 There might be a better way, but this was the first thing that came to mind.I wasn't sure what you meant by "the number of entries varies from 4 to 12" so I included the first part (AND(CH1200<>"",...) to handle blank cells within the range.=AND(CH1200<>"",OR(CH1200=SMALL(CH\$1200:CH\$1211,1),CH1200=SMALL(CH\$1200:CH\$1211,2),CH1200=SMALL(CH\$1200:CH\$1211,3)))If you wanted to be consistant, you could use the LARGE function in a similar fashion to find the highest 3 numbers.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
July 3, 2012 at 14:26:16
 JUST GOT BACK HOME, THANKS FOR THE INFO. THERE ARE NO BLANK CELLS IN THE COLUMN, SO I WILL FIGURE IT OUT. IS THERE A WAY TO COPY AND PASTE? IF NOT I BEST GET STARTED, GOTTA DO IT ABOUT 60 TIMES, ITS OK THO, I'VE GOT COFFEE.THANKS AGAIN FOR YOUR HELP

Report •

#3
July 3, 2012 at 15:28:43
 Please don't post in all upper case letters. That's the internet equivalent of shouting a no one likes to be yelled at.You can Copy and Paste within Conditional Formatting with Ctrl-c and Ctrl-v.Without knowing the layout/location of your 60 cells I can't offer much more than that. There may be a way to re-write the CF formula to account for multiple ranges. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
July 3, 2012 at 16:09:51
 thanks, did not mean to holler at ya.but thanks again. i'll be back with another question some day.

Report •