Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
I'm sure this question has been asked before but I have never been able to
find a satisfactory answer - I have tired everything and I am beginning to think it cannot be done and it may be a bug(or feature) of excel. I am using Microsoft Excel 2002. I have been keeping an excel spreadsheet to record my weight loss/gain i.e. two main columns: "date", "pounds". The date column is formatted as "date" and the pounds column is formatted as "number". My records go back for over 3 years and the entries are not regular; for example in 2004 I went through a stage of recording my weight most days but in 2005 there is about a 6 month gap where I do not make any entries at all. I have used the scatter graph to try and display my weight fluctuation over time but the problem is that the the x-scale (date) axis 2004 readings take up over half the axis because there are more entries for 2004 than for 2005/06. i.e. the date axis is not scaling properly. I have noticed that on smaller graphs (for example only 10 entries of date against pounds) the results display as expected and the time is to scale, but when you get to larger graph (like mine) I feel as if Excel is not able to recognise dates as numbers but only as discrete entries. So when I first started to use Excel for these purposes, and there were only a small number of entries, the graph did actually display the date axis to the correct scale. But not any more ^_^ So, is this a known bug/feature of Excel and/or is there an easy way round it? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
I wonder if the problem is to do with any extra columns that you might have in your dataset. Does your graph only use the two main columns or is the source data set to a larger area. If its the latter, you could try just using the main two. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=571127 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
"mrice" wrote in message ... I wonder if the problem is to do with any extra columns that you might have in your dataset. Does your graph only use the two main columns or is the source data set to a larger area. If its the latter, you could try just using the main two. I am selecing the 2 columns (date and pounds) and then creating the scatter graph for only these 2 columns. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
On Sun, 13 Aug 2006 08:34:23 +0100, "Mary Walker" wrote:
I'm sure this question has been asked before but I have never been able to find a satisfactory answer - I have tired everything and I am beginning to think it cannot be done and it may be a bug(or feature) of excel. I am using Microsoft Excel 2002. I have been keeping an excel spreadsheet to record my weight loss/gain i.e. two main columns: "date", "pounds". The date column is formatted as "date" and the pounds column is formatted as "number". My records go back for over 3 years and the entries are not regular; for example in 2004 I went through a stage of recording my weight most days but in 2005 there is about a 6 month gap where I do not make any entries at all. I have used the scatter graph to try and display my weight fluctuation over time but the problem is that the the x-scale (date) axis 2004 readings take up over half the axis because there are more entries for 2004 than for 2005/06. i.e. the date axis is not scaling properly. I have noticed that on smaller graphs (for example only 10 entries of date against pounds) the results display as expected and the time is to scale, but when you get to larger graph (like mine) I feel as if Excel is not able to recognise dates as numbers but only as discrete entries. So when I first started to use Excel for these purposes, and there were only a small number of entries, the graph did actually display the date axis to the correct scale. But not any more ^_^ So, is this a known bug/feature of Excel and/or is there an easy way round it? Excel should be scaling the dates at some regular date interval. Although with a line graph, you have the option of choosing a "time interval", even with a scatter graph, the axis should be scaled at some fixed number of days. I suspect a problem with your data. Possibly your "dates" are not really Excel dates. If you select a cell and change the format to "General", what happens to the contents of that cell? Excel stores dates as a serial number with 1 = 1/1/1900 or 1/1/1904. If you format a date cell as general, you should see a value of around 38,000. If you do not, then your values in your date column are not really dates. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
"Ron Rosenfeld" wrote in message ... On Sun, 13 Aug 2006 08:34:23 +0100, "Mary Walker" wrote: I'm sure this question has been asked before but I have never been able to find a satisfactory answer - I have tired everything and I am beginning to think it cannot be done and it may be a bug(or feature) of excel. I am using Microsoft Excel 2002. I have been keeping an excel spreadsheet to record my weight loss/gain i.e. two main columns: "date", "pounds". The date column is formatted as "date" and the pounds column is formatted as "number". My records go back for over 3 years and the entries are not regular; for example in 2004 I went through a stage of recording my weight most days but in 2005 there is about a 6 month gap where I do not make any entries at all. I have used the scatter graph to try and display my weight fluctuation over time but the problem is that the the x-scale (date) axis 2004 readings take up over half the axis because there are more entries for 2004 than for 2005/06. i.e. the date axis is not scaling properly. I have noticed that on smaller graphs (for example only 10 entries of date against pounds) the results display as expected and the time is to scale, but when you get to larger graph (like mine) I feel as if Excel is not able to recognise dates as numbers but only as discrete entries. So when I first started to use Excel for these purposes, and there were only a small number of entries, the graph did actually display the date axis to the correct scale. But not any more ^_^ So, is this a known bug/feature of Excel and/or is there an easy way round it? Excel should be scaling the dates at some regular date interval. Although with a line graph, you have the option of choosing a "time interval", even with a scatter graph, the axis should be scaled at some fixed number of days. I suspect a problem with your data. Possibly your "dates" are not really Excel dates. If you select a cell and change the format to "General", what happens to the contents of that cell? Excel stores dates as a serial number with 1 = 1/1/1900 or 1/1/1904. If you format a date cell as general, you should see a value of around 38,000. If you do not, then your values in your date column are not really dates. --ron Spot on ... you've fixed it :-) I'd been curious about this for ages - I just highlighted all the date cells and changed them all to general, as you said - oddly enough all the values changed to 38000 (ish) apart from one curious cell which remained as 11/12/05 - the format of this single cell must have thrown out all the data - don't know how/why it was different from the others but I just re-entered that cell as a new date and now everything works as expected. I won't make that mistake again :-) Many thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
On Sun, 13 Aug 2006 16:48:00 +0100, "Mary Walker" wrote:
Spot on ... you've fixed it :-) I'd been curious about this for ages - I just highlighted all the date cells and changed them all to general, as you said - oddly enough all the values changed to 38000 (ish) apart from one curious cell which remained as 11/12/05 - the format of this single cell must have thrown out all the data - don't know how/why it was different from the others but I just re-entered that cell as a new date and now everything works as expected. I won't make that mistake again :-) Many thanks. You're welcome. Glad to help. That one cell was somehow entered as text, rather than as a true date. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
date scale problem
Mary,
As far as I recall, the scatter graph always uses a value axis, which should always result in graph points placed proportional to the date. Rather than write (another) dissertation on Excel charts and date-time formatting, I'll ask this. Are you certain it's a scatter chart? If you're not, right click it, and choose "chart type." The chart type will be highlighted. We'll go from there. -- Earl Kiosterud www.smokeylake.com ------------------------------------------------------------------------- "Mary Walker" wrote in message ... I'm sure this question has been asked before but I have never been able to find a satisfactory answer - I have tired everything and I am beginning to think it cannot be done and it may be a bug(or feature) of excel. I am using Microsoft Excel 2002. I have been keeping an excel spreadsheet to record my weight loss/gain i.e. two main columns: "date", "pounds". The date column is formatted as "date" and the pounds column is formatted as "number". My records go back for over 3 years and the entries are not regular; for example in 2004 I went through a stage of recording my weight most days but in 2005 there is about a 6 month gap where I do not make any entries at all. I have used the scatter graph to try and display my weight fluctuation over time but the problem is that the the x-scale (date) axis 2004 readings take up over half the axis because there are more entries for 2004 than for 2005/06. i.e. the date axis is not scaling properly. I have noticed that on smaller graphs (for example only 10 entries of date against pounds) the results display as expected and the time is to scale, but when you get to larger graph (like mine) I feel as if Excel is not able to recognise dates as numbers but only as discrete entries. So when I first started to use Excel for these purposes, and there were only a small number of entries, the graph did actually display the date axis to the correct scale. But not any more ^_^ So, is this a known bug/feature of Excel and/or is there an easy way round it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Static Date and Time code problem | Excel Discussion (Misc queries) | |||
date problem in excel | Excel Worksheet Functions | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
Date Format problem | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) |