+ Reply to Thread
Results 1 to 5 of 5

Format the lowest valued cell without effecting duplicates

  1. #1
    Registered User
    Join Date
    04-12-2015
    Location
    CO, USA
    MS-Off Ver
    2013 pro plus
    Posts
    4

    Format the lowest valued cell without effecting duplicates

    I've tried using conditional formatting function but still cant figure it out. I made a school grade spreadsheet, I used the SMALL function to drop 1 of the lowest grades from a set of values and it worked fine. But now I'm trying to format only one of my scores by changing its color, etc. But there are some cells with the same duplicate values and it makes the changes to all the lowest cells. In other words, when I'm trying to identify just one of the lowest scores, it changes the color to all the same valued cells. How do I make these changes?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Format the lowest valued cell without effecting duplicates

    which 1 of the duplicates would you want to change color on?

    Have you considered using a helper column for a tie-breaker?

    A
    B
    2
    1
    1
    3
    2
    2
    4
    2
    3
    5
    2
    4
    6
    3
    3
    7
    4
    4
    8
    5
    5
    9
    5
    6
    10
    5
    7

    B2=A2+COUNTIF($A$2:A2,A2)-1

    This column could be hidden, and you could create a CF rule something like this...
    Highlight A2:A10
    rule would be - Use Formula...
    =B2=SMALL(B$2:B$10,1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-12-2015
    Location
    CO, USA
    MS-Off Ver
    2013 pro plus
    Posts
    4

    Re: Format the lowest valued cell without effecting duplicates

    Thanks for the reply but it doesn't necessarily help, or at least I don't think it does. I only want to highlight or strikethrough, etc., the lowest score of each category. I have the grading figured already by using: "=SUM(C5:C19)-SMALL(C5:C19,1)". But, this only does the math and drops one of the lowest score, whether there's a duplicate or not, and this works great. But visually, I would like to actually SHOW which score is being dropped. I first used the "conditional formatting" "format only the top and bottom ranked values" and only the "1" lowest value and then setting the format to changed the color red and strikethrough. But this will select all the lowest scores because they have duplicates and not just one of them. Here's just one of the tables I'm doing this on:
    Week Homework Discussion Exam
    1 9 10 21.87
    2 9 9 20.68
    3 8 10 16.67
    4 8 9 30
    5 10 10 20.67
    6 7 10 21.34
    7 10 9.5 22.41
    8 7 9 30
    9 10 10 30
    10 10 10 Spring Break
    11 9.5 10 24
    12 9 10 24.01

    So, some of the exams and the discussions have duplicates, and I want to just highlight ONE of the lowest homework, discussion and exam scores as I add new score throughout the semester (15 weeks total). Now, its highlighted all the 7's in homework, the 9's in discussion, and the one 16.67 in exam. the math is right, this is just for a visual aid. Attached is my BLANK excel spreadsheet, the areas that show "error's", will start working as soon as you put scores for each week.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-12-2015
    Location
    CO, USA
    MS-Off Ver
    2013 pro plus
    Posts
    4

    Re: Format the lowest valued cell without effecting duplicates

    Grades Official new test.xlsxAgain, everything works great, just can't figure out how to format only one of the lowest scores for each category, instead of all of them. here is a partially filled spreadsheet that might show my problem better:

  5. #5
    Registered User
    Join Date
    04-12-2015
    Location
    CO, USA
    MS-Off Ver
    2013 pro plus
    Posts
    4

    Re: Format the lowest valued cell without effecting duplicates

    Disregard all the random numbers outside the table, this is just a draft and those mean nothing. Only the cells inside the table, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Remove 'duplicates' to leaving lowest value
    By alseeon in forum Excel General
    Replies: 9
    Last Post: 06-29-2015, 06:22 AM
  2. Highlight lowest 5 out of 10 scores in a row without duplicates
    By Jjoseph6969 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-29-2013, 06:14 PM
  3. [SOLVED] Conditional Format for valued cell only
    By pra4ash in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-30-2013, 12:04 AM
  4. Conditional Formatting Lowest Value of Duplicates
    By n3sky in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-21-2011, 05:48 PM
  5. How do I format cells and enter data to be valued for TIME and th.
    By George in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2005, 03:06 PM

Tags for this Thread

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