JonXL
Well-known Member
- Joined
- Feb 5, 2018
- Messages
- 513
- Office Version
- 365
- 2016
- Platform
- Windows
Hello,
I've been looking to going back to school to get some additional certification and after downloading my college transcripts wanted to find my overall GPA (which is the grade-letter's numerical value weighted against the credit value for each course). I did this pretty easily with a helper column, but was interested to know if there's a way to do this with only one formula and no helper column. I tried using the following
I have two ranges in play - the first listing my courses, credits, and letter grade in each and the second listing each grade letter and it's GPA value as a lookup. The lookup range is called 'Grades', the course listing does not have a name.
Like I said, I can do this with a helper column and have already gotten my answer; my question here is just to explore how to do this without the helper column to help me learn something new.
The course listing:
The Grades range (A2:B15):
Appreciate any insights!
I've been looking to going back to school to get some additional certification and after downloading my college transcripts wanted to find my overall GPA (which is the grade-letter's numerical value weighted against the credit value for each course). I did this pretty easily with a helper column, but was interested to know if there's a way to do this with only one formula and no helper column. I tried using the following
SUMPRODUCT
formula, but it gave a #VALUE!
error and I'm not really good-enough with SUMPRODUCT
to troubleshoot what went wrong:
Code:
=SUMPRODUCT(Sheet1!C2:C46,VLOOKUP(Sheet1!F2:F46,Grades,2,FALSE))
I have two ranges in play - the first listing my courses, credits, and letter grade in each and the second listing each grade letter and it's GPA value as a lookup. The lookup range is called 'Grades', the course listing does not have a name.
Like I said, I can do this with a helper column and have already gotten my answer; my question here is just to explore how to do this without the helper column to help me learn something new.
The course listing:
Book1 | ||||||
---|---|---|---|---|---|---|
C | D | E | F | |||
1 | Earned Credit Hours | Non-Credit Hours | Grading Method | Grade | ||
2 | 1 | 0 | Letter Grade | B | ||
3 | 3 | 0 | Letter Grade | A | ||
4 | 3 | 0 | Letter Grade | A | ||
5 | 3 | 0 | Letter Grade | A | ||
6 | 4 | 0 | Letter Grade | A | ||
7 | 0 | 0 | Audit | AU | ||
8 | 3 | 0 | Letter Grade | A | ||
9 | 3 | 0 | Letter Grade | A | ||
10 | 3 | 0 | Letter Grade | A | ||
11 | 3 | 0 | Letter Grade | A | ||
12 | 4 | 0 | Letter Grade | A- | ||
13 | 3 | 0 | Letter Grade | A | ||
14 | 3 | 0 | Letter Grade | A | ||
15 | 3 | 0 | Letter Grade | A+ | ||
16 | 3 | 0 | Letter Grade | B | ||
17 | 3 | 0 | Letter Grade | A | ||
18 | 3 | 0 | Letter Grade | A | ||
19 | 1 | 0 | Letter Grade | A | ||
20 | 3 | 0 | Letter Grade | B+ | ||
21 | 3 | 0 | Letter Grade | B | ||
22 | 3 | 0 | Letter Grade | A | ||
23 | 3 | 0 | Letter Grade | A | ||
24 | 4 | 0 | Letter Grade | A | ||
25 | 3 | 0 | Letter Grade | B+ | ||
26 | 3 | 0 | Letter Grade | A- | ||
27 | 4 | 0 | Letter Grade | A | ||
28 | 2 | 0 | Letter Grade | A | ||
29 | 4 | 0 | Letter Grade | A | ||
30 | 3 | 0 | Letter Grade | A | ||
31 | 3 | 0 | Letter Grade | A | ||
32 | 3 | 0 | Letter Grade | A- | ||
33 | 3 | 0 | Letter Grade | A | ||
34 | 3 | 0 | Letter Grade | A | ||
35 | 3 | 0 | Letter Grade | A | ||
36 | 4 | 0 | Letter Grade | A | ||
37 | 4 | 0 | Letter Grade | A | ||
38 | 3 | 0 | Letter Grade | A | ||
39 | 4 | 0 | Letter Grade | A | ||
40 | 3 | 0 | Letter Grade | A | ||
41 | 3 | 0 | Letter Grade | A | ||
42 | 3 | 0 | Letter Grade | A | ||
43 | 3 | 0 | Letter Grade | A | ||
44 | 4 | 0 | Letter Grade | A | ||
45 | 3 | 0 | Letter Grade | A | ||
46 | 3 | 0 | Letter Grade | A | ||
Sheet1 |
The Grades range (A2:B15):
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Grade | GPA | ||
2 | A+ | 4.3 | ||
3 | A | 4 | ||
4 | A- | 3.6 | ||
5 | B+ | 3.3 | ||
6 | B | 3 | ||
7 | B- | 2.6 | ||
8 | C+ | 2.3 | ||
9 | C | 2 | ||
10 | C- | 1.6 | ||
11 | D+ | 1.3 | ||
12 | D | 1 | ||
13 | D- | 0.3 | ||
14 | F | 0 | ||
15 | AU | 0 | ||
Grade_GPA |
Appreciate any insights!