Help with formula and variables

[hemai1]

Hi Everyone.
I was hoping someone here could help point me in the right direction. I have an excel sheet with the following columns (Aspiration, Ability and Engagement). Each of these three columns has a data list where you can select three options (Low, medium and High).

I need to figure out how to run a calculation in the column titled "Overall potential".
I need excel to assume Low = 1, Medium = 2 and High =3 which I set up under name manager, but I don't think then setting my data list can actually draw from the variables set in name manager?

The idea is to show the string as above with the numbers masked underneath and then within the 'Overall potential column' have the logic as
IF aspiration+ability+engagement = 3 then return 'Low'
IF aspiration+ability+engagement = 4-7 then return 'Medium'
IF aspiration+ability+engagement = 8-9 then return 'High'

Does anyone know how I'd go about achieving this? I thought maybe using the LET function, but I've gotten myself tied up in knots and can't work it out Any help would be deeply appreciated.

 

[jtakw]

Hi,

Would this work for you:

Book3.xlsx
	B	C	D	E
1	Aspiration	Ability	Engagement	Overall Potential
2	Low	Low	Medium	Medium
3	High	High	Low	Medium
4	Medium	High	High	High
5	Low	Low	Low	Low
Sheet991

Cell Formulas
Range	Formula
E2:E5	E2	=LOOKUP(COUNTIF(B2:D2,"Low")+COUNTIF(B2:D2,"Medium")*2+COUNTIF(B2:D2,"High")*3,{0,4,8},{"Low","Medium","High"})

 

[hemai1]

You are amazing! Yes that works, thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.