Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Create A Fully Proportional Chart Of 3 Sets And Their Areas Of Overlap (2007)

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi Everyone,

[note: subsequent to submitting this original post, I removed the words "Venn Diagram" from the thread title, and removed confusing language from the text below.]

I would like to chart some overlapping data.

Quick data summary -- skip to next para if you like: I sell 3 types of clothing: pants, shirts and sweaters. And I have sales data on a specific group of clothing buyers: teenagers. Specifically, I know how many teens bought pants only, or bought shirts only, or pants + shirts only, etc..,etc..I would like to chart this data on teen buyers, and a Venn diagram comes to mind.

However, a 3-circle Venn diagram *can not* be proportional across all 6 of its spaces -- at least not if it is made with perfect circles (see post below for explanation).

So I'm wondering: is there a better way -- *preferably* a highly automated way -- to chart 3 overlapping sets. Of course, the great thing about a Venn diagram is that they eye instantly recognizes and comprehends the overlap areas - something a bar chart or similar just doesn't achieve.

What else might work? Ideas, anyone?

Cheers,

Jay

View Answers     

Similar Excel Tutorials

Combine Multiple Chart Types in Excel to Make Powerful Charts
In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...
Changing Any Element in a Chart in Excel
In this tutorial I am going to go through the Layout tab in more detail and show you how to build up chart layouts ...
Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Dynamic Charts in Excel - The Easy Way
How to create dynamic charts that update automatically when new data is added to Excel. This method uses the table ...

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics







How can we do it ?

Quote:


I need to make an Excel spreadsheet that will create a Venn Diagram with anywhere from 2 to 6 circles.

For instance, if i needed a diagram with 3 circle of A, B and C. The total population is 836. A is US citizens and n=419. B is asian descent and n=171. C is college educated and n=246. A/B is n=80, Asian us citizens. B/C is n=87. A/C n=138. ABC is 53. I would enter all these numbers and a venn diagram would be produced.

Does anyone know of software or code that will do this? Please help. thank you.


I didn't like this idea
http://www.internet4classrooms.com/excel_venn.htm

I want excel to read directly the numbers , no need to add it by myself . Let's say I have 100 ball , 40 blue 50 red 10 red and blue , i need excel to draw this overlap circles ?

if I change from the data any number it will be reflected in the chart .




Hi..

How can I create a Venn Diagram in excel, or the closest thing to it. I would like all similar countries in the middle (where two circles overlap), then the differences on the outside with the country names. See my attached list.

MSCI vs. FTSE Emerging Market Countires.xlsx

Thanks



I have a list of 17 firms who are in 8 possible states. I need to see/show where there is the highest (next highest, etc) concentration of overlap amongst the firms. I'm thinking a Venn diagram would be perfect, but I can't figure out how to put it into an Excel Graph. I'm looking for something that's accurate and automated not a manual approximation.

Can anyone help me?

Here's the file: VennData.xlsx

Thanks in advance!
ARH3

Hi All,

I have created two Venn Charts (using smart art): Venn Chart 1 is based on two products and Venn Chart 2 is based on 3 products.

I basically want Venn Chart 1 to appear when values are selected from a data validation list in cells C9 and C10 (as shown below). If a third product is selected in cell C11 Venn Chart 1 disappears and Venn Chart 2 appears. If value in cell C11 gets deleted then Venn Chart 1 appears.

Product 1 (C9)
Product 2 (C10)
Product 3 (C11)

Could someone please help?

Thanks


I have a project where we are sampling data. We have 1,000 toys. All 1,000 fall into 1 or more group or none of them:

Group 1 - Approriate for ages 5-7
Group 2 - Approriate for ages 8-10
Group 3 - Approriate for ages 11-12

Any toy may be part of 1 group, 2 or all 3.

I have gathered all this data into a spreadsheet for the different combinations:
Group 1
Group 2
Group 3
Group 1 Only
Group 2 Only
Group 3 Only
Group 1 AND 2 AND Not 3
Group 1 AND 3 AND Not 2
Group 2 AND 3 AND Not 1
Group 1 AND 2 AND 3
Not Group 1 AND Not 2 AND Not 3
Group 1 OR 2
Group 1 OR 3
Group 2 OR 3
Group (1 OR 2) AND Not 3
Group (1 OR 3) AND Not 2
Group (2 OR 3) AND Not 1
All Docs

