#### Lasso88

##### New Member
Have a question if someone would be nice to help me:

I am a teacher.

To get the grade A on a course (a course that has for example 5 criterias to live up to), the course's ALL 5 criterias have to be full filled up to level A. Only then is the final grade A.

In a course that have 5 criterias to live up to I have a student who has lived up to let's say A, C, A, C and C on the 5 criterias.

I want excel to calculate the final grade.

The grading system works like this: to get an A in a course, ALL 5 criterias must have reached A.
To get the grade B however, a MAJORITY of the criterias of A must have been reached (in this case 3 A's)
The student must then of course also have full filled all E (5) and all C (5 of them as well).

So if I in the first column in a spread sheet on the first row have the first criteria, second column I have the 2nd criteria (and so forth) and in row 2 puts A (for grade A) under first criteria, C (the grade C) under the 2nd criteria and so forth –*how do I then in the end column get excel to calculate how many A's the student have reached? And how many C's and E's?

I would also like excel to give me a nice diagram that shows graphically (with bars or something like that) that for example this student (in the example above) has reached the final grade C (which is the total in this case as the student have met all criterias of E, all criterias of C but not a majority of the A criterias (which if he had would have resulted in the final grade B.

Got this code from someone:
CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD

ElseIf CountA > GradeCount / 2 Then
ElseIf CountB > GradeCount / 2 Then
ElseIf CountC > GradeCount / 2 Then
Else
End If
End Function

There are still some things I need to get right;

1) If a student has even only one "F", the final grade has to be F. No matter if there are A's, E's or C's among what the student full filled.

2) If the student (taking a coarse that has 5 criterias to fullfill) has for example 3 A's and 2 E's it means that he or she has not reached C on two criterias. To get final grade "B", ALL C's must have been full filled.
So in this case the final grade has to be E. That is because to get "B" as final grade all C's must be full filled and a majority of A's. So, to be clearer: no matter how many A's you've got, if you have one E you have to lift that up (by studying more!) to a C to be able to get the B as a final grade.

Is it possible to do calculations like that?

Got this from someone:

CountB = WorksheetFunction.CountIf(GradeRange, "B") + CountA
CountC = WorksheetFunction.CountIf(GradeRange, "C") + CountB
CountD = WorksheetFunction.CountIf(GradeRange, "D") + CountC
CountE = WorksheetFunction.CountIf(GradeRange, "E") + CountD

End If

End If

End If

End If

If CountF > 0 Then
ElseIf CountA > GradeCount / 2 Then
ElseIf CountB > GradeCount / 2 Then
ElseIf CountC > GradeCount / 2 Then
Else
End If

End Function

But something is wrong with the code, if I enter F anywhere it says "Value fault" (or something like that, its in swedish). Should result in F.

And 3 A's and 2 C's results in C (not B as it should).

Can anyone help me with this?
Here is the file with the last code above:
https://www.dropbox.com/s/6i494lrs4mf5smg/TEST1A.xlsm

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Asala42

##### Well-known Member
Does it need to be vba or can it be a formula?

Try this one out:
=IF(COUNTIF(A1:E1,"F")>0,"F",
IF(COUNTIF(A1:E1,"A")=COUNTA(A1:E1),"A",
IF(AND(ROUND(AVERAGE(102-CODE(A1:E1)),0)>=4,COUNTIF(A1:E1,"A")>2),"B",
CHOOSE(ROUND(AVERAGE(102-CODE(A1:E1)),0),"E","D","C","C"))))

(Ctrl+shift+entered).

#### Lasso88

##### New Member
Thanks!
Dont know the difference btw vba and formula, where do I put the formula?

#### Lasso88

##### New Member
Tried to put the formula in the cell after cells containing grades (A C C C A). That didn't work...
What do you mean by (Ctrl+shift+entered) ?

#### Asala42

##### Well-known Member
Instead of entering the formula in a cell and then hitting "Enter", hit the key combination "Control+Shift+Enter". It's an array formula, rather than a standard type.

#### Asala42

##### Well-known Member
It's all a single formula.

In F1, paste in the following formula:
All in one cell - it's just a fairly long formula.

=IF(COUNTIF(A1:E1,"F")>0,"F",IF(COUNTIF(A1:E1,"A")=COUNTA(A1:E1),"A",IF(AND(ROUND(AVERAGE(102-CODE(A1:E1)),0)>=4,COUNTIF(A1:E1,"A")>
2),"B",CHOOSE(ROUND(AVERAGE(102-CODE(A1:E1)),0),"E","D","C","C"))))

However instead of just hitting "Enter", like you would to complete most formulas, hold down the "Control" and "Shift" keys while hitting "Enter". If you do it right this will add {} brackets outside the formula in your formula bar, and the formula should display the right grade.

#### Dave Patton

##### Well-known Member
Excel Workbook
ABCDEFGHIJK
1NameCourse 1Course 2Course 3Course 4Course 5Grade0F0
2Name 1ACCAAB15C15
3Name 2AAAAAA20B20
4Name 3AAAACB25A25
5Name 4ABCABB
6Name 5AAAAF
1a
Excel 2003
Cell Formulas
RangeFormula
K1=350-CODE(J1)*5
K2=350-CODE(J2)*5
K3=350-CODE(J3)*5
K4=350-CODE(J4)*5
G3=IF(COUNTA(B3:F3)<5,"F",LOOKUP(SUMPRODUCT(70-CODE(UPPER(B3:F3))),{0,"F";15,"C";20,"B";25,"A"}))
G4=IF(COUNTA(B4:F4)<5,"F",LOOKUP(SUMPRODUCT(70-CODE(UPPER(B4:F4))),\$I\$1:\$J\$4))
G5=IF(COUNTA(B5:F5)<5,"F",LOOKUP(SUMPRODUCT(70-CODE(UPPER(B5:F5))),\$I\$1:\$J\$4))
G6=IF(COUNTA(B6:F6)<5,"F",LOOKUP(SUMPRODUCT(70-CODE(UPPER(B6:F6))),\$I\$1:\$J\$4))
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

You can select the approach that you prefer; I just showed alternatives for your consideration.

You can modify the formulas and the Lookup table to address your requirements.
Try Excel's Formula Evaluate to see how the formula works.

Name 1 is the suggestion that you received from Asala42 with an edit considering if all cell are blank.

Name 2 is a suggestion with a built in lookup array.

Name 3 and Name 4 use a Lookup table see the I1:J4.
You can modify the arithmetic to yield the Grades that you require.

If you evaluate example Name 4, the grades net to 21 and the Lookup selects the Grade B.

Last edited:

#### Lasso88

##### New Member
Wow thanks alot, however I cant get things in place and executa that array thing on my mac (wont work). Could you post a worksheet instead?

Replies
3
Views
125
Replies
22
Views
354
Replies
1
Views
164
Replies
6
Views
202
Replies
8
Views
484

### Forum statistics

1,195,640
Messages
6,010,871
Members
441,571
Latest member
stolenweasel ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back