Graph questions

[First Aid Computing]

Greetings. I am trying to create a chart based on data collected and do not
know how I would format/create such a monster.

Sample data:
12/16/08 6:10 AM 0m 43.57s
12/15/08 11:30 PM 0m 43.61s
12/15/08 6:07 AM 0m 47.15s
12/14/08 11:40 PM 0m 46.94s
12/14/08 10:10 AM 0m 46.97s
12/13/08 11:50 PM 0m 47.03s
Average: 0m 45.88s Standard: 0m 54.60s

I would like:
1) X-axis to be labeled with the date/time or at least the date.
2) y-axis labeled with the m/s but only within the min/max listed. No extra
above or below.
3) A line across the entire thing showing the standard that the data is
based against.
4) Optional: Is it possible to have the chart as an icon that can be clicked
to open up a window showing the cart?

Further information:
Excel 2000
Average experience

 

[Luke M]

A lot depends on how you have your data setup.
First, I'm assuming you're mainly dealing with just two columns. First
column is formatted as dates.
Second column can be entered as a decimal number, special format as
0"m "00.00"s"
Create your chart, setting up proper y and x axis. You will probably need to
format the axis after you create the chart to display the same way.

Under format axis->Scale, change min and max to appropriate values. If this
is not acceptable, the other alternative I see is using a macro to edit chart
properties after it finds the min/max values.

To add your standard line, simply copy the standard next to all the cells
used in first series, and add another series to the chart using these values.
Will create a flat line on your chart.

 

[First Aid Computing]

Was hoping to not have to copy the standard over and over, etc. ad nauseum.
Apparently Microsoft never thought that someone would need a just a simple
line across a graph somewhere. I suppose I could if I had to and just make
the font white. Will take more time, but probably not much more that has
already been spent on this. Bosses like pretty charts.

How would I do the macro you mentioned? I have 14 pages of data in chunks
of 10 or so data/section (read lots of items with little data so far, it's a
work in progress).
Having to manually adjust the min/max for every chart would make my head
explode.

Any ideas on optional question 4? Otherwise, I'll have to resize the chart
to the hight of the data collected, thus making it quite small until more
data is taken.

 

[Dave Curtis]

Hi,

To get your horizontal line across the chart, you only need to enter a
single point using an appropriate x and y value, and then add a horizontal
error bar to that point, extending as far as you wish in either direction.

Dave

 

[First Aid Computing]

Unfortunately, it will only let me add vertical error bars instead of a
horizontal one. Will continue fiddling with this. However, all my data of
the last two weeks was lost as my office computer died while I was gone. Now
I have to wait for the computer to return and hope they didn't nuke the drive
as all inept techies are wont to do.

 

[Dave Curtis]

Hi,
The fact it will only let you add vertical error bars implies you have added
the data point as a line chart. Try selecting it and changing the chart type
to x-y scatter. then you should be able to add horizontal error bars. I can
email you an example if you want.

Dave

 

[Jon Peltier]

Here's a tutorial that shows the technique:
http://peltiertech.com/Excel/Charts/AddLineHorzErrBar.html

- Jon

 

[First Aid Computing]

It's taking some work to get the line right. Excel crashed a few times
trying to get the line extended. Will work on it more this weekend when I
have time. If you have good examples and/or advice on what I might be doing
wrong, you can e-mail me at (e-mail address removed)

 

[Jon Peltier]

I can't imagine why anything in this technique would cause Excel to crash.
Do you get any kind of error message, or any other hints about a possible
problem?

- Jon

 

[First Aid Computing]

I think I've got the X-axis error bars pretty much worked out now. I think I
set them for too great of a range while trying to aim the ends for 0 the
latest X. WOuld be nice if they would get us the latest Office. Might have
to just go ahead and put Openoffice 3 on it.

Still wondering if there's any way to turn the graph into a nice little
icon, though.

 

[First Aid Computing]

I think I've got it pretty much figured out. I think I was setting too great
an error range while trying to aim it for 0 and the latest X.

Still wondering if there's a way to turn the graph into a nice little icon,
though.

 

[Jon Peltier]

Why do you assume that (a) "the latest Office" or (b) OpenOffice would help?
Using the proper values for your error bars is not a function of software
make or model.

- Jon

 

[First Aid Computing]

I only use MS Office at work and therefore have little experience with doing
more than simple word processing. At home I'm more of a "hardware" guy and
thus equate software with firmware i.e. the latest version has more fixed
bugs, newer features, and tends to work better in general. Had hoped that
maybe the things I wish for are either available or easier to do in a later
version.

As for the error bars, it's been a matter of trial and error to figure out
the length of the bar as everything is in days. I just now figured out that
the days are 38368 etc. and how to set the error bar, but I need to keep
adjusting it as I'm always entering new data unless I set it for an obsenely
long length to begin with.

Instead of being able to skip large chunks of missing days (closed time,
switching machinery, general down-time) I had to create multiple charts for
the same data series.

It's the little things like this that I had hoped would've been corrected or
added into later versions of their software so that I can ease up on wasting
your guys' valuable time and enable me to design these for my (pointy-haired)
boss's understanding.

 

[Jon Peltier]

I only use MS Office at work and therefore have little experience with
doing
more than simple word processing. At home I'm more of a "hardware" guy
and
thus equate software with firmware i.e. the latest version has more fixed
bugs, newer features, and tends to work better in general. Had hoped that
maybe the things I wish for are either available or easier to do in a
later
version.

Yeah, well, Microsoft broke the tradition with their newest release of
Office.

As for the error bars, it's been a matter of trial and error to figure out
the length of the bar as everything is in days. I just now figured out
that
the days are 38368 etc. and how to set the error bar, but I need to keep
adjusting it as I'm always entering new data unless I set it for an
obsenely
long length to begin with.

You can probably keep the values as dates. Internally Excel uses the big
number for calculations, but you probably don't need to worry about it. You
can subtract two dates and get a number of days. The format initially might
be as a date, not a simple number, so ten days difference will look like
10-Jan-1900. Format it as General and it will look like a 10.

- Jon