Calculating which students make honor roll

**leahf** · 12-19-2015, 08:21 PM

Hi,

I work at a public school and I'm hoping one of you knows if there's a simple way to extract from a data set which students made the honor roll. Right now teachers have to visually scan the printed report cards and report the names of the students who made honor roll to the main office.

Is there an excel function (I'm thinking a pivot table report or a nested if function, maybe?) that can help?

Let me describe the data set:

There will be a column with student ID #s, a column with the course name, and a column with the final grade. There will be 7 or so rows for each student.

Honor roll is defined as getting 90 or higher in every class. High honors is defined as getting 95 or higher in every class.

Can I somehow extrapolate ONLY those student numbers for students that made the honor roll and high honors?

I guess it would be something like, if all the values in column A (student ID #) are the same, and all the values in column C corresponding to those same rows (final grade) are greater than or equal to 90, return those student numbers?

Thanks a lot for any guidance you may be able to offer.

Leah

http://www.mrexcel.com/forum/excel-q...onor-roll.html

**shg** · 12-19-2015, 08:40 PM

Cross-posted at http://www.mrexcel.com/forum/excel-q...ml#post4375006

**leahf** · 12-19-2015, 08:53 PM

My humble apologies; I didn't realize the sites were related. Thank you shg

**FDibbins** · 12-19-2015, 08:56 PM

Originally Posted by leahf

My humble apologies; I didn't realize the sites were related. Thank you shg

They are not related, but many members here are also members there

**FDibbins** · 12-19-2015, 08:55 PM

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**JeteMc** · 12-19-2015, 10:07 PM

This solution works for ver. 2010 and I believe that it will work for you on Mac. First populate the column that has student ID's by 1) Selecting the column; 2) Under "Find & Select" choose "Go to special" and "Blanks" 3) Use the key strokes =; up arrow and Enter. At this point all of the rows in the ID column should have a number. I will now add five "helper" columns. The first will be for the lowest grade made by that student and employ the array formula (activated by Ctrl+Shift+Enter):

=IF(A2<>A1,SMALL(IFERROR((C2:C20)/(A2:A20=A2),1000),1),"")

The second will be for the student numbers of those students that have between 90 and 94 as a lowest grade using the formula:

=IF(AND(E2>=90,E2<95),A2,"")

The third will be for the student numbers of those students that have a 95 and above as a lowest grade using the formula:

=IF(AND(E2<>"",E2>=95),A2,"")

The fourth and fifth will filter the second and third so that the ID numbers will appear in the top rows of those columns using aggregate formulas. Here is a example file showing the application of those methods and formulas:
Honor Roll.xlsx
Let me know if you have any questions, if my example doesn't match your data layout please upload a sample spreadsheet.

**leahf** · 12-21-2015, 02:41 PM

Hi JeteMC!

This worked BEAUTIFULLY for my application!! Thank you SO much.

-L

**dflak** · 12-20-2015, 05:52 PM

Here is my entry into the contest.

I converted the range into a table because the formulas are easier to read.

This wiki has information on Excel tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

It uses SUMPRODUCT to assign ranks based on score and courses signed up for. This wiki explains how SUMPRODUCT works: http://www.utteraccess.com/wiki/inde...Array_Formulas

Rank 1 is simple: =SUMPRODUCT(([Score]>[@Score])*([1st]=[@1st]))+1

What this formula says is find the rank based on the score and peoples’ first choices. We add one so the rankings start with one instead of zero.

Select 1 is: =[@[Rank 1]]<=VLOOKUP([@1st],Table_Courses,2,FALSE)

Basically this says if the rank is less than or equal to the number of seats, the person is in.

Assign just looks up the information requested.

Rank 2 is more complicated: =IF([@[Select 1]]=TRUE,99,SUMPRODUCT(([Score]>[@Score])*([2nd]=[@2nd])*([Select 1]<>TRUE))+1)

This formula has to account for people who were already selected in the first round. They are assigned a rank of 99 so they won’t be selected on this round.

Select 2 uses the same logic as Select 1.

Rank 3 gets more complicated: =IF(OR([@[Select 1]]=TRUE,[@[Select 2]]=TRUE),99,SUMPRODUCT(([Score]>[@Score])*([3rd]=[@3rd])*([Select 2]<>TRUE)*([Select 1]<>TRUE))+1)

It has to account for people who were selected in both the first and second round. This is as far as I took it. Obviously the following rounds will get more complicated.

**dflak** · 12-20-2015, 05:57 PM

Oops. Seems to be posted to the wrong question. Don't know how to delete a post. Relatively new and am still having problems navigating the site.

**JeteMc** · 12-21-2015, 04:29 PM

You're Welcome and thank you for the enthusiastic feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a good day.