+ Reply to Thread
Results 1 to 5 of 5

Need help with COUNTIF - Perform a Percentile and RANK

  1. #1
    Registered User
    Join Date
    11-10-2022
    Location
    ASIA
    MS-Off Ver
    Latest
    Posts
    8

    Need help with COUNTIF - Perform a Percentile and RANK

    Hello,

    Attached excel sheet is an example of the problem I am facing.

    I have performed the percentage and ranking based on the range in the example.

    I am unable to do it with Percentile and Rank.

    Please help.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Need help with COUNTIF - Perform a Percentile and RANK

    Why do you want to use COUNTIF when the latest version of excel provides all the functions you need?

    Read up on PERCENTILE and PERCENTRANK functions.

    PS. The reason why your COUNTIF function is not working is that your range of scores is text and not numbers.
    Last edited by Croweater; 02-18-2023 at 07:01 PM.

  3. #3
    Registered User
    Join Date
    11-10-2022
    Location
    ASIA
    MS-Off Ver
    Latest
    Posts
    8

    Re: Need help with COUNTIF - Perform a Percentile and RANK

    I think the COUNTIF doesn't allow VALUE in it. For the same reason, I am unable to convert within the format within the formula.

    I understand, I can simple convert the column on the toolbar and perform. But I would like to learn if there is a way we can do it using formula.

    Also, could you share the working formula for PERCENTILE and PERCENTRANK. I failed to get a solution with it.

    And, the reason I started to work using COUNTIF is because, I am trying to define all of the PERCENTILE math from the scratch.

    Thanks in advance.

  4. #4
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Need help with COUNTIF - Perform a Percentile and RANK

    I'm not really sure what you are getting at but believe me the reason that you are having issues is that your scores are TEXT as opposed to NUMBERS.

    You can't just change the format of the cells and expect it to work. There are a few ways to do it (Google is your friend), but one way is to put the number 1 in a spare cell, copy it, highlight your range and paste special>multiply and then select values in the paste options.

    You may then find your COUNTIF functions will work and your percentile functions will also work. OR, you can work out your percentiles from scratch.
    Last edited by Croweater; 02-19-2023 at 06:28 PM.

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2505 and WPS V2024(12.1.0.18543)
    Posts
    4,113

    Re: Need help with COUNTIF - Perform a Percentile and RANK

    Cell J26 formula , Drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. How to Determine Values of Percentile and Percentile Rank
    By rbellotti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-30-2019, 09:57 AM
  2. Replies: 3
    Last Post: 07-18-2017, 04:02 PM
  3. Help Needed Percentile rank based off of an array
    By drcline87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2017, 01:36 PM
  4. Percentile Rank w/ Numerical Array
    By jdanfor1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-05-2016, 01:58 PM
  5. weird rank/percentile question
    By jtrugman in forum Excel General
    Replies: 0
    Last Post: 11-03-2009, 06:10 PM
  6. identifying percentile range to perform averages
    By wilshire in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-25-2009, 01:10 AM
  7. percentile rank change
    By dujang in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-13-2006, 05:44 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