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

Report •


#1
July 3, 2012 at 08:30:01
✔ Best Answer
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 •

Ask Question