Weighted Averages and vlookup help (grade sheet)

[burgeon]

I'm a teacher, and I'm trying to set up a simple sheet to track my grades. I have the basic framework set, but I need a little help with a couple of details - I hope someone can help.

To start, I set up a table with:
Column A: Grades A-F (Rows 1-12, with B+, B-, etc.)
Column B: Corresponding grade points (A=4, A-=3.7, A-/B+=3.5, B+=3.3, etc)

My students have three exams and a participation grade. The exams are essays, so they only receive a letter grade.

Next to each name, I have four lines, two colums each. I've put in a drop-down for a letter grade for each exam and participation, and entered a VLOOKUP to pull the correct grade point next to each grade.

So here's what I'm trying to do:

1. Create a formula calculating an average of all three grades, with the exams weighted 30% each and the participation weighted 10%. (I should mention here that I am NOT a math teacher!)
2. Once I have that weighted average, I need a formula that will select the grade from my vlookup columns.

Obviously, most of the averages won't be exact matches to my grade points, so I need my formula to select the highest grade for the average. Therefore, if the average is 2.9, my returned result will be "B-"(2.7), not "B"(3.0). I may adjust grades up and down, but I want to have a calculation of the hard numbers for my consideration.

I hope this isn't too much to ask, any help would be greatly appreciated.

 

[burgeon]

Okay, so I just solved the weighting issue the hard way, by multiplying each exam grade point by three, adding them together with the single participation grade, and dividing by ten, but does anyone know how to solve the issue with getting the formula to select the grade based on the GPA? Thanks...

 

[shemayisroel]

seems 'doable' would ut be possible to get some sample data to work with?

 

[burgeon]

I'm not able to post using an html table, but I think I can describe it like this:

.........A..........B

1........A.........4.0
2........A-........3.7
3......A-/B+.....3.5
4.......B+.........3.3
5.......B...........3.0
6.......B-..........2.7
7.......C+.........2.3
8.......C...........2.0
9.......C-..........1.7
10.....D+..........1.3
11......F...........0.0
12
13.....3.1
14.....3.5
15.....3.9
16.....1.1
17.....2.4

So, in this example are the GPA's I'm working with are in A13, A14, A15, A16, A17
In B13, B14, B15, B16, B17 I want ot have formulas that look up the letter grade based on the above chart. The results I'm looking for would be:
B13= B
B14= A-/B+
B15= A-
B16= F
B17= C+

Sorry about the lack of a data table!

 

[burgeon]

I'm still trying to work through this issue, but I still can't use the html chart to post data - can anyone help? Thanks!

 

[Dave Patton]

Consider making your Lookup Table like the following

0 F
1.3 D+
1.7 C-
2 C
2.3 C+
2.7 B-
3 B
3.3 B+
3.5 B++
3.7 A-
4 A

and use a formula

=LOOKUP(A13,$A$1:$B$11)

or

=LOOKUP(A13,{0,"F";1.3,"D+";1.7,"C-";2,"C ";2.3,"C+";2.7,"B-";3,"B";3.3,"B+";3.5,"B++";3.7,"A-";4,"A "})

 

[Domenic]

I'm not able to post using an html table, but I think I can describe it like this:

.........A..........B

1........A.........4.0
2........A-........3.7
3......A-/B+.....3.5
4.......B+.........3.3
5.......B...........3.0
6.......B-..........2.7
7.......C+.........2.3
8.......C...........2.0
9.......C-..........1.7
10.....D+..........1.3
11......F...........0.0
12
13.....3.1
14.....3.5
15.....3.9
16.....1.1
17.....2.4

So, in this example are the GPA's I'm working with are in A13, A14, A15, A16, A17
In B13, B14, B15, B16, B17 I want ot have formulas that look up the letter grade based on the above chart. The results I'm looking for would be:
B13= B
B14= A-/B+
B15= A-
B16= F
B17= C+

Sorry about the lack of a data table!

Try...

B13, copied down:

=INDEX($A$1:$A$11,MATCH(A13,$B$1:$B$11,-1)+(INDEX($B$1:$B$11,MATCH(A13,$B$1:$B$11,-1))<>A13))

Hope this helps!

 

[burgeon]

Wow, both great solutions - that works perfectly. Thanks everyone!