Fixing the tickmarks in a line chart

[MF Folz-Donahue]

Hi folks,

I have some assay data for two groups, control and treated, over a
number of days. First there is the pretreatment day, then there is day
0, day 1, and so on to day 10. I want to plot the control and treated
data points against the days. I tried doing this as an x-y scatter
plot, but it won't allow me to include an x point "pretreatment" since
that's not a number. If I try a line graph, it lets me include
"pretreatment" as a label, but the labels, data points and tick marks
do not line up. I have fiddled with the tick mark options a great deal
to no avail.

I seem to remember having this same problem (with the tick marks in
particular) six or seven years ago, but I can't remember how I solved
it.

I thought perhaps I should just do the scatter plot and paste a
"pretreatment" label over the -1 day on the x-axis, but I'm told here
in the office that such labels have a way of vanishing when the file
moves from one computer to another.

Any suggestions? (Besides "Why the heck are you using Excel for
scientific applications?" because I don't have a choice--boss man
makes the rules!)

I have some background in Visual Basic for Access, so if there is some
sort of way to get what I want by using VB instead of the chart
wizard, please let me know.

-MF Folz-Donahue

 

[Jon Peltier]

Here's a trick. Put -1 in the day column for your pretreatment. Use a
custom number format for this range:

[<0]"Pretreat";0

This uses the text "Pretreat" for any value less than 0, and a whole
number for any value >= 0. When you generate the chart, Excel will
likely use the number format from the data range for the axis tick
labels. If not, you can apply the same number format to the axis ticks.

I'll tell you why else you're using Excel for scientific charting.
Despite its shortcomings, Excel is a very powerful tool. If there is a
feature that's not built in, you can usually fake it (example above). It
interfaces nicely with reporting and presentation software, and with
VBA/VB. Everyone has it on their computer, and even if it's an old
version, they can probably make use of what you send them. The interface
is consistent with other programs, which helps reduce the learning
curve. And the help system is pretty good, if you include these online
forums.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______