Relative Ranking Formula

[phnee]

This is for a school. I need to rank the students based on family information.

FamilyID = one ID per family
StudentID = unique student ID
Grade = student's grade
TeachingFamily = binary, do they have a parent who teaches at the school
Status = are they a Full Time or Part Time? (not binary, as their other factors that I didn't bother to explain)

I have to rank the students by grade WITHIN EACH FAMILY. The oldest student receives a 4% discount, the second oldest receives 7%, and any other younger students receive a 40% discount.

In addition, the student must have the following criteria:
TeachingFamily = NO
Status = Full Time

If I use the Countif formula, then the spreadsheet must be sorted by FamilyID and StudentID. If I change the sort, the rankings change.

To restate the above:
The oldest, Full Time, non-Teaching Family student receives a 4% discount.
Second oldest Full Time student receives a 7% discount.
All other younger Full Time students receives a 40% discount.
If you are a Part Time student within this same family, you do not receive any discount.

Also, keep in mind that there may be a family with twins/triplets. If they are the oldest or second oldest, only one receives the 4% and 7% discount.

I'm trying to avoid a macro or VBA because I am not skilled in these areas.

I am using Excel for Mac 2011. Hope this doesn't make a difference!

Thanks in advance.

 

[PCRIDE]

Ok, I have it.

Its complicated so I'll provide the link and you can download the file.

This is what I did.

built a data table with your columns. The bolded ones are Calculated fields in your data table. FamID is Column A.

FamID StuID Grade TeachingFam Status MET? Age Rank

In the MET column I did this.
Find out if the user meets your first requirement
TeachingFamily = NO
Status = Full Time

=IF(AND(D2=0,E2="Full Time"),"YES","NO")

Then use Excel RANK Formula to determine the rank of age based on the input on the age column.
=RANK(G2,$G$2:$G$6,0)

Then I created a logic that checked all the conditions based on your data below.

=IF(AND(F6="YES",H6=MIN($H$2:$H$6)),$K$3,IF(AND(F6="YES",H6=MIN($H$2:$H$6)+1),$K$4,IF(AND(F6="YES",H6<>1,H6<>2),$K$5,"NO Discount")))

They cannot be a teaching family, and must be full time. If that is True then return the value in Column .

Column K3:K6.
is just data table that hold your discounts. This could be a hidden table somewhere on the sheet. You could include it in your formula but I never hard code things like that incase you want to change the discount later, you don't have to change all the code.


So I got all this done and forgot that you had to Rank WITHIN each family. So this code wouldn't do it all but thats it the last peice you need. Maybe someone here can help finish it up.

You need a countif(s) MAX formula.

Download the file here.
http://gradax.com/uploader/images/Grade Rank.xlsx

 

[PCRIDE]

Also I just noticed I did the Ranking based on Age not Grade. So just type the following and it will fix the calculator.

In H2, type =J2 then copy the formula down.

Sorry I would update it but I am tired and ready for bed at this point. I hope this gets you started.

 

[phnee]

Thank you! I will take a look at it this week (see my profile for why I can't look immediately, haha!) and let you know the results. From my initial glance, it looks very do-able.

I have to use a formula for the non-Teaching Family, but I really hope that your solution will teach me how to figure it out myself.

 

[PCRIDE]

Glad to hear! Let us know what the outcome is.