Bubble Concept, Bars Instead
 

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

Jon Peltier has instructions for variable width charts:

http://peltiertech.com/Excel/Charts/VarWidthColumn.html

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

On Thu, 17 Feb 2005, in microsoft.public.excel.charting,
OLLIE said:

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

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?

--
Del Cotter
Thanks to the recent increase in UBE, I will soon be ignoring email
sent to . Please send your email to del2 instead.

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





-----Original Message-----
On Thu, 17 Feb 2005, in microsoft.public.excel.charting,
OLLIE said:

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

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?

--
Del Cotter
Thanks to the recent increase in UBE, I will soon be
ignoring email
sent to . Please send your email
to del2 instead.
.

On Fri, 18 Feb 2005, in microsoft.public.excel.charting,
OLLIE said:

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

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)

--
Del Cotter
Thanks to the recent increase in UBE, I will soon be ignoring email
sent to . Please send your email to del2 instead.

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

In article ,
says...
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





-----Original Message-----
On Thu, 17 Feb 2005, in microsoft.public.excel.charting,
OLLIE said:

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

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?

--
Del Cotter
Thanks to the recent increase in UBE, I will soon be
ignoring email
sent to . Please send your email
to del2 instead.
.