# Thread: Need Clever Sorting Help (2003 sp2)

1. ## Need Clever Sorting Help (2003 sp2)

I was given this problem by a colleague who needs to solve it for grading a large class. A sample file is attached.

Grades on tests are recorded by raw score. Each test a different possible point total. The class grading is by percentage out of the total (dividing the total of raw scores by the total of possible points).

The problem is that the low grade is to be dropped, where "low" is judged by percentage not raw score.

Converting raw scores into percentages is no problem (although I didn't do this in the sample file to keep it simple).

Finding the low percentage score is not problem (although again, I didn't do this in the sample file to keep it simple).

What is a problem is that having identified the grade to be dropped, I have to subtract out the corresponding raw score from a raw score total column (not a problem), but I also have to subtract out the possible points for that test from the column that sums total possible points.

I would prefer a solution using cell formulas (macros are over the head of the person I'll be passing this back to).

P.S. Once a general solution for this is established, I also need a way to identify the lowest percentage score coming from the highest possible point total - as shown in the attached file, it is advantageous to the student to drop this one.

2. ## Re: Need Clever Sorting Help (2003 sp2)

Does the attached do what you want? I calculated the effect of dropping each of the tests for each student, and then selected the highest score for each student.

3. ## Re: Need Clever Sorting Help (2003 sp2)

Not the most elegant solution, but here's something. The calculation "steps" begin on row 11; hope you can follow. When I had to do something very similar I put each of the steps in a different tab (and later hid the tabs to avoid confusing the user), and had a formula pull the result from the last step back to Sheet1.

In a nutshell...
1 - Calc each student score percent
2 - Rank percents (1 being the lowest score)
3 - Adjust the rankings to force unique ranking for every score (will always make higher point total have the lower rank; eliminates possibility of a tie)
4 - Rank the adjusted rankings to determine the FINAL ranking
5 - Pull only those student scores for student tests where rank is > 1
6 - Pull only those test totals for student tests where rank is >1

Then calculates the student overall percent using only those scores from Step 5 and the totals from Step 6

4. ## Re: Need Clever Sorting Help (2003 sp2)

Yes, I think it does.

Clearly I was thinking of the problem in a "forward looking way" (how do I get where I need to be), when there was an easier "backward looking way" (how do I get from my answer back to the starting point) available.

5. ## Re: Need Clever Sorting Help (2003 sp2)

Like your soln much better.

6. ## Re: Need Clever Sorting Help (2003 sp2)

Hans,

Dang! I used the same approach (and got the same result) but got stopped when I tried to figure out how to use Conditional Formatting to highlight the test to be dropped. Do you have magic up your sleeve for that? <g>

Chuck

7. ## Re: Need Clever Sorting Help (2003 sp2)

In the sample workbook, select B24.
Clear the current fill color.
Select Format | Conditional Formatting...
Select 'Formula Is' from the first dropdown list.
Enter the following formula in the box next to it. Note the use of absolute and relative references.
<code>
=(F2=\$I2)
</code>

The formula is for cell B2, it will automatically be adjusted for the other cells.
Click Format... to specify the formatting you want.
Click OK twice.

8. ## Re: Need Clever Sorting Help (2003 sp2)

Hans,

What can I say that has not already been said about the help you so freely share....
Thanks! (I know, that HAS been said before)

Chuck

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•