[Tech] Excel halp!

Angelic

Angelic

Active Member
Hey folks,
I've got one Excel question for you. I'm using Excel '07, just to avoid confusion further down the line :)
I'm looking to present our research results. I'd like to use a type of graph that is not in the basic set of graph types Excel offers you, but IMO should be easily done by using an XY scatter graph. I can't seem to be able to properly set it up to do exactly what I need.
I want point graph, no connecting lines. I have, say, two categories and in each a bunch of values. I'd like to present those as points in a single column for each category. The usual XY scatter graph treats them as pairs of XY values, but I want the points to have a different pair of values: not 1;3.78, but diGeorge;3.78, so to speak.
I might be explaining this too complicatedly :) Anyone understand what I mean?

I'll post a crude drawing when I come back home.

SAdly, googling this wasn't helpful at all :(
 
bacon

bacon

Well-Known Member
Something like this?

oimg
 
Wol

Wol

In Cryo Sleep
Angelic said:
I want point graph, <snip> in a single column
Click to expand...

What are you trying to visualise with this graph? Are you trying to see how clustered each of the points is together? OR show the difference between mean/median of the data? the range of the data? etc.

Can you actually give us an example of the data you want to use? Chances are, if its not a default excel graph style, then theres a good reason for it, and you may realise that what you currently want isn't a helpful of visualising the data.

IF its to do with ranges / max / min / median / mean, then you want a box plot I'd say.
 
Angelic

Angelic

Active Member
@bacon - yes, that is pretty much excatly it. I'd like the X labels to not be numbers, but names of categories, but that could be easily arranged I feel.

@wol - im trying to show spread and clustering of results in each category + mean + easy to see comparison of the two categories (hence using one graph rather than two. Box plot would be nice, although not exactly what I'm after. Problem is, I dont even think you can get box plot easily in Excel 07. You have to fake it using column graph.
 
bacon

bacon

Well-Known Member
Angelic said:
@bacon - yes, that is pretty much excatly it. I'd like the X labels to not be numbers, but names of categories, but that could be easily arranged I feel.
Click to expand...

Well, that's a Google Docs graph so I'd hope excel is a little more customisable than that.

I don't have Excel 2007 available at the moment to test so if I remember I'll have a look once I'm home from work this evening.
 
Wol

Wol

In Cryo Sleep
hXJ0DC.png


thats a box plot in 07 (line plot + high low bar + up down bars)

If thats not quite what you want, is a cumulative graph or probability density graph what you want? Basically what im trying to get at is that normally on a scatterplot, you can sometimes get bit clustered, if you remove one of the dimensions, if you get a few points on top of each other, you can't get a good idea of how clustered they are if theyre drawn directly on top of each other.
 
Panda with issues...

Panda with issues...

Well-Known Member
Angelic said:
@bacon - yes, that is pretty much excatly it. I'd like the X labels to not be numbers, but names of categories, but that could be easily arranged I feel.

@wol - im trying to show spread and clustering of results in each category + mean + easy to see comparison of the two categories (hence using one graph rather than two. Box plot would be nice, although not exactly what I'm after. Problem is, I dont even think you can get box plot easily in Excel 07. You have to fake it using column graph.
Click to expand...

Essentially it sounds like, as wol has suggested, what you want is a kind of box and whisker plot.

Programs don't really seem to be very helpful when trying to produce this kind of graph.

Are you using PC excel 2007 or mac? The mac version is poor.

I think I know what you're trying to do, and the best solution is to plot XY with X as a variable, and Y as a constant, with a different number constant for each category (or vice versa) then edit the labels for your constant later. This will give you a linear array in either the horizontal or the vertical showing the clustering of your data. Individual data points in excel can normally be interrogated and edited if necessary too.
 
Angelic

Angelic

Active Member
grafm.png


I mean this, with the labels for X axis, not having to space the bars manually and not having to spoof the X values of the points to 1;1;1;1;1;1;1;1... and 2;2;2;2;2;2;2;2... in order to make it show them in single column. I'd like to select DiGeorge;DiGeorge;DiGeorge... as one set of X values and control;control;control... as another. That would even take care of labelling the X axis for me. But nope, can't do that! It forces the X axis to be number only, not text values.

Oh gods I hate Excel so much >.< This should be EASY!
 
Panda with issues...

Panda with issues...

Well-Known Member
Angelic said:
grafm.png


I mean this, with the labels for X axis, not having to space the bars manually and not having to spoof the X values of the points to 1;1;1;1;1;1;1;1... and 2;2;2;2;2;2;2;2... in order to make it show them in single column. I'd like to select DiGeorge;DiGeorge;DiGeorge... as one set of X values and control;control;control... as another. That would even take care of labelling the X axis for me. But nope, can't do that! It forces the X axis to be number only, not text values.

Oh gods I hate Excel so much >.< This should be EASY!
Click to expand...


I know it's a pain, but it's not exactly a massive chore to relabel the axes.

I agree that excel is shit for plotting graphs, and I don't use it any more. I now mainly use kalaidograph, and have considered buying an up to date licence for it. I've attempted to use sigma plot too, but since what i've mostly been plotting recently has been histograms, I've stuck with kalaidograph, since sigma plot isn't particularly intuitive.

I'd say that none of the three packages I've mentioned is perfect, but a combination approach generally allows you to be able to do what you want.

Now I'm on the cutting edge of science, I've realised quite how poor a lot of these programs actually are. They're mostly all designed to draw pie charts for fat businessmen. The range of different styles of graphs available in excel is vast, but the fact of the matter is, the only one that is really useful is the xy scatter. It's difficult to add useful things like second axes, and excel graphs look like ass, and are unfit for publication.
 
Angelic

Angelic

Active Member
OK, time to revive this thread.

Again, Excel is refusing to offer me the possibility of creating column graphs that are not grouped/stacked/whatever.

This is a picture of what I need to do:

watineed.png


Can anyone help me with this? I'd like to do it without too much fuckery with paddings/margins/etc, just the logical way, if that's possible.

Thankies,
Ange
 
You must log in or register to reply here.
Top