Forum Discussion
How to do operations on cells that contain letters
I need to be able to type a character string like A- into a cell and have excel average that cell along with other cells that contain letters or numbers. I would have conversions for what number each character string would equal. There is the option of using vlookup but that takes a helper cell for every cell containing a letter and that would take a long time to create since I will end up having nearly 10000 cells that could contain letters. As context the purpose is to convert letter grades to number grades so they can be averaged. Here is an example, say A- equals 95; B+ equals 93; and B equals 90. Now have cell A2 contain B, cell A3 contain A-, cell A4 contain B+. Now have cell B2 have this formula =AVERAGE(A2:A4) I want it to come back with a result of 92.6.
2 Replies
You don't need a helper column (but even if you did it wouldn't be that big a deal and could even be hidden). You can just use your lookup table:
=AVERAGE(XLOOKUP(A2:A4, Grades[letters], Grades[values], ""))
In this case I used "Format as Table" to make the table of grades and values and named the table "Grades", but you could replace Grdae[letters] with the corresponding range.
If a grade entry doesn't match a value in the table I used "" to ignore it, but you might prefer to remove that so you get an error and you know to look into it to find out why.Notice how the D is ignored because there is no "D" in the table of Grades to the right
One more note: If you really don't like looking at all that inside the formula and want it to look like the simple AVERAGE(A2:A4) you could create a LAMBDA function (inside your named manager) something like
AVERAGEGRADES =
LAMBDA(grades, AVERAGE(XLOOKUP(grades, Grades[letters], Grades[values], "")))
then you can just enter in you cell:
AVERAGEGRADES(A2:A4)
