Thread: Looking for Charting Suggestions

1. Looking for Charting Suggestions

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?

2. 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.

graph.png

3. The Following User Says Thank You to Maudibe For This Useful Post:

boobounder (2016-11-07)

4. 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.

5. Graphing rows and columns as you add data

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

autochart1.png

6. The Following User Says Thank You to Maudibe For This Useful Post:

boobounder (2016-11-14)

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

8. 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

9. 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.

10. working on it!

11. 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

autochart2.png

12. The Following User Says Thank You to Maudibe For This Useful Post:

boobounder (2016-11-15)

13. 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

14. 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?

15. 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.

16. 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

17. 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?".

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

autochart3.png

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•