4-dimensional chart

[Paul]

Is it possible to have a bubble chart that uses 4
dimensions i.e. x-axis, y-axis, size, and color, where
color is also dynamically generated from the fourth column
of the data series. In other words without manually
editing the color of individual bubbles? If yes, some
sample VBA code would be much appreciated.

 

[Jon Peltier]

Paul -

You could adapt the technique in my Conditional Charting example
(http://peltiertech.com/Excel/Charts/ConditionalChart1.html) to either
show a bubble size or show a #N/A error in the appropriate column, based
on another variable's value. Your data table might look like this:

6 7 3 #N/A
7 6 #N/A 3
8 5 5 #N/A
9 4 #N/A 5
10 3 7 #N/A

where the first two columns are the X and Y values for Series 1 and 2,
column 3 has the bubble sizes for Series 1, and column 4 has the bubble
sizes for Series 2. These bubble sizes can be linked by formula to
another value which decides whether the bubble should be red (series 1)
or blue (series 2).

- Jon

 

[Paul]

Jon,

Thanks for the advise! I'm now able to have colored
bubbles based on the data series. However, I know have a
follow up. I borrowed the code from
(http://support.microsoft.com/default.aspx?scid=kb;en-
us;213750) to attach labels to bubbles (which means that
the label names are in the column A, then x-values in
column B, y-values in column C, series1 bubble size in
column D, again y-values in column E, series2 bubble size
in column E). Having used your suggestion for coloring
bubbles based on series I know have #NA values in my data
series, which I think is preventing the "label naming"
code from running properly i.e. when the code reaches the
first row where a #NA occurs, I get an error (Unable to
set the HasDataLabel property of the Point class). Any
ideas how to get around this issue? Is there another way
to attach labels to bubbles? BTW, the label naming code
worked when I had the bubble size data all in one column
(i.e. before I attempted to color the different series).

Thanks.

-----Original Message-----
Paul -

You could adapt the technique in my Conditional Charting example

(http://peltiertech.com/Excel/Charts/ConditionalChart1.html
) to either

show a bubble size or show a #N/A error in the appropriate column, based

 

[Tushar Mehta]

It is to your credit that you found out for yourself how to label
points in a Bubble chart.

Now, that you know how it works, it might be easier to use
Rob Bovey's XY Chartlabeler (www.appspro.com) or
John Walkenbach's Chart Tools (http://j-walk.com/ss/index.htm)

If you still want to use your own code, make the following untested
changes:

For Counter = 1 To Range(xVals).Cells.Count
If Not Application.WorksheetFunction.IsError( _
Range(xVals).Cells(Counter, 1).Offset(0, -1)) Then
With ActiveChart.SeriesCollection(1).Points(Counter)
.HasDataLabel = True
.DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
End With
End If
Next Counter

It might be easier to just insert a
On Error Resume Next
before the 'For Counter = 1 To...' loop in the original code. <g>







--
Regards,

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

Jon,

Thanks for the advise! I'm now able to have colored
bubbles based on the data series. However, I know have a
follow up. I borrowed the code from
(http://support.microsoft.com/default.aspx?scid=kb;en-
us;213750) to attach labels to bubbles (which means that
the label names are in the column A, then x-values in
column B, y-values in column C, series1 bubble size in
column D, again y-values in column E, series2 bubble size
in column E). Having used your suggestion for coloring
bubbles based on series I know have #NA values in my data
series, which I think is preventing the "label naming"
code from running properly i.e. when the code reaches the
first row where a #NA occurs, I get an error (Unable to
set the HasDataLabel property of the Point class). Any
ideas how to get around this issue? Is there another way
to attach labels to bubbles? BTW, the label naming code
worked when I had the bubble size data all in one column
(i.e. before I attempted to color the different series).

Thanks.

-----Original Message-----
Paul -

You could adapt the technique in my Conditional Charting example

(http://peltiertech.com/Excel/Charts/ConditionalChart1.html
) to either

 

[Guest]

Thanks for the suggestion. I had already tried JWalks's
add-an and it certainly works. However, it requires me to
set the labels for each series one at a time. The code (as
shown below) let's me label all bubble at the same time.
However, the "fun" continues. My data is set up, as
suggested by Jon, as follows:

LabelName x-value y-value SizeSeries1 y-value SizeSeries2

this way I can automatically set the data range (excluding
the LabelName column)and it gives me a bubble chart with
colored bubble by series. When I run the code shown below
to add labels, it only adds labels to the first series.
How do I edit the code so that labels are added to other
series as well?

I'm also wondering if a solution to this type of 4
dimensioal bubble chart (x,y,size,color, with labels) is
something that could be posted on one of the MVP sites. It
seems that it could be something that many could reference.

Thanks.

-----Original Message-----
It is to your credit that you found out for yourself how to label
points in a Bubble chart.

Now, that you know how it works, it might be easier to use
Rob Bovey's XY Chartlabeler (www.appspro.com) or
John Walkenbach's Chart Tools (http://j- walk.com/ss/index.htm)

If you still want to use your own code, make the following untested
changes:

For Counter = 1 To Range(xVals).Cells.Count
If Not Application.WorksheetFunction.IsError( _
Range(xVals).Cells(Counter, 1).Offset(0, -1)) Then
With ActiveChart.SeriesCollection(1).Points (Counter)
.HasDataLabel = True
.DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, - 1).Value
End With
End If
Next Counter

It might be easier to just insert a
On Error Resume Next
before the 'For Counter = 1 To...' loop in the original

--
Regards,

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

 

[Jon Peltier]

I have a few future topic areas to develop for my web site. Among them
are Gantt charts and Bubble charts. Several pages each, ranging from
basic simple charts, then getting into embellishments like labeling and
other formatting, as well as automation. Since this is a free time kind
of effort, it'll happen when it happens, but it'll happen.

Any other topic areas, let me know, and I'll add it to NEXT year's list <g>.

- Jon

 

[Jon Peltier]

To answer your request for code to label all series, if the labels are
the same for all series (one column left of the X values):

For Counter2 = 1 to ActiveChart.SeriesCollection.Count
For Counter = 1 To Range(xVals).Cells.Count
On Error Resume Next
With ActiveChart.SeriesCollection(Counter2).Points(Counter)
.HasDataLabel = True
.DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
End With
On Error Goto 0
Next Counter
Next Counter2

- Jon