IF AND statement

[juandevry]

Need help with IF(AND statement... ..Calculate the grades for the above data and create the “IF(AND(“ statements for the derivation of a letter grade. (Abs = missed classes, each missed class costs 2 points)
Thank you ...


Qz5 (10/5%) 1st column Lab Rep. (120/20%) 2nd column Nut An (100/10%) 3rd column Test 1 (100/25%)4th column Test 2 (120/30%)5th column Abs (20/10%) 6th column
9 62 40 70 82 3
9 80 50 79 96 1
7 82 70 70 82 7
8 98 80 74 92 1
9 105 92 75 107 0
9 110 90 80 105 2
5 68 50 58 72 8
4 70 68 69 89 4
9 90 85 86 102 1

[Mikeopolo]

With data starting in Column A-F, row 4:

in G4 paste: =(A4/10*0.05+B4/120*0.2+C4/100*0.1+D4/100*0.25+E4/120*0.3-F4*2/100)

(I'm interpreting the last column as 2 percentage points off for each absence)

in H4 paste: =VLOOKUP(G4*100,GRADES,2)

Where Grades is the name of a table of marks and grades, eg:

10 I
20 H
..
90 A

or whatever you want.

Copy the formulae down to all rows.

Regards