Looking for Charting Suggestions

Topic

New Reply

Have an odd data set. Not sure how to chart it the way I want. Open to suggestions.

One variable is ordinal (first, second, third). I’d like that on the horizontal axis.

One variable is cardinal/ratio (0 to 10). I’d like that on the vertical axis.

No problem so far, right? I am thinking about an xy/scatter chart.

But, I have observations on units. Call them A, B, C.

So on my first measurement, I might get 2,3,4 for A,B,C. Then on my second measurement I might get 6,4,5 for A,B,C. For my third, I might get 8,8,8 for A,B,C.

What I would like to do is de-emphasize the points, and emphasize the trend by connecting them with line segments. So, for A, I want a line that goes from 2 to 6 to 8. Then I want a separate line that goes from 3 to 4 to 8 for B, and so on.

My problem is that I have many units that I make observations on (typically 100-200). Honestly, I’d probably like them all shown with the same color/symbol so that no particular one stands out.

This is a very simple thing to do with a small data set (see that attachment). My question is how I should set it up for a large number of observations so that my hand editing of the options on the chart is minimized?

Reply | Quote

Replies

Oops. Don’t see the attachment. Here it is.

Reply | Quote

boobounder,

Here is one possible solution. Set up your matrix in the following manner: Observation units across the top (A, B, C,….) and Observations down column A (1,2,3,4,…)

45946-Boo2

Enter your observations across for each observation. The code detects that that when the observation is complete with values in each column, it will automatically add the series to the chart and display the graph of the observation. There is no limit to the number of series but to what is visually acceptable. Clicking in column B will toggle a check mark that will make the series visible or not. When a new series is added, it will be visible by default.

45947-Boo3

The code assumes a line graph has been placed on the sheet with the name Chart 1.

In the worksheet module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Record As Long
Record = Target.row
If Not Intersect(Target, Range(“C2:G11”)) Is Nothing Then
    For Each cell In Range(“C” & Record & “:G” & Record)
        If cell = “” Then Exit Sub
    Next cell
    Range(“B” & Record) = “a”
    Add_Series Record
End If
[a1].Select
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Record As Long
Record = Target.row
If Not Intersect(Target, Range(“B2:B11”)) Is Nothing Then
    For Each cell In Range(“C” & Record & “:G” & Record)
        If cell = “” Then Exit Sub
    Next cell
    If Target = “a” Then
        Target = “”
        SeriesVisibility False, Record
    Else:
        Target = “a”
        SeriesVisibility True, Record
    End If
    [a1].Select
End If

End Sub

In a standard module:

Code:

Sub Add_Series(row As Long)
ActiveSheet.ChartObjects(“Chart 1”).Activate
With ActiveChart
    .SeriesCollection.NewSeries
    x = .SeriesCollection.Count
    .SeriesCollection(x).Name = “=Sheet1!$A$” & row
    .SeriesCollection(x).Values = “=sheet1!C$” & row & “:$G$” & row
End With
End Sub

Public Sub SeriesVisibility(status As Boolean, ByVal num As Integer)
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.SeriesCollection(num – 1).Format.Line.Visible = status ‘msoTrue
End Sub

Reply | Quote

I just realized that you wanted to graph by values for the units not the observation. Making adjustments

Reply | Quote

Here is one possible solution. Set up your matrix in the following manner: Observation units across the top (A, B, C,….) and Observations down column A (1,2,3,4,…). (see image)

Two graphs are displayed:
Observation– graphs across the row
Units– graphs down the columns

Start adding data across observations.

OBSERVATION GRAPH (top)(graph rows):
Will display when all columns for an observation are filled. Complete a row before completing rows beneath it

UNIT GRAPH (bottom)(graph columns):
Will show graph when column as more than one value.

TOGGLE VIEW:
Click in row 2 or col B to toggle graph visibility.

RESET BUTTON:
Clears both graph series and observation data.

45952-graph

Reply | Quote

Thanks Maudibe.

This is a work in progress. I’m not sure I’m yet asking the right question to get the answer that I need.

What I want is your “Units (graph down)” chart.

But what I really want to automate is adding series to the graphs. So, if I add a 6th column of observations in H, I want a 6th line segment to show up in that chart. And then a 7th, and an 8th, and so on.

Reply | Quote

That can be done with minor adjustments to the code. Working on it.

Reply | Quote

Here is the amended code that will add new series for both Observations (rows) and Units (columns) as the data is added. Like the previous version, the visibility of the series lines can be toggled by clicking the check marks for that series. The graph has been set up to accommodate 10 observations and 26 units however, the code can easily be modified to as many rows and columns as you like.

Maud

45967-autochart1

Reply | Quote

Swamped. Have not forgotten this. May get to look at it today.

Reply | Quote

Boo,

To get a more dramatic effect, right click each graph > Select Data > Hidden and Empty Cells > Option button: Connect data points with line > OK > OK.

A couple of minor adjustments need to be made regarding toggling the lines off/on
Maud

Reply | Quote

Part of me is thankful that you’re helping out.

But part of me is concerned that if I knew how much VBA was underlying this concept, that I would not have been interested in programming it.

I can keep asking for improvements, but I’m not sure how interested you are.

1) The normal data entry flow is different from Excel, where you tap enter and you go one cell down or one cell over.
2) It does not support (well) cutting and pasting of data into the main array. This is a big deal for me: this data is generated in heaps rather than singletons.
3) Your checkmarks are an interesting idea, but it doesn’t populate out in a working order into new series.
4) I would love a macro that toggles all series from the Excel defaults, to the same look: thin black lines, with no symbols for the observations.

Reply | Quote

working on it!

Reply | Quote

Boo,

