Help with Excel Formulas

Discussion in 'Mac Apps and Mac App Store' started by nope7308, Sep 17, 2011.

  1. nope7308, Sep 17, 2011
    Last edited: Sep 17, 2011

    nope7308 macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #1
    I'm trying to create a grade spreadsheet, but I'm having difficulty with one small section...

    Students will be writing 6 essays and a total of 10 quizzes. I want excel to automatically drop the two lowest quiz scores, having the remaining 8 quizzes count for 25% of the final grade.

    I originally created the spreadsheet with only one column for quizzes, which made calculating the average pretty straightforward. When trying to drop the two lowest scores, however, I have no idea how to make it work.

    If the 10 quiz scores are in cells J2-R2, what formula would you use to drop the two lowest scores and have the remaining 8 scores weighted at 25% total?

    Any help would be greatly appreciated!

    Edit:
    Here is the formula I used to calculated the average when I only had one column for the quizzes. As you can see, the assignments are not weighted evenly.
    =(D2*0.05)+(E2*0.1)+(F2*0.1)+(G2*0.15)+(H2*0.15)+(I2*0.2)+(J2*0.25)
     
  2. firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #2
    It's a shame you want to drop the two lowest scores. If you only wanted to drop one, the formula would be trivial... you'd start by summing, and then subtract the MIN() value in the list.

    As it is, you may well find that writing a macro function to iterate through the list and find the two smallest values is the easiest way.
     
  3. nope7308 thread starter macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #3
  4. firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #4
    I'd not heard of the 'small' function before... that does what you want.

    I'm wary of helping you too much to be honest. A person using a spreadsheet where they don't understand how it works is dangerous IMO... they often don't notice small problems.

    Question for you... do you want to eliminate the two lowest scores, or the lowest scores after weighting?

    I think the best spreadsheet practice (especially if you're not an expert) is to take lots of space, and work things out in a lot of smaller steps. In excel you can always collapse columns to hide working.

    So, have your grid of scores.

    On the right of it, have an equal sized grid of single weighted scores. It would be good to take the weightings out of the formulas and put the values on the top line of the grid, referring to them.

    Next column, sum the weighted scores.

    Next column, find the minimum score in the weighted array, using MIN or SMALL(range, 1).

    Next column, find the 2nd smallest score in the weighted array, using SMALL(range, 2)

    Final column, subtract the two minimum scores from the sum of weighted scores.


    Laying out like the above means you can check your working every step of the way. Just hide the weighted sum and semi-total columns to tidy up when everything is working.
     
  5. nope7308 thread starter macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #5
    Thank you for the concern, but I would really prefer that someone provide me with the exact formula. I can easily do the calculations manually to verify accuracy, but that would defeat the purpose of using the formula in the first place.

    For clarity, let me explain exactly what I need and the precise format of the spreadsheet...

    Column A - C will contain identifying information, and columns D - I will be reserved for the 6 assignments (each weighted differently). Columns J - R will be reserved for the 10 quizzes. Of those 10 quizzes, I want to drop the two lowest scores. The remaining 8 quizzes will account for 25% of the final grade. The final grade will be the last column

    Now, can someone please write the specific formula I need? I can easily verify the accuracy of the formula by awarding 100% in 8 of the quizzes and 50% in the other two. If the final grade is 25%, then the formula works.
     
  6. MisterMe macrumors G4

    MisterMe

    Joined:
    Jul 17, 2002
    Location:
    USA
    #6
    You have amped-up firestarter's concern to full-fledged dread on my part. A spreadsheet is a powerful and dangerous application when you know what you are doing. When you are not sure, you really ought to look for alternatives. There are dedicated gradebook applications that will allow you to drop as many of the lowest grades that you like. You are guaranteed with the dedicated programs that all students' grades are calculated using the same criteria.
     
  7. CylonGlitch macrumors 68030

    CylonGlitch

    Joined:
    Jul 7, 2009
    Location:
    SoCal
    #7
    If the 10 quizzes are in the range d1:d10 then the formula you want is this :
    =(sum(d1:d10)-small(d1:d10,1)-small(d1:d10,2))/8

    What you do with it, is up to you. Make sure things are done right though.
     
  8. saberahul macrumors 68040

    Joined:
    Nov 6, 2008
    Location:
    USA
    #8
    Assuming you have n quizzes, why not just sort and replace the column as n-2.
     
  9. nope7308 thread starter macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #9
    Thank you SO much! As it turns out, I almost had the exact formula. Not to fear, I will keep an eye on the calculations and be certain that nothing goes awry.

    Thanks again!
     
  10. macmick macrumors newbie

    Joined:
    Jul 2, 2003
    Location:
    Melbourne, Australia
    #10

    Another way would be- AVERAGE(LARGE(A1:A10,{1,2,3,4,5,6,7,8}))
     

Share This Page