Now I need to chart this. Yep. You heard me.

I have a Venn Diagram, which I manually created (overlapping circles)

One shows exclusive data for the 3 circles:
Group 1 Only
Group 2 Only
Group 3 Only
Group 1 AND 2 AND Not 3
Group 1 AND 3 AND Not 2
Group 2 AND 3 AND Not 1
Group 1 AND 2 AND 3


The other is inclusive (spheres contain possible overlap to other sections):
Group 1
Group 2
Group 3
Group 1 AND 2
Group 1 AND 3
Group 2 AND 3
Group 1 AND 2 AND 3

What I am struggling with is how to show the OR relationships:
Group 1 OR 2
Group 1 OR 3
Group 2 OR 3
Group (1 OR 2) AND Not 3
Group (1 OR 3) AND Not 2
Group (2 OR 3) AND Not 1

I have been researching different chart types but nothing is standing out as a good way to illustrate the data that represents OR relationships in data.

Any ideas would be great!!!


I have a project where we are sampling data. We have 1,000 toys. All 1,000 fall into 1 or more group:

Group 1 - Approriate for ages 5-7
Group 2 - Approriate for ages 8-10
Group 3 - Approriate for ages 11-12

Any toy may be part of 1 group, 2 or all 3.

I have gathered all this data into a spreadsheet for the different combinations:
Group 1
Group 2
Group 3
Group 1 Only
Group 2 Only
Group 3 Only
Group 1 AND 2 AND Not 3
Group 1 AND 3 AND Not 2
Group 2 AND 3 AND Not 1
Group 1 AND 2 AND 3
Not Group 1 AND Not 2 AND Not 3
Group 1 OR 2
Group 1 OR 3
Group 2 OR 3
Group (1 OR 2) AND Not 3
Group (1 OR 3) AND Not 2
Group (2 OR 3) AND Not 1
All Docs

Now I need to chart this. Yep. You heard me.

I have a Venn Diagram, which I manually created (overlapping circles)

One shows exclusive data for the 3 circles:
Group 1 Only
Group 2 Only
Group 3 Only
Group 1 AND 2 AND Not 3
Group 1 AND 3 AND Not 2
Group 2 AND 3 AND Not 1
Group 1 AND 2 AND 3


The other is inclusive (spheres contain possible overlap to other sections):
Group 1
Group 2
Group 3
Group 1 AND 2
Group 1 AND 3
Group 2 AND 3
Group 1 AND 2 AND 3

What I am struggling with is how to show the OR relationships:
Group 1 OR 2
Group 1 OR 3
Group 2 OR 3
Group (1 OR 2) AND Not 3
Group (1 OR 3) AND Not 2
Group (2 OR 3) AND Not 1

I have been researching different chart types but nothing is standing out as a good way to illustrate the data that represents OR relationships in data.

Any ideas would be great!!!


I'm building a Venn Diagram to show revenue across 3 Lines of Business within our organization. The overlap sections will show cross-selling between the lines of business.

I would like to SCALE the diagram, ideally automatically, so that the circles are representative of their revenue size, and the overlaps are also in proportion.

Does anyone know of a macro, add-in, or piece of (cheap) software that could do this for me?

Thanks in advance.


I have a venn diagram in my spreadsheet, but cannot re-size the circles to
coincide with specific values in cells.

Any help is appreciated.

tim





Hi,

I am looking for the best way to create a venn diagram with multiple data thereby the data from several 'circles' would proportionately overlap. I have seen an example on Chandoo that did this with vba but only for two circles. Does anyone know of any good examples? I've read that bubble charts may be also used but haven't seen any examples.

Thanks.

abousetta

Hi,

I'm aware there is another thread or two about Venn diags but I'm wondering if there's a way of creating proportional venn diagrams?

Thanks in advance,

RL


Does anyone know if it is possible to get excel to draw a Venn Diagram from a spreadsheet? I know there is not a function to do this, but is there a way round it?


Hi , I have a problem on sorting a LARGE set of numbers in excel, trying to plot a Venn diagram with numeric values. Tried smartdraw but couldnt work out, if anyone could help?


I have followed this blog to create a two-circle overlapping Van diagram with data linked in. Now I'm curious as to how to build a three-circle propotional van diagram using the same technique?

