Hello,

I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points

a 1 1.3 5.4
a 2 2.4 2.3
b 1 4.5 3.2
b 3 8.9 6.7

and so on. Each combination of items from L and C is present.
Moreover, for each (l,c) tuple, I have multiple data points.

I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

I can use series and the vary color by point, but then points with the
same L-value will still get distinct colors.

Any pointers on how to do this?

At the end of the following article you'll find a couple macros that adjust
point colors based on category label. Instead of category label, you could
look up values in the first two columns, and adjust color and marker style.

http://pubs.logicalexpressions.com/P...cle.asp?ID=390

An alternative is to set up conditional series, where you have as many
series as possible combinations of L and C, with each formatted individually
by series, rather than by point. Here is an explanation of the approach:

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

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


"itkovian" wrote in message
ps.com...
Hello,

I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points

a 1 1.3 5.4
a 2 2.4 2.3
b 1 4.5 3.2
b 3 8.9 6.7

and so on. Each combination of items from L and C is present.
Moreover, for each (l,c) tuple, I have multiple data points.

I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

I can use series and the vary color by point, but then points with the
same L-value will still get distinct colors.

Any pointers on how to do this?

One, rather intricate, but again repeatedly proven subroutine of mine follows.

Of course, you have first to create all the series by markers. Inside of
them, however, you will only declare marker colors by changing the font color
in the cells with appropriate y-values. You could make it by hand or by
macro; conditional formatting is of no help in this case. Of course some
included refinements are of no use for you at present. An all-embracing
subroutine with automatic selection of marker shapes is also thinkable, but
will require more strategic thinking.

Regards

Petr

Sub MarkerColor()
'2003

'Sub changes the colors of individual markers in the selected series
'of x-y chart into the colors of the fonts of parent cell v a l u e s.
'It keeps the marker interior the way as defined for the whole series:
'either empty, or of uniform color. If, however, the background
'of any value cell is light gray, the interior of corresponding
'marker changes into its opposite, i.e. if the series has been declared
'as marker full then such a marker turns to empty and vice versa.
'If the cell background is medium gray, the marker disappears.
'The aim is to identify individual markers or their groups within
'one complete series on a x-y chart without having to decompose
'the parent range into subranges.

Dim SP As Points, W As Range
Dim ErrMsg As String, SPF As String, Rng As String
Dim I As Long, N As Long, PosComma As Long, ICI As Long, FCI As Long
Dim MarkersAreEmpty As Boolean

Const Comma = ",", LightGray = 15, MediumGray = 48

ErrMsg = "No series has been selected"
On Error GoTo ErrExit
Set SP = Selection.Points
MarkersAreEmpty = Selection.MarkerBackgroundColorIndex = xlNone
N = SP.Count
SPF = SP.Parent.Formula
I = 3
Do
I = I + 1
Rng$ = Right(SPF, I)
Loop Until Left(Rng, 1) = "!"
Rng = Right(Rng, Len(Rng) - 1)
PosComma = Application.WorksheetFunction.Search(Comma, Rng)
Rng = Left(Rng, PosComma - 1)
Set W = Range(Rng)
For I = 1 To N
FCI = W.Cells(I).Font.ColorIndex
On Error GoTo Skip
SP(I).MarkerForegroundColorIndex = FCI
ICI = W.Cells(I).Interior.ColorIndex
If ICI = LightGray Then
If MarkersAreEmpty Then
SP(I).MarkerBackgroundColorIndex = FCI
Else
SP(I).MarkerBackgroundColorIndex = xlNone
End If
ElseIf ICI = MediumGray Then
SP(I).MarkerForegroundColorIndex = xlNone
SP(I).MarkerBackgroundColorIndex = xlNone
Else
If Not MarkersAreEmpty Then
SP(I).MarkerBackgroundColorIndex = FCI
Else
SP(I).MarkerBackgroundColorIndex = xlNone
End If
End If
Skip:
Next I
Resume Next
Exit Sub

ErrExit:
MsgBox ErrMsg$
On Error GoTo 0
End Sub


--
Petr Bezucha


"itkovian" wrote:

Hello,

I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points

a 1 1.3 5.4
a 2 2.4 2.3
b 1 4.5 3.2
b 3 8.9 6.7

and so on. Each combination of items from L and C is present.
Moreover, for each (l,c) tuple, I have multiple data points.

I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

I can use series and the vary color by point, but then points with the
same L-value will still get distinct colors.

Any pointers on how to do this?

On Tue, 18 Sep 2007, in microsoft.public.excel.charting,
itkovian said:
I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points

I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

It seems to me that you are coding the codes. Why would you create a
code 1, 2, 3, etc., and then represent them by triangle, square, etc.?

If you use one of the available add-ins for custom labels, you could use
the labels 1,2,3 directly on your graph without requiring the viewer to
perform an act of translation. Position the labels centre,centre and
format the scatter series to have no marker. Then you can represent a,
b, c, by the font colours of the labels, having arranged your data so
that each a, each b, and so on represent a distinct range.

Using custom labels can be a very powerful intuitive graphing technique,
for instance when presenting a scatter graph of two variables across
fifty US states, using the two-capital-letter abbreviations of the
states as the markers. Here's an example using TV stations in two
different colours:

http://junkcharts.typepad.com/junk_c...nd_median.html

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

On Sep 18, 7:53 pm, Del Cotter wrote:
On Tue, 18 Sep 2007, in microsoft.public.excel.charting,
itkovian said:

I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points
I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

It seems to me that you are coding the codes. Why would you create a
code 1, 2, 3, etc., and then represent them by triangle, square, etc.?


Because in reality the labels als much longer :-) And visual
information such as colour, shape is much easier to understand when
looking at a graph. We're trained to do it, letter codes are much more
difficult.

And visual information such as colour, shape is much easier to
understand when looking at a graph.

Color maybe, shape not so much. I would think a colored label would be as
effective as a colored marker, as long as the label were kept to a character
or two in length.

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


"itkovian" wrote in message
ups.com...
On Sep 18, 7:53 pm, Del Cotter wrote:
On Tue, 18 Sep 2007, in microsoft.public.excel.charting,
itkovian said:

I have a set of data I wish to graph in a scatterplot. Each data point
has two identification codes, coming from two separate groups, e.g., L
= {a,b,c,d,e} and C = {1,2,3,4}. So you might have points
I wish to use both colour and shape to identify the data points, e.g.
all a's should be yellow, all b's red, etc. and all 1's should be a
triangle, all 2's a square etc.

It seems to me that you are coding the codes. Why would you create a
code 1, 2, 3, etc., and then represent them by triangle, square, etc.?


Because in reality the labels als much longer :-) And visual
information such as colour, shape is much easier to understand when
looking at a graph. We're trained to do it, letter codes are much more
difficult.