Generating groups based on #of names and performance

**lOYvEpi6M87nEoIF0ul8** · 09-04-2017, 08:02 AM

Hello, I have a problem that seems far too complex for me to solve on my own v.v so help would be amazing. I am new to excel.

A sheet has the following column headers:

Student Score Rank Scores: High to Low Group

The student score is a percentage, the rank is calculated using the formula:

Formula:

Please Login or Register  to view this content.

The values in the Scores: High to Low column are calculated using the following formula:

Formula:

Please Login or Register  to view this content.

There can be a maximum of 35 student names. What I would like to do is automatically generate groups based on student performance. Ideally there would always be 5 groups of students and any remainders would be placed into the group which contains the lowest performing students. So, if for example 26 student names are entered into the student names column, the group column might show “group 1” for the top 5 scoring students and “Group 2” for the next top 5 and so on. The last group of lowest performing students would contain 6. Attached is a sample spreadsheet.

**oeldere** · 09-04-2017, 02:23 PM

c2 =IFERROR(RANK(B2,$B$2:$B$36)+row()/1000000,"")

e2=IFERROR(INDEX(Tabel1[[Student]:[Rank]],MATCH((SMALL($C$2:$C$36,ROW()-1)),[Rank],0),1),"")

f2=IF(INT((ROW()+3)/5)>INT(COUNTA([Student])/5),INT(COUNTA([Student])/5),INT((ROW()+3)/5))

See the attached file.

**lOYvEpi6M87nEoIF0ul8** · 09-04-2017, 02:50 PM

Omg thank you so much! Amazing

Now to spend the rest of my life trying to figure out how this works.

**oeldere** · 09-04-2017, 03:06 PM

Thanks for the rep.

You're welcome. We appreciate the feedback!

Thanks for marking the question solved.

Please Login or Register  to view this content.

the red part makes sure you get all unique values.

INT(COUNTA([Student])/5) = this counts the available values in your range student (which is 26).

You want to group in 5 groups, so we divide by 5.

The INT makes sure it rounds to a value of 5.

F2 = if the value is > 5, then also add that student to the last group, otherwise determine the group.

Hope I exclained well enough.

**lOYvEpi6M87nEoIF0ul8** · 09-04-2017, 03:51 PM

You are welcome.

Yes it is explained well enough for me to understand, thanks again! :D

Generating groups based on #of names and performance

LinkBack

Thread Tools

Rate This Thread

Display

Generating groups based on #of names and performance

Re: Generating groups based on #of names and performance

Re: Generating groups based on #of names and performance

Re: Generating groups based on #of names and performance

Re: Generating groups based on #of names and performance

Thread Information

Users Browsing this Thread

Similar Threads

Generating Unique Groups of 4

How do combine different names under one header & use sumif to see their performance.

Linking names to specific performance across sheets - even if names aren't always present

[SOLVED] generating automated names to numbers

Generating Individual Save Names

Impact of define names on performance

Generating graphs from multiple groups of data by vba

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions