Bubble Charting Hell

[caladanbrood]

Hello!

I'm hoping that I can get a pointer in the right direction for understanding bubble charts. I am happy with manipulating pretty much any others, but bubble charts just don't work well in my brain for some reason.

What I am looking for in to create is this sort of chart, from Chandoo's article on pie chart alternatives (http://chandoo.org/wp/2009/01/16/excel-pie-chart/) :

It is exactly what I am looking for on a dashboard I am working on, but I just don't get how it works. This sounds kinda pathetic but I could really do with a simple idea about what table of data I need to have to make this work!

Attached is a table of data - I want to ideally show two "pie charts" on the same chart - one for the current year, another for the total.

I hope this makes sense. I just can't get my head around bubble charts!!

Thanks,
Jon

 

[Hui]

Do you mean like this ?

See attached file:

 

[caladanbrood]

Hmm, sort of. I was hoping to have them all starting from the bottom rather than from the middle, but it looks like this is beyond excel - the chart values vary, but the size of the biggest bubble each time does not, meaning that the equation to work out y value is beyond my rusty extrapolation skills.

I have hit a brick wall with bubble sizes though. It seems you cannot get bigger than a certain size regardless of the actual values. This is no use, so I will have to go for a radar chart instead, as suggested in the same article. Not quite as pretty, but considerably easier to work with!!

Thanks anyway

 

[NARAYANK991]

Hi ,

There is no reason why bubbles cannot be placed the way you have shown them ; see the attached file.

Narayan

 

[caladanbrood]

Thanks guys - I have hit a brick wall with this dashboard and need to start from scratch, because it will be hosted on a SharePoint site, so data validation is not possible. I have to explore the weird and wonderful world of pivot tables and slicers instead... eek.

 

[Luke M]

@Narayan
I'm sure it's obvious, but is there an equation you used to get the bubbles lined up? I can see that as we change the size, we would need to re-adjust the y-point, but I can't figure out the right correlation.

 

[NARAYANK991]

Hi Luke ,

Actually I have not tried to do anything but show the OP that he could get the bubbles the way he wanted , and that they need not be concentric circles.

However , since he seems to have found out that this is not the correct route , I think there is no point in pursuing this matter.

I think there should be a mathematical way to align the bubble centres in a vertical line.

Narayan

 

[caladanbrood]

@Narayan
I'm sure it's obvious, but is there an equation you used to get the bubbles lined up? I can see that as we change the size, we would need to re-adjust the y-point, but I can't figure out the right correlation.

This is one of the original problems I was having - because of the way the bubble chart forces a specific size on the bubbles, it is incredibly difficult to work out the equation for the y-values. I have an engineering degree and a further maths a-level and I can't work out the equation... otherwise I wouldn't have had to ask :D

 

[Luke M]

Annoyances I've found:
To have any chance at keeping it close, need bubbles plotted using "Size represents width", otherwise it's way more complicated.
Length of axis affects size.
Length of smallest bubble and largest bubble affect size, it is not a true width (MEGA ANNOYING)

My take away...bubble charts are terrible. I'll find a different chart.