+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    I have a list of numbers in A1:A12 as follows:
    8
    8
    8
    12
    12
    12
    12
    12
    12
    12
    12
    6
    I'm trying to conditionally format the two lowest numbers, in this case the "6" and one of the three "8's". When I used the Bottom rule in Conditional Formatting, it highlights the 6 and all three 8's, since those are the two lowest values in the list. In other words, after the conditional formatting, I always want 10 of the 12 numbers to remain unformatted.

    Thanks in advance for your help.

  2. #2
    Registered User
    Join Date
    06-01-2013
    Location
    Columbus, ms
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Try this one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Oops, sorry. I didn't read all your post. I tried editing the formula to work but I need to test it.
    Last edited by Spitzerpl; 06-08-2013 at 01:03 AM.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    hi ktbb0312, select the range you want to apply to (say from A1:A12)
    go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is
    =AND(OR(MIN($A$1:$A$12)=A1,SMALL($A$1:$A$12,2)=A1),COUNTIF(A$1:A1,A1)=1)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Thanks, Spitzerpl. It sort of worked. When I applied the formatting (in this case, changing the color of the selected cells to red), it changed 3 of them. It changed the 6 and the first 8, but it also changed the first 12. I then changed the two of the 12's to 13's and it changed the first 13 as well. It seems like it is taking the first number of each group and identifying it as one to be formatted, rather than the two lowest numbers.

  5. #5
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Thanks, Benishiryo. This worked fine until I made one change. I changed the 6 to an 8 and it did not highlight a second "8." I should have said this in my original post, but it conceivable that my data will end up being all 8's or all 12's or whatever number. Even in that case, I will need the rule to format two of the numbers despite the fact that they are all the same.

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    does this work for you then?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Close. It works as long as one of the other numbers is 7 or lower. If I change the 6 to an 8 or higher, it still only formats the first 8.

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers

    Very close. It works in all scenarios except for two of them. It works fine if I have the following numbers: 8,8,8,8,8,8,8,8,8,8,8,9. It selects the first two 8's like it should. But, if I change the 9 to an 8, then none of the numbers are formatted.

    The second scenario is similar. Using the same numbers as above, if I change the 9 to a 7 or less, it highlights the new number like it should, but not one of the 8's.

    Thanks for your help in working through this. I really appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1