Excel 2011 - Formula Help Needed

[nope7308]

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?

 

[ergdegdeg]

This should give you all you need if the cells already contain letter grades:

="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 D211 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:

="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.

 

[nope7308]

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%

 

[ergdegdeg]

You'll probably want 5 different formulas, then. One for each grade in a different cell?

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

The rest of the grades should be copy/paste while adjusting the cutoffs.

 

[nope7308]

You'll probably want 5 different formulas, then. One for each grade in a different cell?

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

The rest of the grades should be copy/paste while adjusting the cutoffs.

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!

 

[cgc]

Would a histogram be helpful?