Multi-Pie/Bubble Charting

[Shawnews]

I've just changed jobs within my company and have inherited a "map" of
pie-charts that will need to be updated semi-frequently. Here is what has
been done.....

The raw data looks something like


Area X-coord Y-coord CompanyA Company B CompanyC
Total
prairie 3 5 10% 20%
70% 2500
coast 2 1 50% 10%
40% 1000
mnts 4 7 20% 20%
60% 500
etc.....


What has previously been done is that my predecessor has created a pie-chart
for each area (i.e. a pie-chart for prairie, coast, mountains, etc...) that
shows the relative split in ownership between Company A,B,C, (actually more
than 3 but....). He then creates a bubble chart using the x-y coordinates
and the "Total" amount for each area so that he has a bubble (emtpy circle)
that is sized relative to the total-amount. He then overlays the bubble
chart over a bitmap image (that is a scanned map) so that the bubbles are at
the right places with respect to the bitmap image. Then he manually resizes
each of the piecharts and drags them so that they are the same size as their
respective bubbles and are centered over the correct x-y coordinate on the
map.

Quite ingenious I think, and it is a snap to change the company splits since
the pie-charts are still linked to the raw data. However, changes to the
total-amount means a lot of resizing of the bubbles and all this is manually
done.

Now...my question.... basically I'm just looking for suggestions of a better
way to accomplish the desired efffect....which is a "map" of pie-charts that
show the relative company splits with the size of the pie-chart circle being
relative to the total amount and all of this overlaid on an image.

I *think* my plan of attack will be to write some VBA code that will be to
scan down through the total amounts and the x-y coordinates of each area and
have the VBA code automatically resize and reposition the multiple
pie-charts. I'm fairly comfortable that I can do this (eventually) myself,
but obviously am looking for any shortcuts or better ideas... Even other
software that would make this easy would be of interest.

Thanks in advance for any thoughts/tips.

 

[Shawnews]

Ooops, looks like the line wrap got me... here is the sample data in more
compressed format

Area X Y CompanyA Company B CompanyC Total
prairie 3 5 10% 20% 70% 2500
coast 2 1 50% 10% 40% 1000
mnts 4 7 20% 20% 60% 500
etc.....

Again, thanks in advance,

Gary

 

[Jon Peltier]

Gary -

You can make it a lot easier on yourself. You can use any graphic as a custom chart marker, if
you copy the graphic, select the series, and paste using Ctrl-V. If you select just one point
first, only the one point gets the custom marker; if you select the entire series, they all get
it. If you paste a custom marker onto a bubble chart, the marker is sized according to the
bubble size variable.

So when you make the pie chart, make the entire chart square, and format the plot area and chart
area so they have no border and no fill. Make the plot area as large as the chart area, so the
pie is as large as will fit. When the pie is the way you want it, copy it, select the series in
the bubble chart, then select the point (two single clicks), and press Ctrl-V. Repeat for the
other pies.

You can do it with VBA, but at least you know a much quicker way.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Shawnews]

Jon,

First off thanks for the tip. I think that is going to be beneficial. One
point of clarification/question though.... Am I right in assuming that you
can only do this by pasting in a static bitmap image/graphic as the custom
marker. I've made a quick test with a spreadsheet and this certainly seems
to be the case. I needed to copy-paste somewhere else to convert the
individual pie-charts to static images and then paste them back to the
bubble map. Apparently you can't paste a dynamic pie-chart as the custom
marker. Is this correct?

Even so, I believe that I'll be updating the total-amount data a lot more
often than the company ownership amounts will change. As such, if I don't
go to the VBA route eventually I think your suggested procedure will be much
nicer. It'll be a trade-off of going from a setup where updating the
pie-splits can occur automagically to where updating the bubble sizes occurs
automagically (in both cases, it would seem, that I've got a bit of manual
manipulation to update the other part)

Basically, I'm just writing again to make sure I've got this right (about
having to use static images) for your suggestion to work. Thanks again so
much!

Gary

 

[Tushar Mehta]

Yes, those images have to be static.

Also, AFAIK there is no way to really make the plot area equal to the
chart area. There will always be a small 'margin' that XL maintains.
So, if you set the chart and plot areas to none, then copy picture +
paste, the resulting image of the pie in the bubble will be *slightly
smaller* that the actual bubble. Depending on how accurately you want
the bubbles to reflect the associated quantity this might or might not
be an issue.

Finally, automating that process should not be too difficult. Of
course, the operative phrase is *should not* {vbg}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Tushar -

True, there is a slight margin, but as I recall from some tests I ran of this method some time
ago, it was not very significant, and it was the same percentage of each bubble's pie. For 99%
of the end users, it will not be an issue.

- Jon

 

[Shawnews]

Thanks Guys,

Some very good points. I was aware of the bit of margin issue and the
particular thing I'm working on isn't that precise...just meant to give a
map-at-a-glance sort of view that shows which companies have a big state in
the big areas.

Not sure how quickly I'll get to the VBA...could be tomorrow could be next
year. Will just have to see how the new job goes.

Thanks a bunch!

Gary