Skipping Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I have a chart that is based on dates and I want to be able to skip dates
that do not have any data associated with it.

Example:

A B
1/1/05 1
1/2/05 2
1/3/05 1
1/6/05 3
1/7/05 2

Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
plots the missing dates as zero. I want the chart to not not put the missing
dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7,
etc on the X axis.

How do I do that? I have already tried changing the Chart options from
Automatic to Time-Scale .

Thanks for any help,

Sharon
Sharon
 
Okay, we're on the right track now. That worked, but since my series is
based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it
wants to squish the first 25 lines into the first third of the chart and it
plots zero for the cells 26-65. Is there a way I can get it to only plot
where there is data? My data source ($BB$3:$BB$65) is a formula.

Thanks,

Sharon
 
I tried that and it seems like it is what I need to do, but when I try to
enter the data source it gives me an error message saying that, "The
reference is not valid. References for titles, values or sizes must be a
single cell, row or column."

My headers consist of rows 1-3 and my data starts on row 4. Cells A1:C2 are
merged to create a title and cells A3, B3 and C3 are the headers for the data.

I modified the formulas on your website to suit my data as follows:

=OFFSET(RYGSeries1ChartValues, 0, -4)

=OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)

=OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)

Do you know what I'm doing wrong here?

Thanks so much,

Sharon
 
You may have answered it:
"The reference is not valid. References for titles, values or sizes
must be a single cell, row or column."
Click to expand...
=OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)
=OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)
Click to expand...

These two formulas refer to ranges 4 columns wide (the last 4 within the
parens).
/sig
 
Back
Top