Using High, Medium & Low as responses to questions across multiple columns, how do I determine how the overall rating?

[MellyX]

 

[Alex Blakenburg]

I suspect that others come up with some better options but in MS365 & 2021, this might work for you.

20230221 Low Medium High MellyX.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Position	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Average
3		Medium	High	High	Low	High	High	Low	High	High	High	High	High
4		Low	Medium	High	High	Medium	Medium	Medium	Medium	Medium	Medium	Medium	Medium
Data

Cell Formulas
Range	Formula
M3:M4	M3	=CHOOSE( ROUND( SUM( SWITCH(B3:L3,"Low",1,"Medium",2,"High",3)) /COUNTA(B3:L3), 0), "Low","Medium","High")

 

[Fluff]

Another option, using the median rather than the average

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L		N
1
2	Position	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12		Median
3		Medium	High	High	Low	High	High	Low	High	High	High	High		High
4		Low	Medium	High	High	Medium	Medium	Medium	Medium	Medium	Medium	Medium		Medium
5		Low	low	High	Medium	Medium	Medium	High	High	High	High	High		High
Summary

Cell Formulas
Range	Formula
N3:N5	N3	=CHOOSE(MEDIAN(SWITCH(B3:L3,"Low",1,"Medium",2,"High",3,0)),"Low","Medium","High")

 

[MellyX]

Thanks this worked a treat!

​

 

[Alex Blakenburg]

Not sure which option you ended up going with but glad we could help.
Applying Fluff's more concise syntax to the one I gave you reduces it to this:

=CHOOSE(
        ROUND(
            AVERAGE(
               SWITCH(B3:L3,"Low",1,"Medium",2,"High",3)),
         0),
   "Low","Medium","High")