Percentile Rank w/ Numerical Array

[jdanfor1]

I am attempting to have a formula that can find values within the range of 200-800, even though not every number in that range is present within the excel dataset. So, for example, if a score is between 200-242, that would have a percentile rank of >1 (or, for analysis purposes, 0), scores from 243-325 have a percentile rank of 1 and etc.

With the way the formula's default settings (Whether it's PERCENT, PERCENTILE, or either of the .ex or .inc versions of the formula) are, I can't customize my array and I can't customize what certain numerical ranges mean in terms of percentile rank. I feel like I'm missing something. Am I? I'd appreciate all the help I can get here.

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jdanfor1]

I think you managed to answer my question without having to attach my workbook, actually. Seems like the problem I'm having is that I want the formula to reflect the entire range of scores (200-800) though the range present on the spreadsheet is different (243-783). So without a dataset that has the entire range of numbers I want, I can't have a formula that explores the percentile rank of said range?

Does that make sense?

[FlameRetired]

It's been awhile since I took statistics so I am not entirely sure I understand, but I tried this:

With for example (423-783) the desired range I entered the lower boundary 423 in J1 and the upper 783 in K1. This permits you to change the range of data to whatever you want and put it into an array 423-783.

Then this formula

Please Login or Register  to view this content.

Does this give you any workable ideas?

Edit I failed to mention that this formula needs to be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[jdanfor1]

I'll try your idea sometime today, thank you for the suggestion!

[FlameRetired]

You are welcome.

[jdanfor1]

Seems like I couldn't get the formula to work. I kept getting 260 no matter how I used the formula. I do appreciate the recommendation though! I'll see if I can find another way to figure it out.

[FlameRetired]

What is the formula you are using and what are the upper and lower boundaries. Also is your formula being array entered with Ctrl + Shift + Enter and not just Enter?

[jdanfor1]

After some toying around here is what I've decided to use. It works well enough for very specific percents, but since I want a range of scores to represent one particular percentile ranking this just falls short of what I need.

EDIT: How do I attach documents? I tried using the paperclip in front of a paper icon and it won't let me attach anything.

[FlameRetired]

jdanfor1,

Unfortunately that paperclip icon hasn't worked for some time.


To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.