Help…! How do I look for cause and effect

**CDC13** · 08-29-2013, 05:38 AM

Hi, I'm a bit new to fancy statistical things in Excel, so forgive me if this is an obvious question. Also please forgive the non-technical terms.

I have a database with 47 cases where I've recorded a decision made in each case (grant/refuse, entered as text). A bunch (about 40) different reasons were given for the decisions. These overlap, with all cases having several reasons, and many of the same reasons appearing in different cases. The reasons were entered as 0=not mentioned, 1=mentioned. I also have some other variables, like the geographic location of the cases.

I'd like to know which reasons are most likely to case which decision (in other words if it has this then it will be refused, but if it has that it will be granted). I'm sure Excel can do this easily, but I don't know which function to use.

If need be I could aggregate the reasons into smaller categories, but ideally I'd like to keep them all as is. In an ideal world, it would also be nice to include one or more of the other variables.

many thanks

**Norie** · 08-29-2013, 05:43 AM

How is the data organised?

**Sam Capricci** · 08-29-2013, 07:44 AM

Norie's question re: how is the data organised is an important question. You might consider posting an example spreadsheet without confidential or sensitive data so people can help. There are many functions in excel that might work for you. Formulas for search and find etc. For example I am currently working with a file that has over 1600 narratives and I have to find similarities. One thing I did was put a filter on the top row then used the text filter >> contains to find either single words and or multiple words.
People on this site will want to help you, you just have to give them enough info for them to help.

**CDC13** · 08-30-2013, 04:01 AM

The case names are down the side, the decision and the reasons across the top, like so (except that there are around 50 separate issues in the reasons columns bit and 47 separate cases!) Just one decision column. I've done a bunch of bar and column charts by counting numbers of times reasons occur across the whole group, and used pivot tables to relate these to the overall numbers of yeses and nos. Pivot tables also show the patterns in individual cases, but I want the overall pattern - which reasons make people more likely to say yes or no. I think it's regression of some kind, but I'm not sure. Hope that's clearer.

Name Decision Reason 1 Reason 2 Reason 3
Carol yes 1 0 1
Bill yes 0 1 1
Mike no 1 1 0
Sue yes 0 1 0