OT: Plotting data with Excel

Hi there.

I am involved in a project where we want to measure some data.
My job is basically to guide the customer in choise of equipment
and experiment setup, and of analyzing the data after the
measurements are done. The customer will do the actual
measurements, for a number of practical reasons.

Now, I use matlab to plot and do QC on the data. In this
experiment I will not be around to do that, and I don't see
much point in getting the customer to buy a $2000 matlab
lisence just to make a couple of plots of the data during
measurements. So I am thinking of doing this with excel
instead.

The problem is that I haven't used excel for ages.

What I want to know is if it is possible at all to have excel do
something like IMAGESC in matlab. That is, take a NxM
matrix and make an image where the color of pixel (n,m)
is somehow related to the numerical value in cell (n,m).

The data consist of N time series, each M samples long.
I want to plot them either as intensity-coded images, or
as vertical traces, "waterfall transposed" so to speak.

Any suggestions and comments are appreciated.

Rune

"Rune Allnor" schrieb 
>
> [plotting scientific data]
>

Maybe I have an aversion against Excel (having once
spent hours because Excel IIRC by default does not
plot like a real engineer would do, or because in the 
olden DOS days I wrote my own plotting package), 
but I'd rather use anything else.

You might want to look into GNUPLOT, also available
for Windows, which is highly powerful.

Regards
Martin

Martin Blume wrote:
> "Rune Allnor" schrieb
> >
> > [plotting scientific data]
> >
>
> Maybe I have an aversion against Excel (having once
> spent hours because Excel IIRC by default does not
> plot like a real engineer would do, or because in the
> olden DOS days I wrote my own plotting package),
> but I'd rather use anything else.
>
> You might want to look into GNUPLOT, also available
> for Windows, which is highly powerful.

I use matlab for my own stuff, and that works most of the time.
The reason I ask is that the customer does have excel, and I
would like to use that, if possible.

These guys will be logging data for a fortnight, and they would
like to see the data for themselves as the data are collected.
This is a once-only occurence. If the results are good, we
will probably make a more streamlined system where data
logging and presentation are taken care of.

But until we get the results that justify us making a custom
system, we must make do with what we have...

Rune

Rune Allnor wrote:
> Martin Blume wrote:
> 
>>"Rune Allnor" schrieb
>>
>>>[plotting scientific data]
>>>
>>
>>Maybe I have an aversion against Excel (having once
>>spent hours because Excel IIRC by default does not
>>plot like a real engineer would do, or because in the
>>olden DOS days I wrote my own plotting package),
>>but I'd rather use anything else.
>>
>>You might want to look into GNUPLOT, also available
>>for Windows, which is highly powerful.
> 
> 
> I use matlab for my own stuff, and that works most of the time.
> The reason I ask is that the customer does have excel, and I
> would like to use that, if possible.
> 
> These guys will be logging data for a fortnight, and they would
> like to see the data for themselves as the data are collected.
> This is a once-only occurence. If the results are good, we
> will probably make a more streamlined system where data
> logging and presentation are taken care of.
> 
> But until we get the results that justify us making a custom
> system, we must make do with what we have...

