How do I replace text with numbers?

[njlatino85]

I'm trying to quickly analyze essentially letter grades for my sister's high school, by creating a frequency graph and trying to quickly check for any patterns, yet it won't let me use the =freq () function as long as I'm using the letters themselves as the labels. Help!

And if I were trying to create an average or a standard deviation for it, would i have to change the letter grades to a number representation, or can I just use the letters themselves?

[protonLeah]

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[njlatino85]

Essentially, what I'm attempting to do is to create a macro that would "letter 1" and "FINAL" columns and correlate it to a number A-Z:1-26, so that I can calculate a difference between letter 1 and the final score, so that I can analyze how well each teacher is doing on the whole. Or is there an easier way to approach this?

[martindwilson]

you could use 2 helpers to convert to numbers
=CODE(SUBSTITUTE(B2,"*",""))-64 and =CODE(SUBSTITUTE(D2,"*",""))-64

[njlatino85]

Thank you. One more question. How do I create a formula on a separate worksheet that references back to another worksheet? In this instance, I'm trying to get the average of the grades of students, and simply put it on a new worksheet so that my information is simply more organized.