Averaging Text input

[Michael Pavek]

We assess our status in terms of Green, Amber, Red and
Black.

I have constructed a workbook where each user inputs his
status. I am looking for a method to average several
users' status' with an output of Green, Amber, Red and
Black in the roll-up.

 

[Bernard Liengme]

Hi Michael,
On Sheet 2 in A1:B4 I have
Green 1
Amber 2
Red 3
Black 4

On Sheet1 in A1:C1 I have the status values for George: Green, Red, Amber
In E1 (but it could be anywhere, even on another sheet) I have
=VLOOKUP(A1,Sheet2!$A$1:$B$4,2,FALSE), and this is copied to G1 giving
1,3,2. In H1 I use =AVERAGE(E1:G1) to get a numeric average for George.

Any help?
Bernard