Excel 'bubble' chart where colors are used instead of bubble size

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulmichael - 25 Sep 2007 18:10 GMT

I am trying to create plots from 3-D data where the first two columns
determine the X & Y coordinate of the plotted symbol and the third column (Z)
gives the value which is represented by the color of the symbol. This is very
similar to the bubble plot where the size of the bubble represents the value,
however I cannot find any plot type in the menue which uses colors instead.
Is there a way to create a custom plot type to achieve this goal?

Reply to this Message

Jon Peltier - 25 Sep 2007 21:13 GMT

It's not built in, but it's also not hard to build. I assume you have a
discrete number of colors, and aren't trying to gradually change color with
value (which is known not to be an effective way to display variable data).
You could construct a conditional chart:

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

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

>I am trying to create plots from 3-D data where the first two columns
> determine the X & Y coordinate of the plotted symbol and the third column
[quoted text clipped - 6 lines]
> instead.
> Is there a way to create a custom plot type to achieve this goal?

Reply to this Message

Anders Løken - 19 Nov 2007 23:57 GMT

Hi, and thanks for this post. Very helpful. I have one question however: When
I create a conditional bubble chart the bubbles still have different sizes.
To solve this i used a scatter chart instead. This works fine, but it doesnt
look as good is I want it to. I want the color of adjacent data points to
smoothly overlap. Is there any way to do this? I guess I could make the
markers really large? How about manipulating the data before making a chart?
(Interpolating between data points)

> It's not built in, but it's also not hard to build. I assume you have a
> discrete number of colors, and aren't trying to gradually change color with
[quoted text clipped - 20 lines]
> > instead.
> > Is there a way to create a custom plot type to achieve this goal?

Reply to this Message

krislikesmath - 05 Dec 2007 15:33 GMT

On Nov 19, 6:57 pm, Anders LÃ¸ken <Anders
Løk...@discussions.microsoft.com> wrote:

> Hi, and thanks for this post. Very helpful. I have one question however: When
> I create a conditional bubble chart the bubbles still have different sizes.
[quoted text clipped - 28 lines]
> > > instead.
> > > Is there a way to create acustomplot type to achieve this goal?

Here's some code for when I did a similar project. People who were
negative got a "red bubble" and people who were positive got a dark
gray color for a bubble... For the life of me I can't find the
original spreadsheet, so good luck discovering what the ranges looked
like.

Sub bubble3()

Dim myChtObj As ChartObject
Dim colornum As Long
colornum = 1
' color 3 is red
' color 1 is black

Dim rowrange As Range

Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.ChartType = xlXYScatter
myChtObj.Chart.SetSourceData
Source:=Sheets("Sheet1").Range("A3:C6")
myChtObj.Chart.ChartType = xlBubble
myChtObj.Select
' may need to uncomment this for testing purposes, this section just
deletes the previous 'data.
' With ActiveChart
' Do Until .SeriesCollection.Count = 0
' .SeriesCollection(1).Delete
' Loop
' End With
' myChtObj.Chart.SeriesCollection(1).Size
Dim x As Long
x = 2
' x is row var
'For x = 2 To 51 Step 1 ''51 values to enter in
' With ActiveChart.SeriesCollection.NewSeries
' .Name = ActiveSheet.Range(R & x & C1)
' .Name = "=sheet1!R" & x & "C1"
' .Values = "=sheet1R" & x & "C2"
' .XValues = "=sheet1R" & x & "C3"
' .Values = ActiveSheet.Range("B2")
' .XValues = ActiveSheet.Range("C2")
'
' .Interior.ColorIndex = 3
' .BubbleSizes = "=sheet1!R" & x & "C4"
' End With
'Next

''Changes Color Based on a value of a range??
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("C2")
.XValues = ActiveSheet.Range("B2")
.BubbleSizes = "=sheet1!R2C4"
If ActiveSheet.Range("F2") > 0 Then
colornum = 16
Else
colornum = 3
End If
.Interior.ColorIndex = colornum
End With

''Makes the bubble
Set rowrange = ActiveSheet.Range("A1")
For x = 2 To 52
With ActiveChart.SeriesCollection.NewSeries
.Values = rowrange.Offset(x, 2)
.XValues = rowrange.Offset(x, 1)
.BubbleSizes = "=sheet1!R" & (x + 1) & "C4"
If rowrange.Offset(x, 4) > 0 Then
colornum = 16 ''dark gray
Else
colornum = 3 '' red
End If
.Interior.ColorIndex = colornum
End With

Next

End Sub

Reply to this Message