http://www.data-miners.com/blog/2008...agrams-in.html

I'm thinking this is more of a math question than excel...


I have a XR Bar Chart where the samle average is based on 5 entries. There are 40 samples on the chart. I use this chart in real time and I need it to populate my XY Scatter Diagram as the data is populated. My problem is that sample average formulas in the XR Bar Chart are plotted as zero on the XY Scatter Diagram. This is a problem because XY Scatter Diagram also plots 3 other series (target, upper control and lower control) and the XY Scatter Diagram is not readable until all of the sample poplulation is entered. I am asking my employees to look at the chart and make adjustments when there is a negative trend. I have tried everything I can think of, any suggestion would be great.

Thanks,
John




Pivots and Percentages....HELP

--------------------------------------------------------------------------------

Can this be done. I want my pivot to show:

1) Clerk then Dept then Type then Item. These are in the row area. In the column area will be Mont. I then want qty in the page area and I want to bring qty in a second time as a percentage.

Ex: raw data

Clerk....Dept..........Type....Item.........Jan........Feb

Jones...Menswear...Pants...Casual.......10.........5
Jones...Menswear...Pants...Dress.........20.........10
Jones...Menswear...Shirts...Casual.......44.........16
Jones...Menswear...Shirts...Dress........44.........16
Smith...Menswear...Pants...Casual.......10.........5
Smith...Menswear...Pants...Dress.........20.........10
Smith...Menswear...Shirts...Casual.......44.........16
Smith...Menswear...Shirts...Dress........44.........16

Now, I can subtotal this on: Type and see how many pants vs shirts were sold by month by clerk but I want a Percentage of Pants vs Shirts....sort of a percntage but on the subtotal for the clerk...make sense...ideas?

It would tell me for January, Jones sold 30 pants and 88 shirts. Out of the 118 total items for the month for him, 25.4% were pants and 74.5% were pants.


Let's say I have 5 different categories: pants, shirts, sweaters, shoes, underwear. For each category, I have different price ranges:

Pants: $0-$50, $51-$75, $75+
Shirts: $0-$35, $36-$60, $61+
etc.

And for each of those price ranges, I have assigned: low, medium, high.

So I'm trying to do a vlookup that looks at a price, finds the price range it falls under and returns either low, medium, or high. Normally it's simple enough that I do vlookup = true, but I need it to based on the style whether to look in the pants, shirts, or sweaters, etc category.

I would do if then statements but the actual categories are more than the number of statements allowed.

Any ideas?




Hello,

I try to add a piece of map behind my chart, the map should be W30, N75, E060.
What I want to achieve is a perfect overlapping of my chart with the map, I mean the location of my points (from chart) to be the same on map. Is it possible?
I know that there are differences between map and chart scale, I tried a lot of ways but I have no idea how to do it. Could you help me, please?
Thank you very much for your help.

hey everyone,

having a little trouble getting this right. a little help would be appreciated.

basically i have a prompt with options. for example:

1) shoes
2) shirts
3) pants

if 1 is entered i want it to run a different option than if 2 is selected etc. so it's a conditional argument. here is what i have....

C = InputBox("what are you wearing?" & Chr(13) & "1) shoes" & Chr(13) & "2) shirts" & Chr(13) & "3) pants", "question", vbOKCancel)

If C = "2" Then

w = InputBox("Which One?" & Chr(10) & "1) red shirt" & Chr(10) & "2) blue shirt" & Chr(10) & "3) green shirt", "question", vbOKCancel)

If w = "1" Then w = "red shirt"
If w = "2" Then w = "blue shirt"
If w = "3" Then w = "green shirt"

Sheets("Collected Data").Range("C" & x + 1) = w

If C = "3" Then

w = InputBox("Which One?" & Chr(10) & "1) black pants" & Chr(10) & "2) blue pants" & Chr(10) & "3) tan pants", "question", vbOKCancel)

If w = "1" Then w = "black pants"
If w = "2" Then w = "blue pants"
If w = "3" Then w = "tan pants"

Sheets("Collected Data").Range("C" & x + 1) = w

you get the idea. where am i going wrong with the formatting?

I keep getting a compiling error because i don't have an End If statement in here, but i can't seem to get it right.

any help is appreciated

thanks


