Data conversion: Text to Numeric to Support Averaging

F

Frogs5

I have a mix of grades (academic achievement) in numeric (98, 86, etc.) and
text
(A, B+, etc.) format that I need to average for a final grade. While I can
use LOOKUP variants to display the text as numeric, I have yet to figure out
how to manipulate the text so it can be AVERAGEd with the numeric data. I am
not totally new to Excel; however, I figured there are smarter folks in the
community that may have already wrestled this issue into a solution.
Congratulations! Please share your results...thank you!
 
L

Luke M

You'll need to first create a helper column. Assuming you have a lookup
table setup somewhere to convert the text to numbers, can do:
=IF(ISNUMBER(A2),A2,LOOKUP(A2,Your_Table))

Then average the helper column.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text string to Numeric string 1
Averaging Hourly Data 2
Conditionally formatting numeric data and text differently 1
Converting Letter Grades to Numeric 6
Converting Text to Numeric Value 2
AVERAGE Row of Numbers and Return Corresponding Numeric Label 14
How to convert text fields to numeric fields without losing data in text fields. 24
Text Data to Numeric for Report 3

Top