Percentrank function for Ranking

[xpous]

I'm trying to use the Percentrank function and I am able to use it right as long as I define my set or group but, I need to set the group dynamically based on another column.

Column E = Teacher's Name
Column AH = Average grade through the year.

In Column AJ I want to see the PercentRank of all the students with the same teacher (Column E).

The PercentRank function requires 2 parameters, an array and the reference value to compare. I don't know how to make that array to be dynamic based on the Column E.

 

[smkyle]

One option is to use E:E as your range reference. I generally avoid that, but it might solve your problem. Another option is to insert row above the last row. So if your last entry is row 25, then insert a row into 24 instead of starting to type in row 26. You can then paste values up a row and start typing on the bottom. There are more complicated solutions so let me know if either of those is ok for you.

 

[xpous]

I can do it manually using the percentagerank function as it is suppose to (by selecting the Range manually). The problem is that I need it to be dynamic based on the value in Column E. Putting E:E won't do anything. That will not return the appropriate range/array with ONLY those records with the same Teacher as the row being evaluated.

 

[barry houdini]

You can use an "array formula", for example if you have data from row 2 to row 100 try this formula in AJ2

=PERCENTRANK(IF(E$2:E$100=E2,AH$2:AH$100),AH2)

confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula, then copy down the column

 

[smkyle]

gotcha. Is a pivot table an option? Are you willing to sort the teacher names A to Z. If so you can use offset and countif.

 

[xpous]

Thanks Barry. That function seems to work when no other records are in AH (grades) of other teachers. For example, I have "Teacher 1", "Teacher 2" and "Teacher 3" (in Column E) and I have (for Teacher 1) the following grades (per student/row) : 43,33,34,14,45,31,24. If I use the percentrank function selecting manually the range of AH (grades) for that particular Teacher 1, the result is the following values (per row) : 83.3%, 50%, 66.6%, 0%, 100%, 33.3% and 16.6%. If I apply the formula you provided, I end up with: 54.8%, 45.1%, 48.3%, 6.4%, 58%, 35.4% and 25.8%. I removed all the values from AH except the values of the rows with Teacher 1 in it.. and your formula returns the correct values, but not when having all the Teachers results. It seems that it is not "filtering" the AH column range based on the column E (teacher's name).

 

[xpous]

If I use the following function

=PERCENTRANK(IF($E:$E=$E19,AH19:AH25),AH19)
RESULT = GOOD

If I use the function below (as suggested):

=PERCENTRANK(IF($E:$E=$E19,AH:AH),AH19)
RESULT = BAD

The problem is that the IF function should return the array to the function PERCENTRANK. The only way I have found so far to get a GOOD result is to manually select the Array of the IF function (Eg. AH19:AH25) but I need to do that for each of the records and I need some way to do it dynamically with a formula. I need a function that can return an array while that array is part of another function, not by doing the CTRL + SHIFT + ENTER which turns the entire formula into an array.

I'm not even sure if the IF is the right function to use or if VLOOKUP, MATCH, ROWS, or any other would better return the array to the PERCENTRANK.

 

[barry houdini]

Sounds like you may not have "array entered" the formula correctly, you need to enter in a single cell first

To do that put the formula in the first row, select that cell and then press F2 key to select formula. Now hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar and you should get the required result.

Now copy formula down the column.

CTRL+SHIFT+ENTER has to be re-applied if you change the formula

 

[xpous]

Hi Barry:

Now it works!. I was seeing the { } when I did the CTRL + SHIFT + ENTER but don't know if the F2 made any difference.
Thanks!!!