Here is the code modified to accept a batch of pasted values into the matrix. In the image, I copied range AG2:AH6 and pasted to various areas. Any values that fall outside the grid are ignored. See AA10:AB13. This can be changed easily by changing the matrix size. The graphs will add series and points when values are pasted. The lines are uniform thin black lines and the checkmarks to toggle the series visibility have been expanded across to column AB. With same color lines and no labels (legend), toggling the series lines off and on readily identifies the graph for an observation or a unit.

Note: When pasting data into the grid, paste by values so the grid formatting is not disrupted.

HTH,
Maud

45986-autochart2

Reply | Quote

Maud:

Yes, I think this does it. I am learning a lot from your VBA.

I have a couple of minor questions.

1) You remark that I can resize the A1.AB12 array. How? Where? Are the rows and columns defined in your VBA somewhere? I presume I should know how much data I will cut and paste into there before I do this.
2) Does the number of categorical variables along the axes of the charts depend on the number of rows in that array? So, if I add or delete rows from the array, will it automatically add or delete categorical variables to those axes?
3) FWIW: I am warming up to your “Observations” chart, now that it works better with my vision. I still prefer the “Units” though. For the “Observations” chart, it might make more sense for me to have the lines be different colors. How do I change your VBA back so only that chart is colored by default?
4) How do I change the VBA to set the maximum for the values axes to 10, rather than having Excel figure out a plausible maximum?
5) How do I turn off the gridlines in your VBA?

Reply | Quote

Hi boobounder

If I were an astrophysicist, it seems to me your observations A, B, and C for each measurement could be likened to the xyz coordinates of an asteroid in a solar system ‘cube’ of 10x10x10 units.
For 200 measurements of A,B, C you get 200 asteroids, which can be shown ‘floating’ in a 3-D cube.
If you have multiple similar measurements, (e.g. two of 5,5,5 ) then these would ‘clump together i.e. making a ‘bigger asteroid’, If you had millions of measurements, these ‘clumping values could create ‘planets’. A 3-D chart of these ‘asteroid’ data points could show as an ‘orbital belt’ etc etc. There are 3-D charting tools available which will allow you to rotate the 3-D cube in any direction to examine and analyse your data.

If I were a baker, your observations could be translated into the locations of sultanas in a fruit cake. I could then use a 3-D chart of this cake to help me select the best ‘slice’ of cake I could make e.g. with the most sultanas.

If I were..

OK maybe you’d better stick with maud’s excellent charting samples!

zeddy

Reply | Quote

Zeddy:

TMI coming …

1) I will end up with more than 3 dimensions (4 to 7 actually).
2) I have thought about “clumping” for this data. But, I wonder if the audience would get that. In practice I think jittering the raw data will work better for my audience.
3) Yes, there is probably better software out there for this. But I wanted to keep it in Excel because users find that more accessible. My personal preference is not to use VBA at all; I don’t like the philosophy of having a user friendly worksheet on the surface with supplemental code in this layer between the designers and the users. For my part, maybe it’s old school, but I’d normally kick this sort of thing out of a mainline statistical package, and then load output into Excel to make a spiffier chart.
4) I teach (low level) Excel usage in a business school (we are not big enough to support more advanced classes, so my VBA usage is pretty basic). I would not necessarily use this program in class. But stuff like this that I can put out in front of students is a great thing because it generates those how-did-you-do-that sort of questions. It’s also cool because I have several of these where I can say, I didn’t really know how to do this on my own, but there’s this thing called the internets on your phone, and if you google this on bing, there’s people out there like you and Maud who will help. Playing up my inadequacy helps them recognize that they shouldn’t be intimidated by all this.

FWIW: This is actually quiz data on topics that students are learning. With each attempt, the group average should go up, but the group’s variance goes down. But there’s a lot of individual variation due to student guessing. And, I still want each student to be able to pick themselves out of the mix. The audience will be a combination of students trying to spot themselves in the mix, and administrators who will glance at the overall mix and want to see slopes in the direction of improvement.

Reply | Quote

Boo,

The best approach is to add a settings dialogue box to let you configure the chart and graphs the way you want. It will retain the settings if you save then subsequent restarts

-Toggle chart visibility for both charts visibility On/Off)
-Toggle legend Off/On
-Series line Color/Monochromic
-Series line weight
-Marker size
-Matrix size
-Max value

When you say “turn off gridlines”, are you referring to the borders of the matrix or the gridlines of graph? I’ll work on it this weekend.

Maud

Reply | Quote

1) I meant the gridlines in the chart. Excel’s defaults generate too much chartjunk for my tastes.

2) I am not expecting you to do all the other stuff. What I have now is good enough. But I am learning a lot about automating charts with VBA, which has never seemed useful to me. I am very much in the mode of “if I ask for this maybe it will get coded for me?”.

Reply | Quote

Here is the Settings dialogue box to make the graphs and matrix fully customizable. Building the coding in the background. Will post when finished.

46007-autochart3

Reply | Quote

For anyone interested, here is a graph configuration form coded to make the graphs fully customizable. Features include:

-Toggle chart visibility for both charts visibility On/Off)
-Toggle legend Off/On
-Toggle gridlines Off/On
-Series line Color/Monochromic
-Series line weight
-Marker size
-Table size
-Max Vertical Axis

The code is well documented so you can pull out any parts to meet your need.

Maud

46071-autochart7

Reply | Quote

Thanks for all your work Maudibe.

Here’s an image of the final result.

46099-Screen-Capture-from-Maudibes-Spreadsheet

It shows student scores on a multiple choice comprehensive quiz. On the left, they’ve had little exposure, and the scores are lower. As they go to the right, they get more exposure, and the scores generally rise.

Reply | Quote

Yes, I can see why you didn’t need colors or series labels. A nice trend line would be handy though.

Glad to be of some help.

Maud

Reply | Quote