Suppose I have one Excel sheet or region like this:
Good 5
OK 4
Acceptable 3
Poor 2
Bad 1
Nothing 0
And now I have another region like this:
Task 1 Good
Task 2 OK
Task 3 Good
Task 4 Acceptable
Total ...
The words are obviously text values. For the Total cell, I want to calculate the average of the tasks. I want the strings to be interpreted as the numbers in the first chart. In other words, Total should be the average of 5, 4, 5, and 3.
Basically, I want a 1:1 map of “if you see this thing in a cell, treat it as this other thing when you do math on that cell.” If the value in the cell doesn’t exist in the map, a !#REF or some kind of error value is acceptable. (I don’t need a “treat unknown values as this default” is what I’m saying…although if that’s possible that might be nice to know in the future, and if a default must be given that’s fine too.)
I’d like to be able to easily add more values to the mapping at will, so I don’t want to do a whole ton of if/else if/else if… stuff in each calculated cell. (Or imagine if there were 50 different possible words instead of just 5.)
Is this possible with Excel? (I’m on the latest Office 365 version if it matters.)
