Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D 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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 _______ "Bill Carson" wrote in message ups.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 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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 _______ "Bill Carson" wrote in message oups.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 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 _______ "Bill Carson" wrote in message oups.com... 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. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 _______ "Bill Carson" wrote in message ps.com... 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. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 _______ "Bill Carson" wrote in message ups.com... 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
2D Chart - x scale label displays wrong
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel graphs line correctly, shows wrong value label? | Excel Discussion (Misc queries) | |||
text label on scale | Charts and Charting in Excel | |||
line chart displays wrong values | Charts and Charting in Excel | |||
Logarithmic Scale - Tic Label Disappearance | Charts and Charting in Excel | |||
IF function displays wrong amount | Excel Worksheet Functions |