Highline Excel Class 30: SUMIF, RANK & Bonuses

Hi,

I work at a University and I am trying calculate the students ranks based upon their GPA. I understand how to use the Rank function to determine their numerical ranking, however I need to find out each students percentage as they stand in their class.

For Example, the student with the highest and best GPA is the Top 1% of his or her class. Is there a way for Excel to automatically calculate their percentage ranking for each student, rather than having to type it in manually?

My current approach is do a percentile function 'PERCENTILE($X$186:$X$320,.99)' which will give me a GPA corresponding to that Percentage. I then have to take that GPA and manually type in the 1%, 2%, 3% and so on until I have inputted the % Ranks for each student. There must be an easier way, correct?

Thanks for your help!

Tony

Hello!

I am looking for a formula to select specific rows in a data sheet (according to a set of criterias), calculate the rank (RANK) for some of these rows (a new set of criterias), and then summarize those ranks. Is this possible and how would I do it? I was thinking about combining RANK with SUMIF but I have not been able to get it to work...

An example; I want to select all rows where column B="5", and then summarize the ranks of rows where B="5" and C="10".

(Each row of data can be in different "rank groups", so it is not possible to add an extra column for each row and calculate the rank directly in the data sheet; the rank will depend on which other rows are included in each set.)

If it helps, I need the ranks for the rows in order to do the Mann-Whitney test.

Hello all,

I have a list of "weights" in column H. They can all be ranked from 1 to 30 (none are exactly the same). I want to write 1 formula that sums the top 10 (biggest) weights without having to make another column for "weight rank". I tried the following as an array formula, but it didn't work....

=SUMIF($H$3:$H$32,RANK($H$3:$H$32,$H$3:$H$32)&">=10")

Any ideas?

Thanks

Hi All,
I'm stumped.
I have a list of values that I would like to rank. E.g Below

Cell Value Preferred Rank
A1 95 1
A2 130 3
A3 103 2
A4 103 4
A5 95 5

What I would like to do is rank the lowest value which is 95 and compare it to a preferred rank listing. As there are two values of 95, I want A1 to show as rank 1 and A5 as rank 2 as it has a lower preferred rank.

Another example:
A1 140 1
A2 130 3
A3 103 2
A4 103 4
A5 95 5

In this case A5 is the lowest value and there are no other duplicates, so this should be ranked as 1. However cell A3 and A4 are duplicates but based on the preferred rank they show as 2 and 4. Therefore the rank order should be A5 = 1, A3 = 2 and A4 = 3.

Confusing as hell . PLease let me know if you need me to elaborate further. Not sure if this can be done, that's why i'm here posting.

Please HELP

Bonuses are paid out based on whether or not an individual hits 100% of their quota. It's paid out quarterly based on how many of the three months the individual achieved 100%. For this example D2 through D4 = % for each month in the quarter & E5 = Payout

I need E5 to reflect what the individual will get in bonuses based on their results.

The criteria = If > or =100% for 1 of the 3 months, E5 = 125
If > or =100% for 2 of the 3 months, E5 = 200
If > or =100% for 3 of the 3 months, E5 = 325

can this be put into 1 formula to calculate quarters results...?

thanks

Hi. Brand new user.

I have a list of employees production values (In row A). From this, how do I equate the highest value to be 100% and each value below to be a percentage OF this 100%?

I will be using this monthly where the employee with 100% would get $500 each month, for example, and each employee below would get a percentage of $500. Each month the highest value would be different based on how fast they worked

Thanks so much!!!

Hi

I am attaching a workbook and there are two worksheets contained.

The first sheet has some staff ID numbers and $ amounts for product sold. This is just an idea - in reality it will be longer and have more staff member's ID noted.

The second sheet is for calculation of the staff bonus. The top two staff members for a month are paid a bonus. At the moment the bonus is 3.5% of the total sold for that staff member, but the total must be over the current amount of $1000. These figures (bonus percentage and amount to exceed) can change so I need to be able to change these as required on sheet 2 and the calculations will work.

Any help or advice appreciated.

Dave.

Please see below
******** ******************** ************************************************************************> Microsoft Excel - Book9 ___Running: 14.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 B13 B14 B15 B16 B17 B18 B19 B20 =
A B C D 1 Score Rank Random Rank 2 1 1 1.098 3 1 1 1.987 4 1 1 1.023 5 1 1 1.125 6 2 2 2 7 3 3 3.098 8 3 3 3.001 9 3 3 3.034 10 3 3 3.325 11 3 3 3.091 12 4 4 4 13 5 5 5 14 6 6 6.098 15 6 6 6.721 16 7 7 7.987 17 7 7 7.021 18 7 7 7.541 19 7 7 7.712 20 7 7 7.543 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.