Help with decision tree program

Question

I want to create a very simple, maintable decision tree program in Excel

I have a list of questions, with x possible answers per question.

I have created a simple list of the questions, with a drop-down for the answers.

Now I need an easy  way to evaluate the answers that are provided which produce the result for that answer set.

So far...my quick & dirty idea is...create a set of possible answer sets...compare each answer set to the actual response...and then identify the single answer set that macthes the response.  But since my sets of questions can be large, and the number of possible answers per question large...this quickly becomes crazy to maintain.  10 questions with 4 responses per question creates....4^10=1,048,576 sets...well , we cannot even manage this in Excel.

And I know Excel can do something smarter, better.

The  Q&A table is as basically follows...a simple 6 columns by 8 rows table..column 1 is the question...columns 2 thru 8 contain the posisble answers, where each row can have anywhere between 2 and 7 possible responses.   I will have a a bunch of differnt sizes, but that's the basic concept.

My Excel comfort zone includes Vlookup, Hlookup, IF.  Which is what I would normally try to use in most cases.  I really need something simple...not elegant...so I can easily maintain as this prototype evolves.  I am happy to learn new stuff but I prefer formauls and not Vbasic.

Answer 1

You try this formula =IF(COUNTIF(B2:H2,L2)=1,"Correct","Incorrect") considering B2:H2 contains the range of possible answers and L2 is the answer you want to check it with.

For more assistance you may provide more information with sample data including expected results, so that we can assist you better.

Check the link below for information on uploading data to SkyDrive:

http://answers.microsoft.com/en-us/windows/forum/windows\_7-security/how-do-i-post-a-screen-shot-on-this-forum/c86de820-c620-401c-a804-9f6337cd3053

Answer 2

More info: with a bit of scrolling, you can see the table I am working with

Row#	Subject	ID	Question	Client Response		Answer 1	Answer 2	Answer 3	Answer 4		There needs to be a calculated "Product" value based on the answers to the 4 questions. <br>Each calculated product value will be a unique result of the answers to all 4 questions. <br><br>Producing 4*3*3*4=144 columns of possible answer keys to compare to is not feasible, as <br>some Q&A sets will have too many combinations for Excel to manage. <br>Fore eample, 10 questions with 4 answers per is 4^10=1,048,576 combinations.
1	Subject 1	1	Q1	Zero		Zero	1 - 20	21-100	>100
2	Subject 2	2	Q2	With an ERP package		Manually	With a stand-alone software package	With an ERP package
3	Subject 3	3	Q3	using a Payroll provider		by cheque	by direct deposit	using a Payroll provider
4	Subject 4	4	Q4	International		local	Canada	US	International
	Product