Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    5 highest unique numbers (2003)

    How can I obtain the 5 highest unique numbers in a range of data?

    The data are in A1: C6, the awaited results are in Column E
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>2</td><td align=right valign=bottom>51</td><td align=right valign=bottom>*</td><td align=center valign=bottom>5 top</td><td align=center valign=bottom>2</td><td align=right valign=bottom>5</td><td align=right valign=bottom>11</td><td align=right valign=bottom>7</td><td align=right valign=bottom>*</td><td align=right valign=bottom>51</td><td align=center valign=bottom>3</td><td align=right valign=bottom>21</td><td align=right valign=bottom>41</td><td align=right valign=bottom>21</td><td align=right valign=bottom>*</td><td align=right valign=bottom>44</td><td align=center valign=bottom>4</td><td align=right valign=bottom>19</td><td align=right valign=bottom>11</td><td align=right valign=bottom>12</td><td align=right valign=bottom>*</td><td align=right valign=bottom>41</td><td align=center valign=bottom>5</td><td align=right valign=bottom>31</td><td align=right valign=bottom>22</td><td align=right valign=bottom>31</td><td align=right valign=bottom>*</td><td align=right valign=bottom>31</td><td align=center valign=bottom>6</td><td align=right valign=bottom>44</td><td align=right valign=bottom>51</td><td align=right valign=bottom>31</td><td align=right valign=bottom>*</td><td align=right valign=bottom>22</td></table>

    TIA

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 5 highest unique numbers (2003)

    Editted: -05:19 sorry Servando I just realised this only returns the highest unique number not the highest 5.

    Hi Servando

    I have tried this =AVERAGE((LARGE($A$1:$C$500,{1,2,3,4,5}))) but the problem is that in this example would return a decimal place unless set to 'number - no decimals' format. It might give you something to work on though

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 5 highest unique numbers (2003)

    No problem, I'll wait for another

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

    Re: 5 highest unique numbers (2003)

    See the attached workbook. (It was originally attached to <post:=579,127>post 579,127</post:> but lost in the server crash of August, 2007).
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 5 highest unique numbers (2003)

    Tanks Hans

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 5 highest unique numbers (2003)

    Hi all

    Would anyone explain what is -9E+307 and what does it do in the formula =MAX(IF($A$1:$A$41<E1,$A$1:$A$41,-9E+307))

    thanks

    regards,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: 5 highest unique numbers (2003)

    See <post:=579,149>post 579,149</post:> in the same thread that I referred to.

Posting Permissions

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