OT: Excel Scatter plots
Jose de las Heras
use the extensive pool of knowledge (not just about guitars!) in this group.
A simple question (I hope) regarding making scatter plots in Excel, which
I'm pretty sure more than one have used here.
Okay, so to make the plot is very easy. But when I put the mouse pointer
over a particular spot I see some info regarding the values (coordinates if
you wish) of that particular point, and a legend saying something like
"Point 7".
Now, I don't care about "Point 7", what I want to see instead is a name that
means something for that point.
For instance, I have three columns.
Column A gives me a list of names
Columns B and C give me a value each, "coordinates" for my scatter plot.
I want to plot the set of (B,C) points, and to assign the respective name
from column A to each point, so that I can see it displayed when I put the
cursor over any given point.
I haven't found the way to do that... but I'd have guessed it's not an
unusual requirement for a scatter plot, and I'd be surprised if I cannot do
that in Excel.
Any pointers, please?
and... erm... the sooner I find a solution to this, the more time I'll have
to play guitar, of course! ;-)
Jose
--
Musha ring dum a doo dum a dah - www.mcnach.com
Justin Otto
Jose de las Heras wrote:
> Now, I don't care about "Point 7", what I want to see instead is a
name that
> means something for that point.
>
> For instance, I have three columns.
>
> Column A gives me a list of names
> Columns B and C give me a value each, "coordinates" for my scatter
plot.
>
> I want to plot the set of (B,C) points, and to assign the respective
name
> from column A to each point, so that I can see it displayed when I
put the
> cursor over any given point.
>
> I haven't found the way to do that... but I'd have guessed it's not
an
> unusual requirement for a scatter plot, and I'd be surprised if I
cannot do
> that in Excel.
Interesing. I'm not sure how you've set this up, but the name should
indicate the series. In other words, when you create the chart, you
should create a series for each name you would like, and set all the x
values for that item in one field and all the y values for that in the
other. ie.
Name#X#Y
Foo#1#2
Bar#3#4
So create a series with a name of A2, X of B2, Y of C2, and another
with a name of A3, B3, C3, etc. Now the hint will say, "Foo (1,2)" or
"Bar(3,4)".
Scatters are really intended to represent series of points though, so
if you data was more like:
Foos
X#Y
1#2
3#4
5#6
Bars
X#Y
7#8
9#10
etc. I would've though it would've been more appropriate.
*shrug*
HTH.
J.
Jose de las Heras
"Justin Otto" <justi...@hotmail.com> wrote in message
news:cds1sr$1...@odbk17.prod.google.com...
First of all, thanks a lot for looking into this, Justin.
It's a bit late and my brain is somewhat impaired... but I'm not exactly
getting what you were saying above... The first thing you suggest sounds a
bit like what I want to obtain ("Foo (1,2)") but... okay, let me tell you
more clearly.
I have a table with data for anything between 10000-30000 genes. Each gene
in a separate row. Different measurements per column. One very useful thing
for me is a scatter plot made from two measurements per gene. So the table I
would consider would be something like this:
gene ID value1 value2
a5467 12 14
a7499 23 10
a8888 9 5
b4666 20 20
a2663 3 32
.
.
a8701 17 19
So I want a scatter plot made from the second and third column: (12,14);
(23,10); etc... and I want that when I put the pointer over the point (9,5)
the hint tells me "a8888".
I hope it's a bit clearer now.
There are specialised programs that do what I want... but if I can do this
with Excel I can make a sheet customised perfectly for my experiments.
Any ideas? :-)
Jose
Myles
"Jose de las Heras" <jose.de...@virgin.net> wrote in message
news:2mav5dF...@uni-berlin.de...
Use the chart wizard, it will walk you through the steps. At one point,
you'll see a spot for "names", you click on that, highlight the range where
the names are located. Toward the end of the wizard, you'll have a choice
of displaying "values" or "names" for your data labels. Pick names, and you
should be in business.
Myles
>
Jose de las Heras
"Myles" <sp...@mylescochran.com> wrote in message
news:cdu2j6$44j$1...@sparta.btinternet.com...
>
> Use the chart wizard, it will walk you through the steps. At one point,
> you'll see a spot for "names", you click on that, highlight the range
where
> the names are located. Toward the end of the wizard, you'll have a choice
> of displaying "values" or "names" for your data labels. Pick names, and
you
> should be in business.
>
> Myles
Hi Myles,
(sob...)
no, it doesn't work like that.
I can select a range for names, a range for X values and a range for Y
values... and later can I select display labels... but that doesn't do what
I want.
Displaying labels only displays the X value, permanently, next to the point.
When I put the pointer over a point I still get the (x,y) values and a text
saying "Point X"...
The only place where the list of names appear is as a title for the plot
(nah! don't want that) or on the side as "series .. and list of names. And
I mean the whole list of names... If this was my real chart, it'd displat
10000+ names :-) Clearly not what I want.
It's very frustrating, because it looks like EXcel is capable of showing
what I want it to show... but I haven't found how to tell it what to
display, properly.
Jose
Justin Otto
Jose de las Heras wrote:
> First of all, thanks a lot for looking into this, Justin.
You're more than welcome.
Sorry that I am not going to give you the answer you want to hear.
> I have a table with data for anything between 10000-30000 genes. Each
gene
> in a separate row. Different measurements per column. One very useful
thing
> for me is a scatter plot made from two measurements per gene. So the
table I
> would consider would be something like this:
<snip>
> So I want a scatter plot made from the second and third column:
(12,14);
> (23,10); etc... and I want that when I put the pointer over the point
(9,5)
> the hint tells me "a8888".
>
> I hope it's a bit clearer now.
Yes, it is.
You are talking about different data series, here.
The problem you are experiencing here is that the hint functionality
doesn't exist to do what you are attempting. The scatter plot is really
there to show a couple of data series on top of one another. Not 10000+
different data series. The hint is there to show you which one of the
series a particular point belongs to in a confusing picture.
Computing resources are finite, and programmers try to include as few
limits as possible - choosing sensible limits where almost unlimited
extensibility would be more trouble than its worth. Without even trying
it, I can tell you that you will probably hit a hard limit on the
number of data series in your graph long before you've got 30,000
entered. (be it through macro or whatever)
Excel exists to fulfil most generic purposes, which is why there are
limits such as the 65535 row cut-off; a value which is more than
sufficient for most common situations.
> There are specialised programs that do what I want... but if I can do
this
> with Excel I can make a sheet customised perfectly for my
experiments.
The reason why specialist programs exists is to allow different limits
to apply - and to implement functionality in a way that makes sense for
certain more limited applications. Excel can do what you want, but
probably not in the scale you are looking for. At least not
conveniently, or reliably - and possibly not at all.
I would suggest that you look at some of those specialist apps. If
expense is an issue then you might want to see if there's an open
source app which fulfils your requirements.
If you are just looking for something which turns a list of values into
an on-screen display for viewing and interrogation in the manner you
are describing, then shout and I can knock one up for you tomorrow in
about half an hour. If you need more than that, I would suggest that a
more flexible and longer term solution would be found with a specialist
app.
Good luck,
J.
Jose de las Heras
>
>
> You're more than welcome.
you're very kind... :-)
> Sorry that I am not going to give you the answer you want to hear.
so am I, believe me...
> You are talking about different data series, here.
3 of them, yes.
> The problem you are experiencing here is that the hint functionality
> doesn't exist to do what you are attempting. The scatter plot is really
> there to show a couple of data series on top of one another. Not 10000+
> different data series. The hint is there to show you which one of the
> series a particular point belongs to in a confusing picture.
I'm afraid that is totally correct.
There's another aplication that I sometimes want for which Excel will do
just fine. For instance, if I want to display the genes all in a row,
according to their real positions along a chromosome... then I only need *1*
value... and I can use the hint to display the name of the gene. Easy. I've
done it. It gives a nice graphical plot of gene locations and a particular
property that I have measured, and its variation along the chromosome,
"geographically" so to speak. Nice.
The problem with what I wanted to do is that I use my two values to put a
point anywhere on the chart... and I don't have provision for any more...
I let my wishful thinking get the better of me and tried to hard to find
something that didn't exist. That's just me, I have to try and see for
myself.
> Computing resources are finite, and programmers try to include as few
> limits as possible - choosing sensible limits where almost unlimited
> extensibility would be more trouble than its worth. Without even trying
> it, I can tell you that you will probably hit a hard limit on the
> number of data series in your graph long before you've got 30,000
> entered. (be it through macro or whatever)
>
> Excel exists to fulfil most generic purposes, which is why there are
> limits such as the 65535 row cut-off; a value which is more than
> sufficient for most common situations.
Indeed.
The tables I deal with are enormous... but are not made by hand...
thankfully!
The list of genes is already compiled. The measurements and derived
calculations on the sheet are all obtained from a cunning program that reads
pixels from an image, obtained after scanning a microscope slide containing
microarrays of those genes blah blah blah... Basically I just do my white
coat thng, put my slide on the side of a machine, run a program... and I get
an image. Play a little with it, enter some info... and the program
regurgitates that massive table.
I wanted to try to get the rawest data possible from that table and use it
my own way... I'm learning to analyse these data and most programs that I
use are terribly comprehensive and complex, or too simple and give you
solutions without explaining to youhow the reached those conclusions... and,
I don't like believing the result a program gives me just because... I mean,
when somebody criticises my data and my conclusions I want to be able to
refute their arguments, I cannot say "well, erm... the program said so..."
Excel is great. But I see it has limitations, unless you're prepared to
write add-ons... and quite frankly, my programming days are long long
gone... and I was only using TurboPascal (does that betray my age? ;-)
> I would suggest that you look at some of those specialist apps. If
> expense is an issue then you might want to see if there's an open
> source app which fulfils your requirements.
There are commercially available suites for these things. We're looking at
£6000 for it...
I've been taught a couple of them. And that's when I went "nah, I don't need
all that". Those programs offer a lot... but my requirements (and my lab's)
aren't *that* sofisticated...
There are free programs available, and I can get the type of pot that I want
with them, as well as more interesting operations with the data. But I'll be
playing with a host of different formats and have to interconvert all the
time... since that will be done in Excel, I started thinking "hey, I can do
the main calculations here, and if it gives me this simple plot, I can even
make my figures with it".
> If you are just looking for something which turns a list of values into
> an on-screen display for viewing and interrogation in the manner you
> are describing, then shout and I can knock one up for you tomorrow in
> about half an hour. If you need more than that, I would suggest that a
> more flexible and longer term solution would be found with a specialist
> app.
That's really generous of you, Justin. I appreciate your gesture a lot (and
scribble your name in a mental note for possible future reference ;-) but
that plot is only part of a bigger thing. I think I have found the reason
why other labs have developed these tools instead of relying on Excel's
abilities... and I should just choose the one that best suits my needs and
get on with my experiments, instead of playing with mock tables...
Thanks a lot Justin...
case closed.
Jose