Hi, everyone. I found a problem in Excel. Can any better method be used to calculating two GPA (E2 and E7 in the following)?
Example:
Table A:
Table B:
Now, I am using following formulas in Table A:
E2=3.57=
E7=2.33=
Have any better method (reduce VLOOKUP) can be used? Thank you!
Example:
Table A:
Year Subject Title Credits Grade GPA 1 Subject1 1 A+ 3.57 Subject2 2 A Subject3 3 A- Subject4 3 B+ Subject5 2 B 2 Subject6 3 B- 2.33 Subject7 3 C+ Subject8 3 C
<tbody>
</tbody>
Table B:
GPA A+ 4.3 A 4 A- 3.7 B+ 3.3 B 3 B- 2.7 C+ 2.3 C 2 C- 1.7 D+ 1.3 D 1 D- 0.7 F 0
<tbody>
</tbody>
Now, I am using following formulas in Table A:
E2=3.57=
Code:
=ROUND((C2*VLOOKUP(D2,G3:H15,2,FALSE)+C3*VLOOKUP(D3,G3:H15,2,FALSE)+C4*VLOOKUP(D4,G3:H15,2,FALSE)+C5*VLOOKUP(D5,G3:H15,2,FALSE)+C6*VLOOKUP(D6,G3:H15,2,FALSE))/SUM(C2:C6),2)
Code:
=ROUND((C7*VLOOKUP(D7,G3:H15,2,FALSE)+C8*VLOOKUP(D8,G3:H15,2,FALSE)+C9*VLOOKUP(D9,G3:H15,2,FALSE))/SUM(C7:C9),2)