Need to Calculate Average Letter Grade for Students in a class.

[MasterT]

So I've been given an assessment for my Yr. 11 Spreadsheets & Databases Course. I have been tasked with creating a spreadsheet for the Staff of my P.E. department, which is both easy to use and edit. I've been given a list of tasks, with marks corresponding with a letter grade. I only have the range for each grade. The data for each must be of my own making, but the marks per task are extremely different. So instead I planned on assigning each task with a letter (A,B,C,D, and E) and wanted to calculate what the student's overall letter grade was.

	Beep Test		Vertical Jump		Illinois Agility
Overall Grade (Letter)	Raw Score	Grade	Raw Score	Grade	Raw Score	Grade
What Formula should I use?	12	B	10	A	52	A
	14	A	4	C	37	C
	10	C	12	A	27	D
	11	B	-5	E	34	D
	10	C	-2	C	52	A
	9	D	1	E	39	C

I used the IF function to calculate the grade for each individual subject. I've been told my school is moving to Letter based grading, and won't be giving students a number anymore. Appears simple but I can't find any way of doing it. Relatively new to Excel.

 

[AliGW]

What are the grade boundaries?

 

[Bob Phillips]

How do you handle a mid-point grade? For instance, an A, and 2 C's is an average what? B, B+?

 

[retired007geek]

MasterT,

If you assign a values to the letters: A=5, B=4,C=3, etc.

Then you can use this formula: =CHAR(70-INT(((COUNTIF(A1:A11,"A")*5)+(COUNTIF(A1:A11,"B")*4)+(COUNTIF(A1:A11,"C")*3)+(COUNTIF(A1:A11,"D")*2)+COUNTIF(A1:A11,"E"))/COUNTA(A1:A11)))



Note that the formula is copyable across the row.

HTH

 

[MasterT]

What are the grade boundaries?

Beep Test:

Grade	Year 7	Year 8	Year 9	Year 10
A	10	11	12	13
B	8.5	9.5	10.5	11
C	7	8	9	9.5
D	6	6.5	7.5	8
E	<6	<6.5	<7.5	<8

Sit & Reach

Grade	Year 7	Year 8	Year 9	Year 10
A	6	7	8	10
B	2	3	4	6
C	-5	-5	-4	-3
D	-7	-6	-5	-4
E	-9	-8	-6	-5

Vertical Jump:

Grade	Year 7	Year 8	Year 9	Year 10
A	40	43	46	50
B	35	38	40	43
C	29	31	33	35
D	23	24	26	27
E	17	17	19	19

Illinois Agility:

Grade	Year 7	Year 8	Year 9	Year 10
A	15.7	15.7	15.1	15.1
B	16.7	16.7	16.1	16.1
C	17.6	17.6	17.1	17.1
D	18.6	18.6	18.1	18.1
E	19	19	18.6	18.6

Sit Ups:

Grade	Year 7	Year 8	Year 9	Year 10
A	50	52	54	56
B	45	47	49	51
C	40	42	44	46
D	30	32	34	36
E	25	27	29	31

Push Ups

Rating	Year 7	Year 8	Year 9	Year 10
A	40	42	44	46
B	30	32	35	37
C	23	25	30	32
D	16	18	25	27
E	<16	<18	<25	<27

Pull Ups:

Rating	Year 7	Year 8	Year 9	Year 10
A	10	12	15	15
B	5	7	9	10
C	3	4	6	7
D	1	2	3	4
E	0	0	0	1

12 Min Run

Rating	Year 7	Year 8	Year 9	Year 10
A	2200	2300	2400	2500
B	2000	2100	2200	2300
C	1800	1900	2000	2100
D	1600	1700	1800	1900
E	<1600	<1700	<1800	<1900