Opinions on decision Tree concept in Excel

[PFDave]

Morning all,

Just wanted to gauge your thoughts on the best way to implement a decision making process in excel.

Ultimately we have about 100 resolutions depending on what happens at certain points. At each level the options will split and currently there is someone gathering data in the form of a decision tree.

I have started to think about how to do this through excel in a format where the user will answer questions in an order until they reach the only possible answer. I believe named ranges would be the best practice for this so depending on what they select from the first option would then depict what the options were on the second question and so forth. The issue I can forsee is that it's not going to be a straight line of questioning and something that may need 10 question to reach a resolution could also be resolved earlier depending on the answer to questioning. The chap who is gathering the detail for me is doing so for one example for now but I would like to have a template I can ask him to complete to save my work further down the line.

I'm going to mock up an example which I'll attach later if required but just wanted some opinions before I invest too much time into it.

If any of you have any templates you're happy to share that would be great or if you have any experience also I'd love to hear from you.

Thanks

Dave

[TMS]

Google: excel dependent drop down list ... as a starter.

[PFDave]

Hi TMS,

Already sorted on that part

[Kyle123]

I think it sounds very hard to do in Excel, I think it's probably impossible to do without VBA (well highly improbable anyway) if certain questions branch and loop

[PFDave]

I think it sounds very hard to do in Excel, I think it's probably impossible to do without VBA (well highly improbable anyway) if certain questions branch and loop

Hi Kyle,

I was suspecting that this would spiral without VBA, do you have any recommendations on a template for the code, or is it better to wait until I've mocked up a sample and then I could get help to tailor the VBA specific to my requirements?

Regards

Dave

[Kyle123]

The problem as I see it is that decision trees by their nature are hierarchical, Excel simply does not do hierarchical data without all sorts of nasty hacks - Excel is designed to work with tables of data and struggles with data not in this format (hierarchical, relational etc). - look at the faff of dependant dropdowns

A decision tree is doubly complicated since not only is it hierarchical, it also loops back to earlier questions - this isn't something that's easy to do.

If it were me, I'd see if there's another tool for the job, I strongly suspect that this will exist as a fairly low cost web app, that will be much easier to administer, use and save loads of work.

If you absolutely had to do it in Excel, I think I'd go down the route of a flat table with a key & question in each row, outcomes in columns, where outcomes lead to other rows ( if you want some google foo, it's broadly similar to how one models an organisational structure in a database). I'd then parse this into a hierarchical structure and display it in a dynamic userform where the next step is read from the outcome of the current.

You may have an easier time of this in access

[PFDave]

Thanks Kyle, in theory this decision tree/process would never loop back it would only ever move forward but I do understand your point. I'll keep having a think about this but will look about at alternatives also.