Charting woes with hidden dates

  • Thread starter Thread starter dustin pockets
  • Start date Start date
D

dustin pockets

All,

I need some help plotting in Excel. I thought I knew something about
this charting stuff but now I'm stumped. I tracking my investments at
random dates and can x-y plot the Date vs $, with the dates being on
the x –axis and the $ being on the y-axis. Plots OK, axis labels,
dates and dollars are OK. Problem occurs when I try to hide some
dates to unclutter my charts. All plotted values disappear from the
chart or the dates change. The vertical axis remains titled and
scaled properly but the x-axis (the date) will change to 1/0/00 to
x/x/x (where x is the count of data points to be plotted) while the
Format-axis value control is set to automatic. If I opt to select a
fixed date (in Format axis) value the data completely disappears.
This problem exist whether I'm charting 6 or sixty values. I've
checked the Plot Visible Cells Only box and the format for the date is
3/24/04 , the format for the $ is Accounting.

I've tried to duplicate this problem in a separate spreadsheet with
conflicting results!! A column of six dates and simple $ amounts will
chart correctly when rows are hidden. BUT, copying a 8 line section
of my investment spreadsheet into the separate spreadsheet copies the
problem described above.

I've carefully checked the formats for the dates and $ but now I'm
stumped and respectfully request your thoughts.

TIA
 
Jerry -

I tried several thingds, but I cannot replicate your problem. Are the
dates actually numeric (right aligned within the cells)? How are you
hiding the cells?

- Jon
 
Jon,

Thanks for the reply. This problem is puzzling. After posting last
night I tried a few things with the original spreadsheet. I took the
last ten rows and and created a new x-y chart. I found that the
problem will occur when I hide specific rows of data. For example, a
column 10 dates starting at 1/1/04 thru 1/10/04 paired to a
corresponding column of accounting formatted figures $1 thru 10.

I can successfully hide the first row or the second row or the tenth
row and produce an accurate graph. If I hide any row from the 3rd to
9th, the date on the x-axis of my chart changes to 1/0/00 thru
1/10/00. If I format the date axis to fix the date range on the
chart,-- the data disappears. This little anomaly I understand. I
want data for dates in the year 2004, but the data is changed to the
year 1900. No data for 1900, no plot.

I created a new spreadsheet with two columns of data as described
above and was able to repeatable recreate the phenomena. There
appears to be some type of position-sensitive malfunction here.
Whenever I hide the third visible row in the series the chart
malfunctions. Even stranger, when I hide the second row, I can hide
any other row without malfunction. No theory yet …still thinking.

To answer your question about formatting; I did check the format for
the cells and am currently using the 1/01/04 configuration. (I
understand this is fussy—spend hours once before I found a month with
one-too-many-days in it.:) I tried changing the alignment using the
tool bar buttons—left, center, or right, didn’t cure the problem.
Checked the format for a single cell by right clicking, alignment,
right, also without solving the problem. I’m hiding the cells by
selecting the row, then right clicking, then hide. I also tried
using the Format at the top of the page but the results are similar.

I have the sample spreadsheet if you’d like to look at it. Let me
know where to send it. The email name at the top of the page works
but is rarely checked.

Regards,

Jerry
 
Jon,

Thanks for all your efforts. I have installed an update to my Excel
program and it has cured my problem. I posting here so others may
learn from my experience. I was under the impression that Windows
Update took care of all Microsoft products--guess not. Anyway sorry
to waste the bandwith and your time.

Jerry
 
Back
Top