Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    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. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Clever Sorting Help (2003 sp2)

    Like your soln much better.

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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