Average letter gradings

[jimbobrich]

Hi,

I have a table of data, column A would have the persons name. Column B - F will have the headings of the 5 tests.

The data will be dumped from 3rd party software in the form of letter grades.


I'd like the formula to look up column A, and every time the students name is matched it Averages the grade in column B. Then ill copy the formula for Column, c,d,e,f.

The tests will be run every week and the data added.

[TABLE="******* 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Test1[/TD]
[TD]Test 2[/TD]
[TD]Test 3[/TD]
[TD]Test 4[/TD]
[TD]Test 5[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Brad[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]D[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for your help.

 

[AliGW]

You need to create a lookup table somewhere to convert grades to points, and then use this to calculate your average grade using INDEX and MATCH.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Test_1	Test_2	Test_3	Test_4	Test_5		Average grade letters
2	Angie	A	A+	A	C	D		B
2e

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX({"A+","A","B","C","D","E","F"},ROUND(AVERAGE(IF(B2:F2<>"",MATCH(B2:F2,{"A+","A","B","C","D","E","F"},0))),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

What Grades are possible?
Do you have grades like A+?

What do you mean by "Averages the grade in column B"

 

[jimbobrich]

Hi Dave,

Thanks for your reply.

Basically what will happen is each person will take all 5 tests weekly the score will be A-G rating. i have a bout 97 people doing the tests, the data will be dumped out by 3rd party software.

I'd just like to paste the data straight into the table as above, adding the next week below. What i want to do is find each persons average of each test over the year.


so my results would look like

Luke test 1 yearly average
Luke test 2 yearly average

 

[jimbobrich]

Hi Ali,

Thanks for your answer, i didnt really want to go down the route of assigning a value.

 

[Dave Patton]

Hi jimbobrich

Did you try the formula?

Edit the formula to reflect the relevant Grades (the score will be A-G rating).

Ensure the rounding calculation is consistent with your requirements.

 

[Dave Patton]

Did you try a Pivot Table?

If you require a formula, you may be able to adapt the suggestion that calculates the average for the 5 tests.

 

[jimbobrich]

Hi Dave,

Yes I tried the formula it works, i might also try a pivot table, that might also help!

Jamie

 

[Dave Patton]

Hi Jamie
The following shows 2 additional ideas. They use a helper column that provides a number for the grades.
I used an Excel table since the data range is probably dynamic.

The Data is filtered by name.

Excel 2010
	A	B	C	D	E
4	Bill	C	3		C
5
6	Name	Letter	Num
7	Bill	A	1
9	Bill	A	1
11	Bill	G	7
13
2e

Cell Formulas
Range	Formula
B4	=LOOKUP(C4,Grades2)
C4	=SUBTOTAL(101,Table1[Num])
C7	=LOOKUP(B7,Grades)
C9	=LOOKUP(B9,Grades)
C11	=LOOKUP(B11,Grades)
E4	=LOOKUP(AVERAGEIF(Table1[[Name ]],A4,Table1[Num]),Grades2)

Named Ranges
Name	Refers To	Cells
Grades	='2e'!$J$7:$K$13
Grades2	='2e'!$K$7:$L$13

 

[Rick Rothstein]

Excel 2010

	A	B	C	D	E	F	G	H
Name	Test_1	Test_2	Test_3	Test_4	Test_5
Angie	A	A+	A	C	D	B

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Average grade letters[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

</tbody>

​

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]{=IFERROR(INDEX({"A+","A","B","C","D","E","F"},ROUND(AVERAGE(IF(B2:F2<>"",MATCH(B2:F2,{"A+","A","B","C","D","E","F"},0))),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Assuming no plus grades (e.g., A+) or minus grades (e.g., A-), then this shorter (two less function calls) array-entered** formula appears to produce the same results as your formula...

=CHAR(ROUND(AVERAGE(0+CODE(B3:F3)),0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself