New graph instead of a Venn (office 2002, 2003)

Topic

New Reply

1623871
1368829
1395171

I have these 3 pieces of data. They were represented as a venn diagram, 2 circles (left circle= 1623871, right circle=1395171) with the intersection equal to 1368829.

How else could i represent this data? Would a stacked bar graph be appropriate? thank you

Reply | Quote

Replies

A Venn diagram is especially suitable for showing overlap between data. A stacked bar or column chart could be used to show the same data, but they don’t convey the idea of overlap, so you’d have to make that clear in a caption.

Reply | Quote

yes, it is a good way but there is no automated way to make the 2 circles propotionate and data labels to appear in the center of the 2 circles in addition to the “overlapped” section. I have to do 30 of them a month and want to automatically make them. Is the stacked bar the best 2nd choice? thanks

Reply | Quote

I’d ask the person or persons who have to use the charts.

Reply | Quote

See post 265,155 which you started 2.5 years ago and the responses. My reply in post 265,349 shows how to create a proportianl Venn diagram that could be adapted realtively easily if you only have 2 circles and an overlap.

The difiiculty is 3 circles and overlapping areas are not always possible exactly and compromises in the areas must be done.

Steve

Reply | Quote

I did read the old posts but completely missed your sample. I have 30 venns with only 2 circles. I tried to modify for 2 cicles but frankly got lost. Can you help me? thanks

Reply | Quote

Here is a modified version of Steve’s example. If you change the values in B2:B4, you may have to fiddle with the scale (min/max) of the chart axes.
If the solver doesn’t find a solution, enter a different guess in E23.

Reply | Quote

I see Hans modified my original one based on 3 circles for only 2.

This is an improved version. WIth only 2 circles the area of each is exact and solver is not needed to “massage” the areas and compromise. This version will have proportianate areas for the left and right and the put them the correct distance apart to have the overlap area match the area. (The areas are “filled” by adding hundreds of vertical lines to each area…)

The calculations are intensive since there are many cases. It is much easier to do a stacked bar or stacked column (which I have also included for comparison). These chats only require 3 values which can be directly determined from your values…

Steve

Reply | Quote

Great! I was too lazy (see post 544,543 in Scuttlebutt) to work out the exact solution.

I agree that a stacked bar or column chart is much easier.

Reply | Quote

I had already had much of it done when I originally played with it 2.5 years ago. The question then was for 3 circles which lead me to post the “solver solution” since there was generally no exact answer for all the areas (you can get total of each circle and the 2 circle overlap of each exact, I recall, but this setup defines the overlap of all 3 circles so it is not variable).

I cleaned up my working workbook, but I am not even sure if I need all the calcs, it was so long since I had created it and worked thru all the variants, I am not sure what all the calcs do anymore .

Steve

Reply | Quote

oh my goodness. This is TOOOO wonderful.

Is there a way it can be changed to keep them perfect circles? When i use

5,006,000
18,727,000
6,905,000

the circles come out round. When i use
8,749,000
5,544,000
3,162,000

they are oblong.

I’ve looked everywhere for a solution like this. I can’t believe all the calculations. I would love to do circles but may switched to stacked bars to keep it simple since i have to do 30 of them. Thank you so much.

Reply | Quote

The problem is the XY axis scales are notlined up correctly. You just need to expand the X-direction of the chart and possibly decrease the Y.

It might be possible with a formula to add points (that are not visible) to ensure that the axes expand as needed automatically to cover the same range, though this may not be perfect. I think it would require a macro to ensure that the XY axes are manually set make perfect grid squares.

Actually if you are going to use a macro, I think I would just use the calculations and have the macro draw the 2 overlapping circles (using the drawing tools) and have them partially transparent to show the overlap. I think this would be easier than trying to keep the chart “square”. The calculations for the center of the circles and the their radii is on the calc sheet. It would just require drawing and placing them on a sheet (and scaling them to fit in a particular size, and maybe center them).

It should not be too difficult. I don’t know if I will have time to look at it tonight, but if no one takes on the challenge I will try to sit down and work on it…

Steve

Reply | Quote

(Edited by sdckapr on 09-Jan-06 09:28. Added Corrected file. Fixed typo found by Sundog. Thank you.)

I finally had a chance to play with this.

I got rid of the calculation sheet and do everything with user-defined functions/subroutiones. These routines could be used independently of the procedure if desired.
The sheet has 3 data entry cells (B1:B3). D1:D5 has the info for the graphic labels.

The drawn object has a background (rectangle named “OverallArea”) with 5 textboxes (containing the info from D1:D5). It also contains a smaller rectangle name “VennArea” which the circles will fill up (it has no border so it is not seen). These are essentially “unchanged” by any of the code. The 2 circles (“AreaA” and “AreaB”) are deleted and recreated when B1:B3 is changed.

The routines are:
Worksheet_Change is in the sheet code. When a new value is entered into B1,B2, or B3 this runs and calls the sub “DrawVenn” to draw the venn Diagram

DrawVenn takes the Area of A (without , the overlap of A and B, and the area of B (without A). It also needs the names of the 2 areas (“AreaA” and “AreaB”) and the worksheet object where it will draw and from that draws 2 circles. You can change the colors of the circles here (I used vbBlue and vbRed, near the end of the code. In this procedure the size of the rectangle “VennArea” is determined and objects are scaled to fill the area. The procedure calls the function “GetDistance” and “DrawCircle”

GetDistance takes as arguments the Area of A (without ,the area of B (without A) and the overlap of A and B and calculates the distance the 2 circles need to be apart to have the given overlap. It interpolates the area from 5 different distances. It calls the function “OverlapArea”.

OverlapArea determines the overlap of 2 circles given the 2 radii and the distance between them

DrawCircle, given the center of the circle (X,Y coords), the radius, color, name and worksheet, will delete a previous one, then redraw at the proper size.

I hope this helps. If you need further assistance let us know.

Steve

Reply | Quote

You are too kind!

Reply | Quote

Steve,

Fantastic code!

But the formula for Total B should read =”Total B = “&TEXT(B2+B3,”#,##0”), not =”Total B = “&TEXT(B3+B3,”#,##0”).

Reply | Quote

Ooops.

Steve

Reply | Quote

When I enter

A (NOT 100,000
A and B 50,000
B (NOT A) 50,000

It seems like the right circle should be 66.7% (B=100,000) of the size of the left circle since A=150,000. The right circle is a lot larger than 67% of the left circle. How are the circle sizes calculated? thank you for the help.

Reply | Quote

This is another reason why you should use the stacked bars rather than circles, our minds are not good at determining the relative sizes based on the radii.

The numbers you give are used to define the area. Based on the area of A and B the radius of each circle is determined [radius = sqrt(Area/pi)]. Then the routine determines the distance the centers of the circles should be apart to have the overlap area equal to what you have given. Based on the radii of the circles, their distances apart they circles are drawn.

If you think they are incorrect, print it out and measure the radius or print out 3, cut them out to get each area and weigh them to convince yourself they are correct.

Steve
PS I printed out 3 copies and cut, area from each one, and weighed them

Reply | Quote