Matrix

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya,

I need to create a chart which is a matrix of four 'boxes' (a bit like the
BCG 'Cash cow, star, etc matrix if you know it). But am having real trouble.

What I need is this (and I'm going for the simplest method here - I ignored
the four 'boxes' and just tried to get the data as I want on the chart):

Data source:

Project Name Value(1-10) Difficulty(1-10)
Proj1 5 8
Proj2 8 10
Proj3 1 8
Proj4 3 2
Proj5 6 4

Chart:

Y-Axis: Value
X-Axis: Difficulty
Label next to each point: Project Name

So, with this graph there would only actually be 5 points - 1 for each
project. And each point would have the project name next to it.

I'll make a crude effort at drawing it:

(val)
10 |
| X proj2
| X proj5
| X proj1
| X proj4
|
1 |_____________________X proj3__
1 10 (diff)

I had very moderate success with 'bubbles' - but hit a dead end.
Anyone know how I can achieve this??

Many thanks,

Basil
 
Thank you so much both of you, these are both absolutely fabulous and combine
perfectly for exactly what I was after!

There is just the 1 more thing I am struggling with:
I want to make the chart look at (and adjust to) a dynamic range.
Since it is not a simple case of a chart looking at a rectangular
datasource, I am struggling.

With Rob Bovey's XY Chart labeler, I also have the problem that although it
does accept a named range, if (as in my instance) the named range is a
varying off-set formula - when I add to the range having already added the
labels, it will not re-asses the named range to add the additional row.

Does that make sense?

Any ideas?

Thanks again, your tips have been magic.

Basil
 
Basil,

If you are familiar with VBA and named ranges, I think you can build a chart
that meets your requirements if I'm interpretting your needs correctly.

For example, assume the label "Name" is in cell A1 and that your initial XY
data is set up like this:

Name Value Difficulty
Proj1 1 1
Proj2 2 2
Proj3 3 3
Proj4 4 4
Proj5 5 5
Proj6 6 6

Add a column D with cell references to column C. In a formula view, the
data set will now look like this:

Name Value Difficulty Labels
Proj1 1 1 =C1
Proj2 2 2 =C2
Proj3 3 3 =C3
Proj4 4 4 =C4
Proj5 5 5 =C5
Proj6 6 6 =C6

Copy the =C# reference all the way down the column until you hit cell C20.

Go to the standard toolbar and hit INSERT -> NAME -> DEFINE. Add this
formula and call it "DataLabels":

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$20),1)

Add this formula and call it "XAxis":

=OFFSET(DataLabels,0,1)

Add this formula and call it "YAxis":

=OFFSET(DataLabels,0,3)

Now, click once on your XY chart. In you standard toolbar hit CHART ->
SOURCE DATA - SERIES TAB.

Assuming your workbook is named "XYAuto.xls", enter the following formula in
the X Values box:

=XYAuto.xls!XAxis

Enter this formula in the Y Values box:

=XYAuto.xls!YAxis

Finally, assuming your chart appears in "Sheet1" of the "AutoXY.xls" file,
enter this VBA code into the Sheet1 code module:

Private Sub Worksheet_Calculate()
For Each cell In Range("D2:D20")
Fmt = """" & cell.Offset(0, -3).Value & """"
cell.NumberFormat = Fmt
Next cell
End Sub

The end result is that the XY chart will automatically update with new
values as they are entered into the range A2:D20. The VBA code formats the
values in the Labels column to be the same as those values entered into the
Name column.

Since this procedure is long, I'll post an example workbook to the downloads
section of my site shortly.
 
Basil -

You need to have a worksheet range that holds the values of the defined
range of labels. If the size of the dynamic range is unbounded, this
will be an issue, but if you know it will always be less than, say, 50
items, select an unobtrusive range, F1:F50 for example, and enter this
array formula

=MyDynamicLabels

and hole CTRL+SHIFT while pressing Enter. If you do this right, Excle
puts the formula within curly braces:

{=MyDynamicLabels}

Then fill the data range with dummy values (zeros), make a series in the
chart with 50 points, and use Rob's Labeler to use the worksheet range
F1:F50 for its labels. Clear out all the dummy values from the data
range, and proceed. Each point in the series will remember which cell
contains its label, even if the dynamic range causes fewer points to
appear. When it reappears, it will remember the link.

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

Perfect - I didn't find the need to have the array formula with {}. It
worked fine without. Very quick and smart! Doesn't even require recipients of
the workbook to have the Bovey add-in to get it to work!
The quadrant was absolutely spot on too - so simple and effective!

John M:
Your solution worked a treat (although with a large range it can take a
while for the VBA to run on every sheet calc and also putting the code on
worksheet_calc can be a bit inconsistent in working - although I found a work
around).
I thought your site was great.

Thanks to all who responded - I now have a variety of techniques that all
work excellently!
I'll get on to the programming or formulas bits to see if I can return the
help to another lost soul!

Many thanks,

Baz
 
Back
Top