Calculating which students make honor roll

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

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

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).

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

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):

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

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.