Gnuplot costs a little: the time to download and install it, and the 
time to learn to use it (or learn the hard way that you didn't, really). 
All of that might nevertheless be a moderate and justifiable expense.

Jerry
-- 
Engineering is the art of making what you want from things you can get.
�����������������������������������������������������������������������

Jerry Avins wrote:
> Rune Allnor wrote:
> > Martin Blume wrote:
> >
> >>"Rune Allnor" schrieb
> >>
> >>>[plotting scientific data]
> >>>
> >>
> >>Maybe I have an aversion against Excel (having once
> >>spent hours because Excel IIRC by default does not
> >>plot like a real engineer would do, or because in the
> >>olden DOS days I wrote my own plotting package),
> >>but I'd rather use anything else.
> >>
> >>You might want to look into GNUPLOT, also available
> >>for Windows, which is highly powerful.
> >
> >
> > I use matlab for my own stuff, and that works most of the time.
> > The reason I ask is that the customer does have excel, and I
> > would like to use that, if possible.
> >
> > These guys will be logging data for a fortnight, and they would
> > like to see the data for themselves as the data are collected.
> > This is a once-only occurence. If the results are good, we
> > will probably make a more streamlined system where data
> > logging and presentation are taken care of.
> >
> > But until we get the results that justify us making a custom
> > system, we must make do with what we have...
>
> Gnuplot costs a little: the time to download and install it, and the
> time to learn to use it (or learn the hard way that you didn't, really).
> All of that might nevertheless be a moderate and justifiable expense.

I am not sure the users will find it justifiable. Again, it is not me
who 
will be using the program.

Rune

"Rune Allnor" schrieb 
> 
> [plotting with Excel]
>
Sorry Rune, I didn't read your whole post. As soon
as I read "Excel" and "plotting", I went into overdrive.
I can't really comment on Excel's plotting capabilities,
as I don't use them (perhaps because I don't use it,
plots turn out to be ugly and because they are ugly, 
I don't use it).

Coming back to Gnuplot: it is driven by command
scripts and you could prepare them in advance (using
e.g. sample data). Then the customer can run your 
script with the data measured. IMHO this is much simpler
than with Excel, but YMMV.

If the customer has a little expertise, he can modify
the scripts himself easily, e.g. to plot only part of 
the data, eliminate outliers, ...
Being scripted, gnuplot is easier to work with.
But then OTOH, I am a command line guy.

As a sample for plotting, consider this:

*** plot file ***
set terminal postscript landscape color
set output "out.ps"
set grid
set title "Field Strength"
set xlabel "f [MHz]"
set ylabel "Field Strength [dBuV/m]"

plot [20.0:80.0] [10.0:120.0] "abc.dat" \
 using 1:2 title "abs avg" with lines,\
 "abc.dat" using 1:3 title "abc min" with lines,\
 "abc.dat" using 1:4 title "abc max" with lines
 
*****

This takes the file "abc.dat", plots only data in 
the x range 20 to 80 MHz, plots the first line 
(averages) using the first and the second field, 
plots minimal values using the first and third field
and plots maximal values using the first and fourth
field.

Want to plot from 40.0 to 45.0 MHz? Just exchange
[20.0:80.0] to [40.0:45.0]. Explain to a non-Excel user
(like me :-)) how to do this in Excel ...

The URL 
http://t16web.lanl.gov/Kawano/gnuplot/index-e.html
may give you some more examples and help.
And of course, the official homepage at
http://www.gnuplot.info/
is there also.

I think - and it may have transpired from the above
posts - that gnuplot is an execellent package.

HTH
Martin

"Rune Allnor" <allnor@tele.ntnu.no> wrote in message
news:1138722531.640861.235120@g44g2000cwa.googlegroups.com...
> Hi there.
>
<snip>
>
> What I want to know is if it is possible at all to have excel do
> something like IMAGESC in matlab. That is, take a NxM
> matrix and make an image where the color of pixel (n,m)
> is somehow related to the numerical value in cell (n,m).
>
> The data consist of N time series, each M samples long.
> I want to plot them either as intensity-coded images, or
> as vertical traces, "waterfall transposed" so to speak.
>
> Any suggestions and comments are appreciated.
>
> Rune

Hi Rune,
 I would just write a quick BMP file utility to take your raw data and
generate an un-compressed windows bitmap file. With a command line interface
for parameters (N and M, for example) or with that info embedded in a header
to your raw data files, the actual data-to-bmp code is trivial. Customer
could then use windows paint or some such to view it. The nice thing about
BMP is it supports un-compressed images so you don't even have to do RLE of
LZW, just construct the header and spit out the pixels (with whatever
data-to-color algorithm you want). Google "BMP file format".

Bob

Bob wrote:
> "Rune Allnor" <allnor@tele.ntnu.no> wrote in message
> news:1138722531.640861.235120@g44g2000cwa.googlegroups.com...
> > Hi there.
> >
> <snip>
> >
> > What I want to know is if it is possible at all to have excel do
> > something like IMAGESC in matlab. That is, take a NxM
> > matrix and make an image where the color of pixel (n,m)
> > is somehow related to the numerical value in cell (n,m).
> >
> > The data consist of N time series, each M samples long.
> > I want to plot them either as intensity-coded images, or
> > as vertical traces, "waterfall transposed" so to speak.
> >
> > Any suggestions and comments are appreciated.
> >
> > Rune
>
> Hi Rune,
>  I would just write a quick BMP file utility to take your raw data and
> generate an un-compressed windows bitmap file. With a command line interface
> for parameters (N and M, for example) or with that info embedded in a header
> to your raw data files, the actual data-to-bmp code is trivial. Customer
> could then use windows paint or some such to view it. The nice thing about
> BMP is it supports un-compressed images so you don't even have to do RLE of
> LZW, just construct the header and spit out the pixels (with whatever
> data-to-color algorithm you want). Google "BMP file format".
>
> Bob

Thanks. That's a brilliant idea. At first glance, it ought to be
possible to
write a stand-alone data2BMP converter in a day or so.

Rune

Rune Allnor wrote:
> Hi there.
> 
> I am involved in a project where we want to measure some data.
> My job is basically to guide the customer in choise of equipment
> and experiment setup, and of analyzing the data after the
> measurements are done. The customer will do the actual
> measurements, for a number of practical reasons.
> 
> Now, I use matlab to plot and do QC on the data. In this
> experiment I will not be around to do that, and I don't see
> much point in getting the customer to buy a $2000 matlab
> lisence just to make a couple of plots of the data during
> measurements. So I am thinking of doing this with excel
> instead.
> 
> The problem is that I haven't used excel for ages.
> 
> What I want to know is if it is possible at all to have excel do
> something like IMAGESC in matlab. That is, take a NxM
> matrix and make an image where the color of pixel (n,m)
> is somehow related to the numerical value in cell (n,m).
> 
> The data consist of N time series, each M samples long.
> I want to plot them either as intensity-coded images, or
> as vertical traces, "waterfall transposed" so to speak.
> 
> Any suggestions and comments are appreciated.
> 
> Rune
> 

What about Scilab. It's free with several color plotting routines and 
has function to convert Matlab code to Scilab.

In article <1138722531.640861.235120@g44g2000cwa.googlegroups.com>,
Rune Allnor <allnor@tele.ntnu.no> wrote:
>What I want to know is if it is possible at all to have excel do
>something like IMAGESC in matlab. That is, take a NxM
>matrix and make an image where the color of pixel (n,m)
>is somehow related to the numerical value in cell (n,m).

Excel can do this, a couple of ways.  Neither are satisfying.

1. A surface plot is the best way if N and M are small (like less
than 50 or so).  If N and M are large, you won't see colors because
Excel draws a rather thick border around each cell.  Click on the
chart wizard icon (it's near the zoom percentage box on one of the
toolbars) and go on from there.  Be warned, it's primitive.  You can
display the surface either as a 2-D map or a 3-D surface.

2. If N or M is less than 256, you can color individual cells by the
value in the cells.  However, you'll need to create a macro to do
this.  I've actually seen this done for radar image data, with the
cells shrunk down real small almost to pixel size.  What's nice is
you could click on a cell and see the value creating the color.

-A