Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Matrix
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 |
#2
|
|||
|
|||
If it were me, I'd create an XY scatter using Difficulty as the X and Value
as the Y. I bet you knew that :^D I'd then use Rob Bovey's XY Chart Labeler ( http://www.appspro.com/Utilities/ChartLabeler.htm ) to add the labels to the series Have a great day! Barb Reinhardt "Basil" wrote in message ... 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 |
#3
|
|||
|
|||
Basil -
You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
#4
|
|||
|
|||
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 "Jon Peltier" wrote: Basil - You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
#5
|
|||
|
|||
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. ---- Regards, John Mansfield http://www.pdbook.com "Basil" wrote: 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 "Jon Peltier" wrote: Basil - You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
#6
|
|||
|
|||
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/ _______ Basil wrote: 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 "Jon Peltier" wrote: Basil - You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
#7
|
|||
|
|||
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 "Jon Peltier" wrote: 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/ _______ Basil wrote: 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 "Jon Peltier" wrote: Basil - You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
#8
|
|||
|
|||
Baz -
Thanks for the feedback. We're just all glad to help. - Jon Basil wrote: 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 "Jon Peltier" wrote: 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/ _______ Basil wrote: 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 "Jon Peltier" wrote: Basil - You can build a quad chart using this technique: http://peltiertech.com/Excel/Charts/...ackground.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Basil wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a matrix from columns | Excel Discussion (Misc queries) | |||
How do I print a file holder label from Excel to a dox matrix pri. | Excel Discussion (Misc queries) | |||
Finding Values in a "Matrix" | Excel Discussion (Misc queries) | |||
entering matrix formula | Excel Worksheet Functions | |||
Matrix and determinant | Excel Worksheet Functions |