# Excel 2011 - Formula Help Needed

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

1. ### nope7308 macrumors 65816

Joined:
Oct 6, 2008
Location:
#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. Sep 15, 2011
Last edited: Sep 15, 2011

### ergdegdeg Moderator emeritus

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 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:
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

Joined:
Oct 6, 2008
Location:
#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

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(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.

Joined:
Oct 6, 2008
Location: