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.
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.
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.
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.
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.
Cell J26 formula , Drag down
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks