Averaging letter grades that convert to numbers for a point total

[mpmachala]

I finally figured out how to get a letter grade to equal a point value and average. However I can't figure out the formula to do the same thing, but with data from other columns for a "final grade" as a numerical value.

Example, I need C4:F4, J4:M4, and Q4:T4 (some times there won't be a grade in the cell) all to average in column X, but with a numeric value instead.

Letter Grade values:
"E"=4
"V"=3.5
"G"=3
"M"=2
"U"=1

For the life of me, I can't figure out why I can't average across disconnected columns?
Thanks,
Mike

 

[BigBeachBananas]

What I am trying to say in a convoluted way, is that if you look at row 5, the value in X5 should be 3.75 (7.5/2=3.75), not 2.5. It seems to be averaging in a zero value in the 3rd Trimester Score, but there isn't a grade yet. (7.5/3=2.5)

I used your formula in X4 and it returned a "0".

View attachment 55106

It's because it assumes blanks cell as grade =0, so (4+3.5+0)/3=2.5

 

[bebo021999]

Is it are you looking for?
Array formula in G4 (For Ex2016 latest, required Ctrl-Shift-Enter)

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3	Date	Date	Date	Date	1st Trimester	Trimester Score		Date	Date	Date	Date	2nd Trimester	Trimester Score		Date	Date	Date	Date	3rd Trimester	Trimester Score		Year Avg. Points		Current Year Score
4	E	V	U	E	3.125	3.125		V	V	U	E	3.000	3.000		E	V	U	E	3.125	3.125		3.08	1	3.08333333
5	E	E	V	G	3.625	7.250		G	E	U	G	2.750	5.500		V	V	M	G	3.000	6.000		3.13	2	6.25
6			E		4.000	12.000					V	3.500	10.500						0.000	-		3.75	3	11.25
Sheet2

Cell Formulas
Range	Formula
U4:U6,N4:N6,G4:G6	G4	=IFERROR((SUM(IFERROR(MATCH(C4:F4,{"U";"M";"G";"E";"V"},0),0))-COUNTIF(C4:F4,"V")*1.5)/COUNTA(C4:F4),0)
V4:V6,O4:O6,H4:H6	H4	=G4*ROWS($1:1)
X4:X6	X4	=AVERAGEIFS($G4:$U4,$G$3:$U$3,"*"&"Trimester",$G4:$U4,">0")
Z4:Z6	Z4	=X4*Y4
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mpmachala]

Is it are you looking for?
Array formula in G4 (For Ex2016 latest, required Ctrl-Shift-Enter)

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3	Date	Date	Date	Date	1st Trimester	Trimester Score		Date	Date	Date	Date	2nd Trimester	Trimester Score		Date	Date	Date	Date	3rd Trimester	Trimester Score		Year Avg. Points		Current Year Score
4	E	V	U	E	3.125	3.125		V	V	U	E	3.000	3.000		E	V	U	E	3.125	3.125		3.08	1	3.08333333
5	E	E	V	G	3.625	7.250		G	E	U	G	2.750	5.500		V	V	M	G	3.000	6.000		3.13	2	6.25
6			E		4.000	12.000					V	3.500	10.500						0.000	-		3.75	3	11.25
Sheet2

Cell Formulas
Range	Formula
U4:U6,N4:N6,G4:G6	G4	=IFERROR((SUM(IFERROR(MATCH(C4:F4,{"U";"M";"G";"E";"V"},0),0))-COUNTIF(C4:F4,"V")*1.5)/COUNTA(C4:F4),0)
V4:V6,O4:O6,H4:H6	H4	=G4*ROWS($1:1)
X4:X6	X4	=AVERAGEIFS($G4:$U4,$G$3:$U$3,"*"&"Trimester",$G4:$U4,">0")
Z4:Z6	Z4	=X4*Y4
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you! This worked perfectly. Sorry for all the confusion with what I was trying to say.