Hello,

I have 700+ rows of survey responses with "free form" text such as the below example. How can I sort all of these to arrive at the top 10 or 15 responses?

I am sure there are mis-spellings, extra spaces and other problems scattered thruout the list but whatever direction you can provide is very much appreciated!

Shirts, luggage tags, luggage, hats
Shirts, hats sweat shirts
jackets, hats, luggage, etc...
shirts - work appropriate
Gold shirts, Dress shirts, hats, coffee travel mugs, sales bags, fleece's, outerwear.
Golf shirts, hats, jackets, travel bags, luggage tags
Swetshirt or Jacket
hoodies, tshirts, yoga pants
dress shirts/ mugs/jackets

Thank you,
NIRAV


I have an Pie chart. My problem is that sometimes the data
labels around the pie chart overlap each other in areas where the
values are small. Is there any way to prevent the labels from
overlapping without having to manually move them? Any help is greatly
appreciated.

Jay




Hi,

I am new at creating bubble charts and am stuck on making the x and y axis scales come out correctly. I have created two data points, first x and y axis = 0. Second is on the same y axis (i.e. y = 0) and the x axis is the radius of the first bubble. The end result should look like a venn diagram but the bubbles are far apart and don't know how I should be scaling the x and y axes because they are coming out much smaller than the anticipated radius for each bubble.

Attached is my attempt.

Thanks.

abousetta



I am trying to use the CountA functions and it is not returning the correct results. I think my problem maybe in the order that I using. Here is what I am trying to do.
I have a list items that references a table of up to four criteria. To keep it simple well say it a list of players and the table is the uniform of shirts and pants. The function I am trying to build will look at the cells that contain the criteria and if there if there is text count it and return a number. I do not need a range to go through the list I am using the offset function to pull the selected item so the data that is need comes out on a single row. I want the function to count the cell if there is text but ignore it if there is a zero and depending on the combination out put a number between 1-4.
The different combination each player in the list could have is (1 shirt 0 shirt and 1 pants 0 pants), (1 shirt 1 shirt and 1 pants 0 pants), (1 shirt 0 shirt and 1 pants, 1 pants 0 pants), and ( 1 shirt 1 shirt, and 1 pants 1 pants). Every play must at least have the 1st shirt and 1st pants. I want the function to set each combination equal to a number. That number will be read by a macro and feed to a case statement. What I have so far is this.

=IF(AND(G2=0,COUNTA(C2,D2,F2)=3),1,IF(AND(D2=0,COUNTA(C2,F2,G2)=3),2,IF(AND(D2=0,G2=0,COUNTA(C2,F2)=2),4,3)))
A sample row for C2,D2, F2, and G2 would be Yes, Yes, 0, Yes

The problem is getting the case of (1 shirt 0 shirt, and 1 pants 1 pants) which in my formula should return a value of 4.
Any suggestions would help a lot.

Thank You



Excel VBA - how to make a excel vb chart diagram with 2 variables (like this - link to a chart diagram with 1 set of data) http://www.excelforum.com/excel-char...05#post3417105

~~Forgot to add - I've the file to Chandoo so you may see it elsewhere (or not - it's pretty rough)

Hi,

I've been look for an excel tool to draw venn diagrams with the 'circle' size in propartion to the set (population) sizes and the correct degree of intersect.
Having no luck I've made one myself - it's not pretty (the code sure is ugly) but it get's the job done.

May not work in versions earlier than xl07 - haven't been able to test

So take a look - pull it to pieces all you want.

**Warning**
Attachment contains Macros
**********

Orginal text in thread:
Silly question..but that's never held me back.

Are there instructions for posting spreadhseets to this forum anywhere?


Hi All -

I am trying to build a tool to help prioritize activities into a 2x2 Matrix. I am using the scatter diagram chart with Excel to do this.

My scale is from 0 - 10 on both X and Y and my quadrants are even - the intersection line is at 5. There will be 40 - 60 items on the list.

The Data Series is currently set up as two columns. One thought that I had was to set up the data in a series of rows using each description as that series' name. This is obviously pretty manual and makes my chart very messy.

Is there a way to show in which quadrant each item falls outside of the chart? Or, alternatively a way to make my chart a little more readable?

Thanks for any assistance that you can provide. It's very much appreciated.

Viki