If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charting woes with hidden dates
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 |
#2
|
|||
|
|||
Charting woes with hidden dates
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 Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 |
#3
|
|||
|
|||
Charting woes with hidden dates
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 Peltier wrote in message ... 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 Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 |
#4
|
|||
|
|||
Charting woes with hidden dates
Jerry -
Deobfuscate the email in the most obvious way you can think of. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 Peltier wrote in message ... 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 Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 |
#5
|
|||
|
|||
Charting woes with hidden dates
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 Jon Peltier wrote in message ... Jerry - Deobfuscate the email in the most obvious way you can think of. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 Peltier wrote in message ... 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 Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ dustin pockets wrote: 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 |
Thread Tools | |
Display Modes | |
|
|