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> [TABLE="width: 845"]
<colgroup><col style="width:65pt" span="13" width="65"> </colgroup><tbody>[TR]
[TD="class: xl58, width: 65"]Student ID[/TD]
[TD="class: xl58, width: 65"]Last Name[/TD]
[TD="class: xl58, width: 65"]First Name[/TD]
[TD="class: xl58, width: 65"]Hist %[/TD]
[TD="class: xl58, width: 65"]Mark[/TD]
[TD="class: xl58, width: 65"]Prblm Solv %[/TD]
[TD="class: xl58, width: 65"]Mark[/TD]
[TD="class: xl58, width: 65"]PE %[/TD]
[TD="class: xl58, width: 65"]Mark[/TD]
[TD="class: xl58, width: 65"]Writing %[/TD]
[TD="class: xl58, width: 65"]Mark[/TD]
[TD="class: xl58, width: 65"]Reading %[/TD]
[TD="class: xl58, width: 65"]Mark[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]69.6[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]65.7[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]95[/TD]
[TD="class: xl58"]A[/TD]
[TD="class: xl58"]59.8[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]79.7[/TD]
[TD="class: xl58"]C+[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]62.2[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]65.7[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]86.1[/TD]
[TD="class: xl58"]B[/TD]
[TD="class: xl58"]79.6[/TD]
[TD="class: xl58"]C+[/TD]
[TD="class: xl58"]59.3[/TD]
[TD="class: xl58"]NY[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]61[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]75.4[/TD]
[TD="class: xl58"]C[/TD]
[TD="class: xl58"]89[/TD]
[TD="class: xl58"]B+[/TD]
[TD="class: xl58"]66.7[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]66.9[/TD]
[TD="class: xl58"]NY[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]82.3[/TD]
[TD="class: xl58"]B-[/TD]
[TD="class: xl58"]79.6[/TD]
[TD="class: xl58"]C+[/TD]
[TD="class: xl58"]84.3[/TD]
[TD="class: xl58"]B[/TD]
[TD="class: xl58"]67.5[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]85.1[/TD]
[TD="class: xl58"]B[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]71.3[/TD]
[TD="class: xl58"]C-[/TD]
[TD="class: xl58"]75.4[/TD]
[TD="class: xl58"]C[/TD]
[TD="class: xl58"]89[/TD]
[TD="class: xl58"]B+[/TD]
[TD="class: xl58"]66.7[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]66.9[/TD]
[TD="class: xl58"]NY[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]67.8[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]65.4[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]83.3[/TD]
[TD="class: xl58"]B[/TD]
[TD="class: xl58"]71.5[/TD]
[TD="class: xl58"]C-[/TD]
[TD="class: xl58"]71.8[/TD]
[TD="class: xl58"]C-[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]78[/TD]
[TD="class: xl58"]C+[/TD]
[TD="class: xl58"]76.3[/TD]
[TD="class: xl58"]C[/TD]
[TD="class: xl58"]79.9[/TD]
[TD="class: xl58"]C+[/TD]
[TD="class: xl58"]85.2[/TD]
[TD="class: xl58"]B[/TD]
[TD="class: xl58"]75.5[/TD]
[TD="class: xl58"]C[/TD]
[/TR]
[TR]
[TD="class: xl58"]0000000
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]None
[/TD]
[TD="class: xl58"]75.5[/TD]
[TD="class: xl58"]C[/TD]
[TD="class: xl58"]69.9[/TD]
[TD="class: xl58"]NY[/TD]
[TD="class: xl58"]92.7[/TD]
[TD="class: xl58"]A-[/TD]
[TD="class: xl58"]82.3[/TD]
[TD="class: xl58"]B-[/TD]
[TD="class: xl58"]79.9[/TD]
[TD="class: xl58"]C+[/TD]
[/TR]
</tbody>[/TABLE]

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