automatically reducing grades of students who are not within a certain range.
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.
I did not see a spreadsheet,
I did not see a spreadsheet, so I do not know what columns and rows you are using.
The way I would handle this is as follows
First, sort the students and grades by highest grade to lowest grade.
Then have a count total to show total number of students. For this example assume this number is in cell A1
In cell A2 type in 30%
Select a blank cell and name it "Number of A's" In the cell next to it input the formula =A1*A2 (lets call this cell B3
Lets assume cell D1 is 1, cell D2 is 2, etc to the last student
Assume cell E1 is the students name and cell F1 is the grade
Then in a another column, lets say G1 type in this formula:
=IF(and(D1<=$B$1,f1>89),"A",if (and(D1>$B$1,f1>89),"B",""))
I am assuming a grade of 90 and above is an A.
Not sure how you want to address duplicate grades, that may result in more that 30% get A's.
So in your example of 20 students, only 6 get A's. What happens in student 6 and 7 (or more) have the same grade???