Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    LARGE Function Dilemma (Excel 2003 SP1)

    I have a list of numbers between 100 and 200 that are randomly listed in B5:B500. I want to have column C list (once) in descending order each number that appears in column B. Then in column D I want to show how many times the corresponding number in column C occurs in the overall list in column B. See attached sample worksheet, scanned with NAV.

    My problem is that when I use the LARGE function in column C, it returns multiple instances of the same number (i.e., cells C11:C13; C14:C16; C19:C20, etc. in sample worksheet)

    How can I change my formula in column C so that multiple instances of the same number will not be returned?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LARGE Function Dilemma (Excel 2003 SP1)

    If you download and install Laurent Longre's free Morefunc add-in from Excel add-ins, you can use the UNIQUEVALUES function:
    - Select F5:F29 (or whatever range you want, as long as it's large enough to hold all distinct values)
    - Enter the formula =UNIQUEVALUES(B5:B29) and confirm with Ctrl+Shift+Enter to make it an array formula.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LARGE Function Dilemma (Excel 2003 SP1)

    Does the attached work for you?
    Starting in row 5

    <table border=1><td>C</td><td>D</td><td>=max(range)</td><td>=counitif(range,c5)</td>
    <td>=LARGE(range,SUM($D$55)+1)</td><td>=counitif(range,c6)</td></table>

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: LARGE Function Dilemma (Excel 2003 SP1)

    Thanks for your replies. I chose mbarron

Posting Permissions

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