Averaging a column of numbers after removing certain cells

[Andy0311]

I need help automating several steps I now do in one column to complete my task. The task is to take letter grades run from an Excel column, convert them to numeric values in a new column to the right, and get a numeric average of the values. The column with a letter grade has a special leading character so that has to be removed so only the letter grade is in the cell. Also, not all cells convert to a number because there are blank spaces in the column. What I would like is to run a macro that converts the letter grade to a number (A=4, B=3, C=2, D=1, F=0), removes any cells that are not numbers, and calculates an average of the numbers in the column. Thank you.

Letter Grade	Numeric Value​
B	3​
B	3​
D	1​
A	4​
C	2​
B	3​
	#VALUE!​
	#VALUE!​
	#VALUE!​
A	4​
D	1​
F	0​
A	4​
B	3​

 

[Anfinsen]

Hello,
I don't think VBA is needed for this, see the simple formulas I have added here.
I see where you mention a special character needing to be stripped out, that too can be accomplished with a formula. See below for my example. If you provide examples of the special character, that too can be incorporated into the formula.

Book1
	A	B	C	D
1	Letter Grade	Numeric Value	Letter Conversion	2.27
2	B	3	3
3	B	3	3
4	D	1	4
5	A	4	4
6	C	2	2
7	B	3	3
8		#VALUE!
9		#VALUE!
10		#VALUE!
11	A	4	4
12	D	1	4
13	F	0
14	A	4	4
15	B	3	3
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUM(C:C)/COUNTA(C:C)
C2:C15	C2	=IFERROR(IFS(A2="A",4,A2="B",3,A2="C",2,A2="D",4),"")

 

[Andy0311]

Hello,
I don't think VBA is needed for this, see the simple formulas I have added here.
I see where you mention a special character needing to be stripped out, that too can be accomplished with a formula. See below for my example. If you provide examples of the special character, that too can be incorporated into the formula.

Book1
	A	B	C	D
1	Letter Grade	Numeric Value	Letter Conversion	2.27
2	B	3	3
3	B	3	3
4	D	1	4
5	A	4	4
6	C	2	2
7	B	3	3
8		#VALUE!
9		#VALUE!
10		#VALUE!
11	A	4	4
12	D	1	4
13	F	0
14	A	4	4
15	B	3	3
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUM(C:C)/COUNTA(C:C)
C2:C15	C2	=IFERROR(IFS(A2="A",4,A2="B",3,A2="C",2,A2="D",4),"")

Thanks for helping me. This really helped.

 

[smozgur]

Thanks for helping me. This really helped.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Andy0311]

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

Can one both thank the person who helped provide the solution and mark the post as solved?

 

[smozgur]

Can one both thank the person who helped provide the solution and mark the post as solved?

The "solution mark" is not only an indicator that shows the question has an answer, but also the pointer to the solution post. Therefore, if a question has an answer then we mark that post as the solution.
Hope this makes sense.