Need a Venn diagram, but in numbers? Not sure how to approach this.

[seacliffs]

Hi Everyone -

I've been stumbling around trying to problem solve a massive spreadsheet I have. I know how to do what I want in very laboriously - but with 15 columns, I'd really, really, really, really, really like to avoid that.

Here's a sample data set

	Food	Water	Bedding	Heat
1	Reported	Reported	Not reported	Reported
2	Not reported	Not reported	Not reported	Reported
3	Reported	Not reported	Reported	Not Reported
4	Not reported	Reported	Reported	Reported

Here's the sort of results I need:

Food: 2 reported and 2 not reported, 50% and 50%
Water: 2 reported and 2 not reported, 50% and 50%
Bedding: 2 reported and 2 not reported, 50% and 50%
Heat: 3 reported and 1 not reported, 75% and 25%

Food and water and bedding and heat: 0 reported and 4 not reported
Food and water and bedding: 0 reported and 4 not reported
Food and water: 1 reported, 3 not reported
Food and bedding: 1 reported, 3 not reported
Food and heat: 1 reported, 3 not reported
Water, bedding, heat: 1 reported, 3 not reported

And basically every permutation like that.

I can get the reporteds and not reporteds via filters and pivot tables and count ifs - but running the permutations is doing my head in a bit and I'm afraid I'm making data errors.

Suggestions?

Thanks!

 

[Dan_W]

Your title says that need a Venn diagram, but you make no reference to that at all in your original post, instead saying that you're having difficulty running the permutations.

Why does it need to be a Venn Diagram? I'm not sure that this is the appropriate (or even correct) visualisation for this data. I've given it only a little bit of thought, but Venn Diagrams seem to visualizations that show a number of elements and the extent to which those elements have a shared attribute. Your dataset above seems to have another one or two dimensions to it, that I don't know that a Venn Diagram has the capacity to show. Mind you, I could be completely be wrong.

As to the permutations, I going to have to plead ignorance on the best method of doing this, but I wonder if a recent article posted in this forum might be of some help to you? Excel Formula: An easy way to get all combinations of items in different columns

Sorry I can't be of more help.

 

[seacliffs]

Your title says that need a Venn diagram, but you make no reference to that at all in your original post, instead saying that you're having difficulty running the permutations.

Why does it need to be a Venn Diagram? I'm not sure that this is the appropriate (or even correct) visualisation for this data. I've given it only a little bit of thought, but Venn Diagrams seem to visualizations that show a number of elements and the extent to which those elements have a shared attribute. Your dataset above seems to have another one or two dimensions to it, that I don't know that a Venn Diagram has the capacity to show. Mind you, I could be completely be wrong.

As to the permutations, I going to have to plead ignorance on the best method of doing this, but I wonder if a recent article posted in this forum might be of some help to you? Excel Formula: An easy way to get all combinations of items in different columns

Sorry I can't be of more help.

The Venn diagram is the only way I could think to visualise it - in that I want to say something like - one study (which is what the numbers represent in my data), covered categories a, b, c, and d, but three studies covered A only. I'm sure there are better ways to visualise it - I'm just brain stuck! (Feel free to suggest!) I'll have a looksee at that post link. Thanks!