Excel 2011 - Formula Help Needed

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

  1. nope7308 macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #1
    This should be a simple conditional function, but I can't figure it out...

    I'm creating a grade sheet and I would like excel to automatically calculate grade distribution as I'm inputing the grades. For instance, let's assume I have a list of 20 grades in the D column for the first assignment. As I input those grades, I want excel to tell me how many of them are in the A-range, B-range, etc. I want this done for each assignment, so I would need a corresponding calculation for the E column (2nd assignment).

    Can anyone help me out?
     
  2. ergdegdeg, Sep 15, 2011
    Last edited: Sep 15, 2011

    ergdegdeg Moderator emeritus

    ergdegdeg

    Joined:
    Oct 13, 2007
    #2
    This should give you all you need if the cells already contain letter grades:
    Code:
    ="A: "&COUNTIF(D2:D11,"A")&", B: "&COUNTIF(D2:D11,"B")&", C: "&COUNTIF(D2:D11,"C")&", D: "&COUNTIF(D2:D11,"D")&", F: "&COUNTIF(D2:D11,"F")
    It will show the result like this: A: 3, B: 2, C: 4, D: 1, F: 0

    You'll probably need to change the cell ranges (from D2:D11 to whatever). You should also be able to drag that forumula to the next cell over and it will count the grades for you.

    If you have the grades as points, the following should work:
    Code:
    ="A: "&COUNTIF(D2:D11,">=90")&", B: "&COUNTIFS(D2:D11,">=80", D2:D11,"<90")&", C: "&COUNTIFS(D2:D11,">=70",D2:D11,"<80")&", D: "&COUNTIFS(D2:D11,">=60",D2:D11,"<70")&", F: "&COUNTIF(D2:D11,"<60")
    The results will be something like this: A: 3, B: 3, C: 0, D: 2, F: 2

    You may need to adjust the cutoffs for each grade.
     
  3. nope7308 thread starter macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #3
    Thanks for the response, but things are slightly more complicated...

    The individual grades will be entered as a percentage. So, I need to calculate the overall distribution by specifying that "A" falls within the range of 90-100%. Then, I would like to see how many As, Bs, etc. were awarded, but have this represented as a percentage.

    It's difficult to explain, so here is an example:

    Let's assume that we have ten students in the class, and each student submitted the first assignment (10 total). Of those submissions, two received 90%, while the remaining eight received 75%. I need a formula that would show me the following:
    A = 20%
    B = 80%
    C = 0%
    D = 0%
    F = 0%
     
  4. ergdegdeg Moderator emeritus

    ergdegdeg

    Joined:
    Oct 13, 2007
    #4
    You'll probably want 5 different formulas, then. One for each grade in a different cell?

    Here's the one for A: ="A = " & (COUNTIF(D2:D11,">=0.9")/COUNT(D2:D11)) * 100 &"%"
    And the one for B: ="B = " & (COUNTIFS(D2:D11,">=0.8",D2:D11,"<0.9")/COUNT(D2:D11)) * 100 &"%"

    The rest of the grades should be copy/paste while adjusting the cutoffs.
     
  5. nope7308 thread starter macrumors 65816

    nope7308

    Joined:
    Oct 6, 2008
    Location:
    Ontario, Canada
    #5
    Awesome! Thank you so much - I finally figured it out. The only correction to the above would be to limit the As to under 100, similar to how B is limited to under 90.

    Thanks again!
     
  6. cgc macrumors 6502a

    Joined:
    May 30, 2003
    Location:
    Utah

Share This Page