Excel Formula to calculate GPA

[wngadea]

This is what my row looks like I want to add a column at the end that calculate GPA (Letter Value/Number of classes)..
Could someone please help me?? I try the "IF" formula but is not working

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl58 { text-align: center; }</style>

Student ID	Last Name	First Name	Hist %	Mark	Prblm Solv %	Mark	PE %	Mark	Writing %	Mark	Reading %	Mark
0000000	None	None	69.6	NY	65.7	NY	95	A	59.8	NY	79.7	C+
0000000	None	None	62.2	NY	65.7	NY	86.1	B	79.6	C+	59.3	NY
0000000	None	None	61	NY	75.4	C	89	B+	66.7	NY	66.9	NY
0000000	None	None	82.3	B-	79.6	C+	84.3	B	67.5	NY	85.1	B
0000000	None	None	71.3	C-	75.4	C	89	B+	66.7	NY	66.9	NY
0000000	None	None	67.8	NY	65.4	NY	83.3	B	71.5	C-	71.8	C-
0000000	None	None	78	C+	76.3	C	79.9	C+	85.2	B	75.5	C
0000000	None	None	75.5	C	69.9	NY	92.7	A-	82.3	B-	79.9	C+

<colgroup><col style="width:65pt" span="13" width="65"> </colgroup><tbody>
</tbody>

A =4
A- =3.7
B+ =3.3
B =3
B- =2.7
C+ =2.3
C =2
C- =1.7
NY =0

 

[djrod1rep]

You can use the formula to convert the letter grade to your grade scale in the appropriate column:

=IFERROR(CHOOSE(MATCH(M2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0)

 

[djrod1rep]

In your new column at the end, you can use this formula:

=SUM(IFERROR(CHOOSE(MATCH(E2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0),IFERROR(CHOOSE(MATCH(G2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0),IFERROR(CHOOSE(MATCH(I2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0),IFERROR(CHOOSE(MATCH(K2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0),IFERROR(CHOOSE(MATCH(M2,{"A","A-","B+","B","B-","C+","C","C-"},0),4,3.7,3.3,3,2.7,2.3,2,1.7),0))/(5-COUNTIF(E2:G2:I2:K2:M2,"ny"))

Using your chart above, this will use 5 columns: E, G, I, K, M. It will calculate the GPA based on valid Letter Grades.

 

[L. Howard]

Would the GPA's for the eight students be thus:

NY
NY
NY
C
NY
NY
C+
C

Per your example?

Howard