My question was nearly answered by How do I assign a number value to a non-numerical value in Excel
We use a RAG status (red, amber green), and I’d like to average a group of these values.
EG, if there were 5 RAG statuses, which were red, red, amber, green, green then the average would be amber. I calculate this by assigning the value 1 to red, 2 to amber and 3 to green. As such, (1+1+2+3+3) / 5 = 2
I’m struggling to do this with Excel
My table looks like
Voter's name Score Anna R Ben A Charles G Donna G Eddie R Average HELP!
In a second worksheet, I added another table which was
G 3 A 2 R 1
This should mean I can look it up. So, in the Cell that corresponds to Average Score, I added
This only counts 1 of the cells. I don’t know how to Average all the results for each cell in the Score column
There are a few ways to do this, my favorite is with the newer version of Excel 365:
XLOOKUP lets you pass a range as its lookup value, let’s you use a typed array as the lookup array and as the return array. So in this example, XLOOKUP would return an array of 5 numbers, which can then be passed to the AVERAGE function.
edit: As an extension to this, you can re-convert the average to a RAG score by wrapping the function above in another XLOOKUP call and reversing the arrays. I’ve used ROUNDDOWN to ensure the lookup value is an integer, but I suppose you could use ROUNDUP if you’re feeling generous!
If you have an older version of Excel (i.e. no XLOOKUP), then you’re on the right track with VLOOKUP but remember you need to VLOOKUP a value for every row in your table, then AVERAGE that list.
The third option, which may end up making your life easier when you try to do other math on these scores, is simply have them entered as 3,2,1 in the first place. 🙂
If one does not yet have Excel O365 this needs to be entered through CtrlShiftEnter