Can I show text that represents a number

[Ron]

I want to take a table that shows text in a column for several rows, but the
text in each row represents a number that I can average?
Here's the table - If I want the A+ to represent a number when O enter A+,
how do I do that?:
A+ 4.5
A 4
B+ 3.5
B 3
C+ 2.5
C 2
D+ 1.5
D 1
F 0

 

[Bernard Liengme]

Let's say that your table is in A1:B8
In C1, I enter some text like: C+
In D1 I can use =VLOOKUP(C1,A1:B8,2,FALSE) to give me the result 2.5
Of course, your table could be on another sheet and then we use
=VLOOKUP(C1,Sheet3!A1:B9,2,FALSE)
best wishes

 

[Mike H]

try this

=SUMPRODUCT(COUNTIF(A1:A10,{"A+";"A";"B+";"B";"C+";"C";"D+";"D";"F"}),{4.5;4;3.5;3;2.5;2;1.5;1;0})/COUNTA(A1:A10)

Change the range to suit. Any text other that the grades in the range will
give an incorrect result.

Mike

 

[Gary''s Student]

For letter grades in column A:

=(COUNTIF(A:A,"A+")*4.5+COUNTIF(A:A,"A")*4+COUNTIF(A:A,"B+")*3.5+COUNTIF(A:A,"B")*3+COUNTIF(A:A,"C+")*2.5+COUNTIF(A:A,"C")*2+COUNTIF(A:A,"D+")*1.5+COUNTIF(A:A,"D"))/COUNTA(A:A)

Thus if column A contained:

A
A+
C
C+

the formula returns 3.25

 

[Teethless mama]

Try this:

=MAX(69-CODE(UPPER(A1)),0)+(LEN(A1)=2)*0.5