Scenario

30% of students can receive A+/A.

In a class of 20, 8 student get A's but only the top 6 scores can qualify.

The other 2 exceed the 30% rule.  How do I reduce the lowest A scores down to a B+ without affecting the other A scores or the other 12 students?

Someone already unsuccessfully tried this formula:

=IF(L6="a",IF(COUNTIF(\$L\$6:\$L6,"a*")>COUNTIF(\$L\$6:\$L\$25,"a*")*0.3,"b+",L6),IF(L6="a+",IF(COUNTIF(\$L\$6:\$L6,"a*")>COUNTIF(\$L\$6:\$L\$25,"a*")*0.3,"b+",L6)))

This is what the worksheet should look like with the correct formula

K                 L                M              N

 Student Score Grade Correct Grade (A's<30%) 1 10 F F 2 91 A B+ 3 92 A B+ 4 94 A A 5 74 C C 6 93 A A 7 35 F F 8 56 F F 9 45 F F 10 77 C+ C+ 11 85 B+ B+ 12 88 B+ B+ 13 84 B B 14 89 B+ B+ 15 99 A+ A+ 16 98 A+ A+ 17 97 A+ A+ 18 96 A+ A+ 19 90 A B+ 20 90 A B+

There is 1 comment. Login or register to view it.

|
• 413 12
• 1,732 19
• 2,545 40
• 107 5
• 126 6
• 80 2
• 28 1
• 127 5
• 236 2
• 24 1
• 53 1
• 104 4
• 114 3
• 113 5
• 239 1
• 228 4
• 418 8
• 76 4
• 352 6
• 237 3
• 1,070
• 567
• 378
• 284
• 230
• 182
• 167
• 153
• 147
• 145