dealing with student grades

I am doing a spreadsheet for student grades. There is a limit (30% of no of students) on how many students can get A or A+.

More students get A's based on their test scores than is allowed, so I want to know if there is a formula (to put in Column D2 below) to automatically reduce the A scores of the students over the 30% limit down to B+

Example

There are 20 students in a class but only 6 can get A and above.

However 8 get A scores on their tests.

So I need a formula to reduce the two lowest A scores outside of the 30% range to B+.

Originally Posted by Ace_XL

Please refer attched sample worksheet. The data is sorted based on scores. Refer 'Column D' for the correct grades

Thanks for your reply. IT works with the A-B reduction, but I don't want lower scores being raised, just the A/A+ scores over the 30% being reduced to B+.
How could that be reflected in the formula?

Originally Posted by vlady

can you try to change the * in "A*" with "A+" if this works.
IF(COUNTIF($C$3:$C3,"A+")>C

Maybe I should have sent the file at the beginning to make it obvious.

The formula in question is in the 'running totals' worksheet column M6-25

I want the lowest A+/A grades over the 30% maximum number of students reduced to a B+ but not any of the other scores below an A.

This doesn't seem to work.

Please see attachment.

This formula worked perfectly with my decending ranked scores. Thanks a lot...

Now, how can I get these 'changed 'scores to copy or go into a column that is not ranked?