+ Reply to Thread
Results 1 to 7 of 7

Marking top 50/10% highest numbers

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    derby
    MS-Off Ver
    Excel 365
    Posts
    3

    Marking top 50/10% highest numbers

    Hi
    I have a lot of data where I am trying to get the top 10% or top 50 (whichever is the smallest number) highlighted.
    I am currently using =IF(AC8648="","FALSE",AC8648>=PERCENTILE.EXC($AC$2:$AC$10375,0.9)), to get the top 10%, which worked until I got a data set as large as this one (the first bit is just to remove the blanks I have). Now I need to limit it to just the top 50 (what I want is a formula that can do both so I don't need to use a different formula for each sheet, as I will be giving this to others to use).

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Marking top 50/10% highest numbers

    If you have a large dataset I advice you to use Pivot Table to analyse the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Marking top 50/10% highest numbers

    There is no PERCENTILE.EXC function in Excel 2003. Please update your Excel version.

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Marking top 50/10% highest numbers

    Solution Marking top 50/10% highest numbers: Use Top/Bottom rules of Conditional formatting: See https://www.youtube.com/watch?v=ufOMOPbAC4k

  5. #5
    Registered User
    Join Date
    12-14-2012
    Location
    derby
    MS-Off Ver
    Excel 365
    Posts
    3

    Re: Marking top 50/10% highest numbers

    Thanks, forgot I had set which version I was using in my profile

  6. #6
    Registered User
    Join Date
    12-14-2012
    Location
    derby
    MS-Off Ver
    Excel 365
    Posts
    3

    Re: Marking top 50/10% highest numbers

    Thanks HansDouwe but that only allows me to do either top 50 or top 10%, it wont let me have the option of both but use which is less (if I have 1000 then I want the top 50, but if I have 50 then I want the top 10% which would be 5), I am not sure of a way you can do that using those rules on conditional formatting.

  7. #7
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Marking top 50/10% highest numbers

    Yes, that is also possible, but a little bit more complicated.
    You should work with formula in conditional formatting.
    Please attach a representative sample sheet (see yellow banner)

+ 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. Replies: 3
    Last Post: 06-09-2016, 12:51 PM
  2. Counting reference numbers and marking the latest date as a 1
    By blaketague in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-06-2014, 11:52 AM
  3. [SOLVED] ROUNDING Formula marking if it rounds numbers below x.5
    By drrazor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2014, 04:41 PM
  4. Replies: 3
    Last Post: 12-23-2012, 08:53 PM
  5. Find top 2 lowest numbers and top 2 highest numbers
    By photographex in forum Excel General
    Replies: 7
    Last Post: 02-12-2011, 10:57 PM
  6. Replies: 6
    Last Post: 02-24-2010, 11:48 PM
  7. Replies: 1
    Last Post: 01-09-2006, 09:30 AM

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