REMOVING GAPS IN DATES

[Erick]

I want to chart some production data. Right now I have a x-y scatter
and it plots the dates on x-axis and the data on the y-axis. The boss
would like the dates that we did not run production to be cut out of
the chart.

So, if we ran on 01-01-04 to 01-02-04 and picked up again at 03-01-04,
then only the days that we ran would be on the chart and evenly spaced
throughout like 01-01-04, 01-02-04, 03-01-04, etc.

If anyone would give me a hint it would be very helpful. Thank you.

Erick

 

[Guest]

YOu could change your chart type to "line" remove lines
and leave markers and select chart options and
select "category" for the x axis which will force show
only the dates you've got entered.

Aminna

 

[Erick]

Thank you for the reply, I guess I forgot to mention a couple of
things.

The one thing that I can not seem to do with the "line" type of chart
is use a different x-axis setup for two series of data. I have two
components that I am comparing the values for and sometimes there will
be (10) components measured on the same day that the other one only
got measured (9) times, so when I go to pick the x-axis values the
"line" chart will only give me one set of data I can use for x-axis.

Thank you,

Erick

 

[Jon Peltier]

Erick -

Do the series have the same X values, except one starts sooner and the
other ends later? You could use this setup:

X1 Y1
X2 Y2
X3 Y3 Z3
X4 Y4 Z4
X5 Z5

X is the shared X values, Y and Z are the Y values for series 1 and 2.
Select the entire range, so both series include blanks, corresponding to
nonblank values in the other series. The blank cells by default are not
charted.

If not, you could plot one series on the secondary axis, and bring up
the secondary X axis. Right click on the chart, select Chart Options,
click on the Axes tab, and check the Secondary X Category Axis box
(uncheck the Secondary Y box too if necessary).

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

 

[Erick]

Jon, Thanks, The first way is not going to work because my data is
more like

X1 Y1 Z1
X1 Y2 Z2
X1 Y3
X2 Y4 Z3
X2 Y5
X2 Z4
X4 Z5
X5 Y6 Z6
X5 Y7
X6 Z7

With different amounts of data for a day, and some days missing
altogether.

The second way did work very good, however one thing that I forgot was
that sometimes I compare up to 4 sets of data. (I had a little
trouble figuring out how to add the second axis, which you just have
to double click on the series). I tried 4 series the same way as two
sets and I do not see excel giving me the option to have more than 2
category axes.

I might just be out of luck?

Thanks for keeping this going.
Erick

 

[Jon Peltier]

Hi Erick -

What didn't work with the first approach? If the gaps between values are
truly empty cells (i.e., not formulas that look empty but return "",
which is a rather short text string), you can choose to extend the line
across the gap (interpolate) or leave the gap. Select the chart, choose
Options from the Tools menu, and look at the top half of the Chart tab.

You can only get two official sets of axes, Primary and Secondary. I
demonstrate how to get a fake Tertiary axis on my web site:

http://peltiertech.com/Excel/Charts/TertiaryAxis.html

And this can always be extended to the point of illegibility. You might
look at this approach, at least to see how you can relax the bounds of
the predefined axes provided by Excel.

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

 

[Erick]

Thank you Jon,

I am giving a small paste of the data that I am working with to draw
some more light on the subject.

I can not get the first way to work. I am also sending Jon a copy of
the sample spreadsheet that I have been working on, and anyone else
who wants it please email me and I will give you a copy. Thank you
much

DATEID L1-POINT1L2-POINT1
1/7/04 0:38 #N/A 2931.501
1/7/04 1:22 #N/A #N/A
1/7/04 14:2 2931.269 #N/A
1/7/04 15:0 #N/A #N/A
1/7/04 15:2 2931.421 #N/A
1/7/04 16:1 #N/A #N/A
1/7/04 18:1 2931.22 #N/A
1/7/04 19:2 #N/A #N/A
1/7/04 21:5 #N/A 2931.445
1/7/04 22:4 #N/A #N/A
1/7/04 23:5 2931.3 #N/A
1/19/04 7:2 2931.187 #N/A
1/19/04 8:0 #N/A #N/A
1/19/04 8:2 2931.215 #N/A
1/19/04 19: #N/A 2931.284
1/19/04 19: #N/A #N/A
1/19/04 22: 2931.108 #N/A
1/19/04 23: #N/A #N/A
1/20/04 3:5 #N/A 2931.352
1/20/04 4:4 #N/A #N/A
1/20/04 8:0 2931.215 #N/A
1/20/04 8:5 #N/A #N/A
1/20/04 13: 2931.17 #N/A
1/20/04 14: #N/A #N/A
1/20/04 20: #N/A 2931.373
1/20/04 21: #N/A #N/A
1/20/04 23: #N/A 2931.388
1/21/04 0:3 #N/A #N/A
1/21/04 0:5 #N/A #N/A
1/21/04 1:0 2931.192 #N/A
1/21/04 6:0 #N/A 2931.217
1/21/04 6:5 #N/A #N/A
1/21/04 7:1 2931.015 #N/A
1/21/04 15: #N/A 2931.399
1/21/04 16: #N/A #N/A
1/21/04 16: #N/A 2931.291
1/21/04 18: #N/A 2931.408
1/21/04 19: #N/A #N/A
1/21/04 23: #N/A 2931.395
1/22/04 0:4 #N/A #N/A
1/22/04 1:1 2931.311 #N/A
1/22/04 2:4 #N/A #N/A
1/22/04 9:2 #N/A 2931.511
1/22/04 10: #N/A #N/A
1/22/04 12: 2931.381 #N/A
1/22/04 13: #N/A #N/A
1/22/04 19: #N/A 2931.628
1/22/04 20: #N/A #N/A
1/22/04 20: 2931.289 #N/A
1/22/04 21: #N/A #N/A

Erick

 

[Jon Peltier]

I cheated. There were a few points on 1/7/04, then a gap until 1/19/04.
I added 11 days to the 1/7/04 dates, so they appeared to occur on 1/18/04.

To add a number to a bunch of cells, type the number into a cell, copy
the cell, select the cells you want to increase, and choose Paste
Special from the Edit menu. Select the Values and Operation-Add options,
and the cells will all increase by that amount.

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