Help please - yes-no form

[evatyn]

Hello,
I am a complete Excel novice tasked with creating a form that contains three questions, the answer to each is either High or Low. Based on the answers, there are 8 possible outcomes e.g.
Low - Low - Low = Type 8
High - High - High = Type 7
Low - High - High = Type 6
High - High - Low = Type 5
High - Low - High = Type 4
Low - High - Low = Type 3
Low - Low - High = Type 2
High - Low - Low = Type 1

I created drop down boxes for each with either Low or High but no idea how to make the next cell determine the type based on the 3 answers? Any help would be much appreciated

 

[Eric W]

Welcome to the MrExcel forum!

Your list has no easily identifiable pattern, so a look-up table seems best. Here's one way to accomplish that:

Book2
	A	B	C	D	E	F	G	H	I
1	Value1	Value2	Value3	Type		dropdown1	dropdown2	dropdown3	Type
2	Low	Low	Low	8		Low	High	High	6
3	High	High	High	7
4	Low	High	High	6
5	High	High	Low	5
6	High	Low	High	4
7	Low	High	Low	3
8	Low	Low	High	2
9	High	Low	Low	1
Sheet17

Cell Formulas
Range	Formula
I2	I2	=SUMIFS(D2:D9,A2:A9,F2,B2:B9,G2,C2:C9,H2)

The table can be incorporated in the formula itself, but it would make it pretty awkward.

Hope this helps!

 

[evatyn]

Eric - it is working and I am literally crying with joy. I hope this did not take you too long to come up with, but you absolutely saved my bacon here and I am soo grateful! ))

Is there a way to assign a name to each type? for example: If type 8 shows in one cell then the call next to show name e.g. if type 7 show "Definitive", if type 6 "Dependent" , if type 5 "Dangerous" etc?

 

[Eric W]

Sure!

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Value1	Value2	Value3	Type	Description		dropdown1	dropdown2	dropdown3	Type	Description
2	Low	Low	Low	8	Dire		Low	High	High	6	Dependent
3	High	High	High	7	Definitive
4	Low	High	High	6	Dependent
5	High	High	Low	5	Dangerous
6	High	Low	High	4	Dastardly
7	Low	High	Low	3	Devilish
8	Low	Low	High	2	Demonic
9	High	Low	Low	1	Dreadful
Sheet17

Cell Formulas
Range	Formula
J2	J2	=FILTER(D2:D9,(A2:A9=G2)*(B2:B9=H2)*(C2:C9=I2))
K2	K2	=FILTER(E2:E9,(A2:A9=G2)*(B2:B9=H2)*(C2:C9=I2))

I took a different approach this time, since you are looking for text instead of a number, and since you have Excel 365. See if this works for you!

 

[evatyn]

Haha works a treat, thanks again, you're a star! ))))

 

[Eric W]

Happy to help! Thanks for the feedback! ?