 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / October 20072D Chart - x scale label displays wrong
Using Excel 2000, the chart displays systolic and diastolic blood pressure as a line graph, with date of measurement on the x axis and pressure on the y axis. There are 1049 values each for the systolic and diastolic pressures. If I allow the chart to automatically pick the maximum x axis date, it doesn't do it right, so not all the data is plotted. When I force the x axis scale to use the last date in the spreadsheet as the maximum, then it plots all the data, but now the x axis labels are wrong, in that they they are a half a year ahead of the dates in the spreadsheet, i.e. the last date is 10/9/2007 in the spreadsheet, but the last labeled x axis tic shows 3/4/08. When I open the "format axis" dialog it shows a date of 4/1/2008, where I had earlier entered the maximum date 10/9/2007, and checked the maximum control. I'd rather not have to program Excel to fix this, because unless I am entering something wrong in the dialog, Excel should be able to handle this. Thanks, B.C. How does it work if you plot the data on an XY chart instead? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > Using Excel 2000, the chart displays systolic and diastolic blood > pressure as a line graph, with date of measurement on the x axis and [quoted text clipped - 18 lines] > > B.C. NO - can't get it to work as an XY chart either. Is it possible I have gone over the limit for a chart with 1050 values? Would badly formatted data cause the problem? I have looked and looked at the data and don't see anything. Is there any macro that will check data or charts for errors? I am wondering if I should update my Excel with the Microsoft updates to see if it would make a difference? This is getting to be very frustrating. B.C. A chart series can accommodate 32000 points, so that's not the problem. I suspect your problem may be with bad data, such as non-numerical data (numbers stored as text, etc.). What I asked wasn't "does it work as an XY chart?", but "HOW does it work as an XY chart?". Could you describe how it fails in an XY chart? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > NO - can't get it to work as an XY chart either. Is it possible I > have gone over the limit for a chart with 1050 values? Would badly [quoted text clipped - 7 lines] > > B.C. Sorry for being so slow to respond, but I am extremely busy on other projects. FYI, the dates for the chart are entered into the spreadsheet as: 10/9/2007 10:00 AM (as an example) There is usually one measurement per day, and sometimes more, of course at different times. The dates range from 5/19/2005 to 10/9/2007. When I do an XY chart, and allow Excel to auto-pick the minimum and maximum date ranges, it plots the y intercept as 1/0/1900 and the last date on the axis as 4/14/1903. If I enter the minimum and maximum dates in the format axis dialog for the scale, then the y intercept is correct at 5/19/2005, but no data points or curves are plotted, i.e. the graph is blank. Doe this give you any clues? Thanks, B.C. 1/0/1900 is zero and 4/14/1903 is 1200 when viewed in General number format. Do you perhaps have 1100 or so points? If Excel does not recognize your X values as numbers (or valid date-time values, which are also numeric), it assigns X values of 1, 2, 3, etc. Test the values. Make the column wider than needed to view the date-time values, and change the horizontal alignment to General. Numbers are right aligned and text is left aligned. If your dates are not right aligned, then Excel will assign 1, 2, 3, as I described above. All you need is one bad value, or one seemingly blank cell in the range that contains something like a space character or other text. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > Sorry for being so slow to respond, but I am extremely busy on > other projects. [quoted text clipped - 19 lines] > > B.C. EUREKA!!!!! Thanks so much, I fixed it with your advice. I spread the date column out and found two wrong entries, one had spaces in front of the date and the other had a period instead of a colon in the time. After I fixed it, the chart now displays as expected. Thanks for sticking with me through this problem. B.C. It's always something dumb, isn't it? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > EUREKA!!!!! > [quoted text clipped - 6 lines] > > B.C. Yes, garbage in and out. Which leads to a question I thought of after I sent my last response. Will Excel warn the user if he enters an incompatible format into a column that is designated as date format? Apparently not, since it allowed entry of bad date values in my spreadsheet, in which I had set the format to be date in the format dialog. Do you know of any capability built into Excel to do data entry warnings, or perhaps any code that will parse entries and check for format errors? Thanks, B.C. You could use data validation to try to limit what is entered, and conditional formatting to try to highlight what doesn't belong. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > Yes, garbage in and out. Which leads to a question > I thought of after I sent my last response. Will Excel [quoted text clipped - 11 lines] > > B.C. Your forcing me to RTFM, but it was worth it. I now have applied data validation to the date column and found it very useful. This should reduce the bad data being entered. Thanks again for all your help. B.C. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.