Server Bug Fix: How to sum non numbers by number value

Original Source Link

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

=VLOOKUP(B13,Scores!A1:B3,1)

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.

=AVERAGE(XLOOKUP(B2:B6,{"R","A","G"},{1,2,3}))

enter image description here

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!

=XLOOKUP(ROUNDDOWN(AVERAGE(XLOOKUP(B2:B6,{"R","A","G"},{1,2,3})),0),{3,2,1},{"G","A","R"})

enter image description here

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.

enter image description here

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. 🙂

Alternatively use FIND:

=AVERAGE(FIND(B2:B6,"RAG"))

If one does not yet have Excel O365 this needs to be entered through CtrlShiftEnter

Tagged : / /

Leave a Reply

Your email address will not be published. Required fields are marked *