Bubble Concept, Bars Instead

  • Thread starter Thread starter OLLIE
  • Start date Start date
O

OLLIE

Is there a way of charting in a way very similar to a
bubble, but rather than the bubble changing size, a bar or
a post does (taller for larger)?


TIA
 
Is there a way of charting in a way very similar to a
bubble, but rather than the bubble changing size, a bar or
a post does (taller for larger)?
Click to expand...

You can replace the bubble with a graphic, in your case a rectangle, and
the size of the rectangle can be proportional either in width & height
or in area to the value being displayed. But I don't know how to make
the graphic have a constant width and variable height.

Alternatively, as Debbie says, you can design one of Jon Peltier's
variable-width charts with stacked bars, and make the lower bar
invisible, leaving the upper bar floating in air; like a Gantt chart,
but vertical. I wouldn't be surprised if you could contrive to have
clusters of N bars hanging in air like that, each cluster representing N
dimensions!

Does anybody know where I might find pie-bubble charts, with each
"bubble" being a pie chart?
 
Thanks for the reply... I think I need to be more
specific:

I have a XY Scatter that I would like to use as a Bubble
Chart to show a third quality (e.g bubble size = Sales
Growth). The problem is two fold:

1. Too many bubbles
2. The number can be negative

My thinking is I could replace the bubble with a bar whose
length varies accordingly. It would almost be 3D in that
for negative numbers, it could grow, but downward...

Hope this isn't just more confusion... Thanks
 
I have a XY Scatter that I would like to use as a Bubble
Chart to show a third quality (e.g bubble size = Sales
Growth). The problem is two fold:

1. Too many bubbles
2. The number can be negative
Click to expand...

What if you made the bubbles smaller, and arranged somehow for negative
numbers to be represented by bubbles of a different colour?

Or, have you considered using the "error bars" facility to mimic the
effect you want? Bubble charts can do it too, giving each data point
theoretically seven degrees of freedom (X, Y, bubble size, x-error plus
and minus, y-error plus and minus) There's a limit to how thick I can
make the error bars in Excel 2000, but they certainly provide
information at a glance.

(I played around with 3D bar charts in Excel 95, but they don't really
do what you want. If only they had 3D scatter column graphs: z-height
columns scattered on an x-y base)
 
Another option to consider is to change the size of each marker, which
can be between 2 and 72. Of course, there is no native way to vary the
size based on a cell value. To do this by hand you will have to go
marker-by-marker. Alternatively, you will need a VBA macro. A
skeletal example is below. It sets the marker size of the first series
of the first chart embedded in the active sheet to the contents of
column H starting with H1. In addition to all that information being
hard coded, it also has zero error handling.

Sub Macro1()
Dim aCell As Range, i As Integer
i = 1
With ActiveSheet
For Each aCell In Range(.Range("H1"), .Range("h1").End(xlDown))
.ChartObjects(1).Chart.SeriesCollection(1).Points(i) _
.MarkerSize = aCell.Value
i = i + 1
Next aCell
End With
End Sub
--
Regards,

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