LinkBack URL
About LinkBacks
Hi there,
Can you tell me how to run the formula under conditional format where I can highlight the 6 smallest number in a range, this include zero.
174 195 206
173 181 188
210 195 211
211 182 192
0 0 0
Kindly advice.
Cheers
Raymond
Hi there,
Can you tell me how to run the formula under conditional format where I can highlight the 6 smallest number in a range, this include zero.
174 195 206
173 181 188
210 195 211
211 182 192
0 0 0
Kindly advice.
Cheers
Raymond
The 6 smallest numbers or the 6th smallest number???
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
6 smallest number:
174 195 206
173 181 188
210 195 211
211 182 192
0 0 0
so the following number will be highlighted - 0,0,0,173,174,181
What version of Excel are you using?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I cannot post for some reason.
use a formula that selects every number smaller than the 7th smallest, use the small function.
Last edited by mehmetcik; 12-10-2016 at 12:46 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Conditional formatting dialog - format only cells that contain - cell value - less than - =SMALL($A$1:$C$5,7)
where $A$1:$C$5 is your array.
With a helper column in E1:E6
enter formula in E1 and copy down
Formula:
Please Login or Register to view this content.
then got to Conditional Formatting and choose "Use a formoula..." and enter this formula:
Formula:Please Login or Register to view this content.
apply desired color and make sure it applies to: =$A$1:$C$5
v A B C D E 1 174 195 206 0 2 173 181 188 0 3 210 195 211 0 4 211 182 192 173 5 0 0 0 174 6 181
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Assuming you're using Excel 2007 or later...
Let's assume the numbers are in the range A2:C6.
Select the ENTIRE range A2:C6 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.
Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format
Enter this formula in the box below:
=A2<=SMALL($A$2:$C$6,6)
Click the Format button
Select the desired style(s)
OK out
This can also be done without any formulas:
Highlight range A1:C5
Go to Conditional Formatting Rules Manager
Click on New Formatting Rule and select Format only top or bottom ranked values
Change Top to Bottom and adjust values to 6 and apply desired color
Click Ok
Thank you all - it works now. I use the conditional Formatting Rules Manager.
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks