CODEWITHSUNDEEP
excelexcel-formula
Yash
Yash

Reputation: 1018

Distributing students across classes based on marks

enter image description here

Name Marks Rank Class
Eddie 20 6 C
Tom 10 10 A
Jenny 30 4 A
Riva 40 3 C
Andy 50 2 B
Mark 5 11 B
Sally 78 1 A
Jack 15 8 B
Dick 15 8 C
Harry 20 6 A
Dom 30 4 B

The students are expected to be distributed across classes A, B and C, based on their marks in the above picture. The student with the highest marks goes in A. The one with the next highest goes in B. The next highest goes in C. The next goes again to A and so on. What should be the formula to be used in Excel 2013 and above for calculating the Class?

Upvotes: 1

Views: 190

Answers (3)

Terry W
Terry W

Reputation: 3257

If you are using Excel 365, you can use the SORTBY function to solve the question.

Assume the Name column is in a named range called List_Name, the Marks column is in a named range called List_Marks, your example dataset is in range A1:D12, and you want to return the class code in column D.

In cell D2, enter any one of the following formulas and drag it down:

=CHOOSE(MOD(MATCH(A2,SORTBY(List_Name,List_Marks,-1),0),3)+1,"C","A","B")

Alternatively, you can use the following in cell D2 instead:

=INDEX({"C";"A";"B"},MOD(MATCH(A2,SORTBY(List_Name,List_Marks,-1),0),3)+1)

Solutions

If you cannot use the SORTBY function, then the answer provided by Ron Rosenfeld should do the job quite well.

Let me know if you have any questions.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

  • Sort the table by either Marks descending or Rank ascending.
  • D2: =CHOOSE(MOD(ROWS($1:1)-1,3)+1,"A","B","C")

enter image description here

Upvotes: 2

Doragon
Doragon

Reputation: 367

Assuming that the chart you provided is in cells A1-D11

Try making a 2x3 chart on the side (I’m using F2-G4 with 1...A 2...B 0...C

and then put the formula in D1 as follows: =vlookup(mod(C2,3),F2:G4, false)

You could even skip out the whole C column if you wanted, writing =vlookup(mod(rank(A2,B:B),3),F2:G4, false)

But then you might have an issue of 2 people going to the same class if they rank the same.

Upvotes: 1

Related Questions