+ Reply to Thread
Results 1 to 6 of 6

Smallest number in a column, only if there are no duplicates

  1. #1
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Smallest number in a column, only if there are no duplicates

    My first post.

    Making a golf club score card recap sheet to use to tally all 28 members scores at our scheduled tournaments. I would like to identify in a column the lowest score posted on each hole. I know about the min/max, but I only want the number to appear if there is no duplicates.

    Should I add a min cell at the bottom of each of the 18 columns with the formula?

    Is there a way to highlight the low number in each column?

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Smallest number in a column, only if there are no duplicates

    Hello,

    to show the min value you can try

    =IF(COUNTIF(MyRange,MIN(MyRange))>1,"",MIN(MyRange))

    In conditional formatting use this formula

    =AND(A1=MIN(MyRange),COUNTIF(MyRange,MIN(MyRange))=1)

    Change A1 to the first cell in MyRange. MyRange is the range of cells you want to monitor.

    cheers

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Smallest number in a column, only if there are no duplicates

    If your scores are in A1:A28 you can use conditional formatting with this formula to highlight only the unique minimum (if one exists)

    =(A1=MIN(A$1:A$28))*(COUNTIF(A$1:A$28,A1)=1)
    Audere est facere

  4. #4
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: Smallest number in a column, only if there are no duplicates

    Tried the min formula, didn't work. Not sure if "countif" is what I am looking for. Looking for the smallest number. ???

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Smallest number in a column, only if there are no duplicates

    "didn't work" is not good enough. All formulas offered in this thread work.

    see attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: Smallest number in a column, only if there are no duplicates

    My apologies. IT DID WORK. My first attempt at using conditional formatting failed. Looked up some basic info or the procedure, inserted your formula, and it worked perfectly.

    Thank you.

+ 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