Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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 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:

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 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'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'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?


Hi all, i need a formula where in the purchase price +transportation cost- discount to be picked from the data given below in green and yellow and to be populated monthwise in their respective section of retail, wholesale and others, product wise.

Month Mar/08 Apr/08 Retail Shirts Pants Socks Blazer tie Whole Sale Shirts Pants Socks Blazer tie Other Shirts Pants Socks Blazer tie Purchase Price Mar/08 Shirts Pants Socks Blazer tie Retail 200 350 50 2000 150 Wholesale 150 300 30 1850 75 other 175 325 35 1900 100 Transportation Mar/08 Shirts Pants Socks Blazer tie Retail 15 10 5 100 5 Wholesale 15 8 3 150 4 other 10 2 5 100 3 Discount Mar/08 Shirts Pants Socks Blazer tie Retail 3 3 1 15 0 Wholesale 2 2 1 10 1 other 1 1 1 10 0 Purchase Price Apr/08 Shirts Pants Socks Blazer tie Retail 215 365 65 2015 165 Wholesale 165 315 45 1865 90 other 190 340 50 1915 115 Transportation Apr/08 Shirts Pants Socks Blazer tie Retail 15 10 5 100 5 Wholesale 15 8 3 150 4 other 10 2 5 100 3 Discount Apr/08 Shirts Pants Socks Blazer tie Retail 3 3 1 15 0 Wholesale 2 2 1 10 1 other 1 1 1 10 0

Hi all, i need a formula where in the purchase price +transportation cost- discount to be picked from the data given below in green and yellow and to be populated monthwise in their respective section of retail, wholesale and others, product wise.

Month Mar/09 Apr/09 Retail Shirts Pants Socks Blazer tie Whole Sale Shirts Pants Socks Blazer tie Other Shirts Pants Socks Blazer tie Purchase Price Mar/08 Shirts Pants Socks Blazer tie Retail 200 350 50 2000 150 Wholesale 150 300 30 1850 75 other 175 325 35 1900 100 Transportation Mar/08 Shirts Pants Socks Blazer tie Retail 15 10 5 100 5 Wholesale 15 8 3 150 4 other 10 2 5 100 3 Discount Mar/08 Shirts Pants Socks Blazer tie Retail 3 3 1 15 0 Wholesale 2 2 1 10 1 other 1 1 1 10 0 Purchase Price Apr/08 Shirts Pants Socks Blazer tie Retail 215 365 65 2015 165 Wholesale 165 315 45 1865 90 other 190 340 50 1915 115 Transportation Apr/08 Shirts Pants Socks Blazer tie Retail 15 10 5 100 5 Wholesale 15 8 3 150 4 other 10 2 5 100 3 Discount Apr/08 Shirts Pants Socks Blazer tie Retail 3 3 1 15 0 Wholesale 2 2 1 10 1 other 1 1 1 10 0


Hi,

I have the following dilemma. I have to make multiple tables with many columns and if I were to type in the data manually it would take me a few weeks. But if I could somehow specify the number of columns(and the number of choices in each column) and then have Excel(or Access) create a table with all the possible combinations it would make my job a LOT easier. For example, let's say I have department stores in multiple cities. I need to create the following table:

CITY DEPT CLOTHING

San Jose Men's Shirts
San Jose Men's Pants
San Jose Men's Shoes
San Jose Women's Shirts
San Jose Women's Pants
San Jose Women's Shoes
Los Angeles Men's Shirts
Los Angeles Men's Pants
Los Angeles Men's Shoes
Los Angeles Women's Shirts
Los Angeles Women's Pants
Los Angeles Women's Shoes

Instead of typing that out manually, is it possible to create some sort of list like:

Cities: San Jose, Los Angeles
Dept: Men's, Women's
Clothing: Shirts, Pants, Shoes

And have Excel or Access create the table with all of the possible combinations?

Thanks for your time.


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...


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.




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.


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


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?


I have an Excel workbook that has the following data in 30 different sheets(each day of the month). The format of the data is like below on each sheet that I receive on daily basis:

HOUSTON $1,190.00
SHOES $500.00
SHIRTS $300.00
PANTS $250.00
SOCKS $140.00
DALLAS $601.00
SHOES $300.00
SHIRTS $125.00
PANTS $141.00
SOCKS $35.00
AUSTIN $1,110.00
SHOES $650.00
SHIRTS $120.00
PANTS $285.00
SOCKS $55.00

At the end of the month, I want to have a summary sheet which shows me the Monthly Sales Report (sum of daily sales for each item, for each city) in the format below:

---------------- Shoes Shirts Pants Socks
Houston:
Dallas:
Austin:

Let me know please if it can be done in Excel. Would really appreciate your help.

Regards!

Shaheed Faiz


I have an Excel workbook that has the following data in 30 different sheets(each day of the month). The format of the data is like below on each sheet that I receive on daily basis:

HOUSTON $1,190.00
SHOES $500.00
SHIRTS $300.00
PANTS $250.00
SOCKS $140.00
DALLAS $601.00
SHOES $300.00
SHIRTS $125.00
PANTS $141.00
SOCKS $35.00
AUSTIN $1,110.00
SHOES $650.00
SHIRTS $120.00
PANTS $285.00
SOCKS $55.00

At the end of the month, I want to have a summary sheet which shows me the Monthly Sales Report (sum of daily sales for each item, for each city) in the format below:

---------------- Shoes Shirts Pants Socks
Houston:
Dallas:
Austin:

Let me know please if it can be done in Excel. Would really appreciate your help.

Regards!

Shaheed Faiz


I just read through a thread posyed today titled:

Select Range of entered data based on last entry in col A then search range and msgbox empty cell for user to complete

Here's another innocent person out there in the world who has Excel and has found this site ONLY to say...

Please find 13 shirts, 6 pairs of pants, 2 sweaters, 1 two-piece business suit, etc, outside my door. Could you please dry-clean and return to me (for no charge of course) all items;
I'd like the shirts on hangers, with light starch; the pants ironed -- be careful folding the sweaters, I like them done just so personally - etc, etc........

Here is ONCE AGAIN another clear example of MrExcel not clearly defining it's purpose, which is "ASSISTING" persons who are FIRST trying to help themselves by FIRST investing in doing something before just CRYING OUT -- from the nursery.

I program excel for a living -- and seeing the MASS GIVE AWAY that goes on daily here "ERKS" me. Can or should anything be done to CURB this on-going problem?


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 guys.

I'm looking to create a stacked bar chart like the one in the attached picture. The issue is with the series of data. Colours always have to be in the same order and for the example of booking out an area in the diagram, this isn't possible.

Does anyone have any ideas as to the best way to go about producing a diagram like this in Excel?

Many thanks in advance!


~~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?