Return a text value into different cells in a column based on specific criteria

Hello,

you could create different pivot tables to report on students with specific grade averages. You can filter each pivot table to show only students with a specific average range.

Your workbook is a bit too overwhelming (too much information). It's not quite clear where the source is. 

In general, you should avoid the practice of entering empty columns to separate chunks of your data. Enter data into contiguous tables, then it's easy to report on the data. 

With regards to your second question: yes, this can be done, but it will probably require VBA. It would be easier to give better advice if you could be a bit more specific. Provide a small (!) data sample, mock up the desired result and explain what logic leads to the result. Since this is a second question in the same thread, you may want to start a new question for that specific issue, so each question can be marked as solved separately.