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
|
|||
|
|||
Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and as simple as it sounds, I can't seem to do it. At one point I did manage to get a true "Date" value there, but the date values were a month out in the past, compromising the visuals of the graph. So, how do I get the values of Column A (whether as a "Date" value or an "Asci" representation) to show up under (but "outside" of the graph -- i.e., as "labels") the corresponding Col. B and Col. C values? Thanks, Marc |
#2
|
|||
|
|||
I am assuming you mean that you want the dates to appear
as labels on the x-axis and that your problem is with the formatting of these dates. I don't know why your dates would show up 'a month in the past' but I will share a few things you can try to solve your problem: TRY 1. .. Right click inside the chart and select 'SOURCE DATA' .. In the dialogue box that appears, click on the 'SERIES' tab .. At the bottom is a box where you can select your 'Category (x) axis labels' - if you click on the little red arrow button, it will give you an opportunity to select the cells that contain your date data. .. Hit OK If your dates now look wrong, try one of the following: TRY 2 .. Select the cells in which your dates appear and format the cells with the correct date format [FORMAT/CELLS/NUMBER] If this does not work try this: TRY 3 .. Select the COLUMN in which your dates appear and select [DATA/TEXT TO COLUMNS] .. In the dialogue box that appears, select 'FIXED WIDTH' and press NEXT .. Ignore the next screen and press NEXT .. On the following screen, you can set the data format to date, by selecting the box that is labeled 'DATES' and picking a format from the drop down list. .. Hit ok DONT FORGET TO REFRESH YOUR GRAPH (sometimes it is set on manual update) If this doesn't solve the problem, I don't have a clue how to help. Sorry. -----Original Message----- I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and as simple as it sounds, I can't seem to do it. At one point I did manage to get a true "Date" value there, but the date values were a month out in the past, compromising the visuals of the graph. So, how do I get the values of Column A (whether as a "Date" value or an "Asci" representation) to show up under (but "outside" of the graph -- i.e., as "labels") the corresponding Col. B and Col. C values? Thanks, Marc . |
#4
|
|||
|
|||
None of these suggestions appear to have helped, although it may be operator
error. The "Date" column values are all formatted to MM/DD/YY and they correspond one-to-one with the two data points plotted on the graph, so that each "Date" value has only one Diastolic and one Systolic value -- there's no tricky data here. When I try selecting 'SOURCE DATA', clicking on the 'SERIES' tab, selecting 'Category (x) axis labels' and select the "Date" cells under my date column (with or without a "blank column A"), I end up with a straight line leading to a cluster of data at the end for both pressure values (and no "dates" under the graph as labels for each Systolic and Diastolic pressure point pair). |
#5
|
|||
|
|||
Do you mean blank column A or blank cell A1? Where does the line start, that ends in
a cluster of points? What's on the category axis, anyway? What does the series formula say? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Private Person wrote: None of these suggestions appear to have helped, although it may be operator error. The "Date" column values are all formatted to MM/DD/YY and they correspond one-to-one with the two data points plotted on the graph, so that each "Date" value has only one Diastolic and one Systolic value -- there's no tricky data here. When I try selecting 'SOURCE DATA', clicking on the 'SERIES' tab, selecting 'Category (x) axis labels' and select the "Date" cells under my date column (with or without a "blank column A"), I end up with a straight line leading to a cluster of data at the end for both pressure values (and no "dates" under the graph as labels for each Systolic and Diastolic pressure point pair). |
#6
|
|||
|
|||
"Jon Peltier" wrote in message ... Do you mean blank column A or blank cell A1? Where does the line start, that ends in a cluster of points? What's on the category axis, anyway? What does the series formula say? I tried it as my spreadsheet existed (Column A holds my "Date" dates) and with an inserted column w/o data, Column A (so that then my "Date" dates are in Column B). Selecting the Catagory X Axis Labels and highlighting the valid "Date" dates in Column A produces a formula of "=Sheet1!$A$3:$A$37". In it's simplest form, the first row of data contains "9/3/04" as a Date Type Formatted MM/DD/YY, the second row contains 143 as a Numeric, and the third row contains 76 as a numeric. The second row is 9/4/04, 151, 83. Third row 9/5/04, 163, 92, etc. with entries for every day of Sept. The Left Side Y Axis is labeled "Pressure" with Excel-generated values starting at Zero and going to 160 in increments of 20 with associated lines runing across the graph . Excel is providing it's own calculated gap between data points. The bottom is labeled "By Day". This is where I would like the actual Date dates to appear instead, one date per pressure value pair. Normally, the Chart contains a graph with two horizontal lines of plotted data. The top line is Systolic data, the bottom Diastolic. So for the first row of data, I would have a data point indicater ("Value") at 143 for the top (Systolic) graphed line and a DPI at 76 for the bottom (Diastolic) graphiced line. Then gap, data point top line at 151, bottom line at 83, gap, DP top line at 163, bottom line 92, etc. If I select Catagory X Axis Labels, select the dates in Column A, then the graph shows up with two graphed lines, the top one starting with a data point from the 4th Row (Series "Systolic", Point "9/6/04" Value "141") then a straight line (Series "Systolic" Point "9/3/04" w/no value) to the end of the graph with a blob of all of the remaining data points at the end of the line (however, the first point of the blob is Series "Systolic" Point "9/3/04" Value "143". The same for the bottom Diastolic line (except, obviously, with the Diastolic data). No "Date" values appear as labels for the X axis. |
#7
|
|||
|
|||
I still suspect your data, and nothing you described disproves the hypothesis. When
you select the cell that has "9/3/04", what does the formula bar show? If it doesn't show 9/3/2004 (a 4-digit year), your dates are not dates. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Private Person wrote: "Jon Peltier" wrote in message ... Do you mean blank column A or blank cell A1? Where does the line start, that ends in a cluster of points? What's on the category axis, anyway? What does the series formula say? I tried it as my spreadsheet existed (Column A holds my "Date" dates) and with an inserted column w/o data, Column A (so that then my "Date" dates are in Column B). Selecting the Catagory X Axis Labels and highlighting the valid "Date" dates in Column A produces a formula of "=Sheet1!$A$3:$A$37". In it's simplest form, the first row of data contains "9/3/04" as a Date Type Formatted MM/DD/YY, the second row contains 143 as a Numeric, and the third row contains 76 as a numeric. The second row is 9/4/04, 151, 83. Third row 9/5/04, 163, 92, etc. with entries for every day of Sept. The Left Side Y Axis is labeled "Pressure" with Excel-generated values starting at Zero and going to 160 in increments of 20 with associated lines runing across the graph . Excel is providing it's own calculated gap between data points. The bottom is labeled "By Day". This is where I would like the actual Date dates to appear instead, one date per pressure value pair. Normally, the Chart contains a graph with two horizontal lines of plotted data. The top line is Systolic data, the bottom Diastolic. So for the first row of data, I would have a data point indicater ("Value") at 143 for the top (Systolic) graphed line and a DPI at 76 for the bottom (Diastolic) graphiced line. Then gap, data point top line at 151, bottom line at 83, gap, DP top line at 163, bottom line 92, etc. If I select Catagory X Axis Labels, select the dates in Column A, then the graph shows up with two graphed lines, the top one starting with a data point from the 4th Row (Series "Systolic", Point "9/6/04" Value "141") then a straight line (Series "Systolic" Point "9/3/04" w/no value) to the end of the graph with a blob of all of the remaining data points at the end of the line (however, the first point of the blob is Series "Systolic" Point "9/3/04" Value "143". The same for the bottom Diastolic line (except, obviously, with the Diastolic data). No "Date" values appear as labels for the X axis. |
#8
|
|||
|
|||
Private Person sent a copy of his workbook, and I discovered the problem. His data
were true dates, between 9/3/04 and 10/7/04, except for the anomalous 9/6/02. So this point appeared all by itself at the beginning of the chart, and all the others were two years later, at the end of the chart. I suspected the data, but for the wrong reason. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Jon Peltier wrote: I still suspect your data, and nothing you described disproves the hypothesis. When you select the cell that has "9/3/04", what does the formula bar show? If it doesn't show 9/3/2004 (a 4-digit year), your dates are not dates. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Private Person wrote: "Jon Peltier" wrote in message ... Do you mean blank column A or blank cell A1? Where does the line start, that ends in a cluster of points? What's on the category axis, anyway? What does the series formula say? I tried it as my spreadsheet existed (Column A holds my "Date" dates) and with an inserted column w/o data, Column A (so that then my "Date" dates are in Column B). Selecting the Catagory X Axis Labels and highlighting the valid "Date" dates in Column A produces a formula of "=Sheet1!$A$3:$A$37". In it's simplest form, the first row of data contains "9/3/04" as a Date Type Formatted MM/DD/YY, the second row contains 143 as a Numeric, and the third row contains 76 as a numeric. The second row is 9/4/04, 151, 83. Third row 9/5/04, 163, 92, etc. with entries for every day of Sept. The Left Side Y Axis is labeled "Pressure" with Excel-generated values starting at Zero and going to 160 in increments of 20 with associated lines runing across the graph . Excel is providing it's own calculated gap between data points. The bottom is labeled "By Day". This is where I would like the actual Date dates to appear instead, one date per pressure value pair. Normally, the Chart contains a graph with two horizontal lines of plotted data. The top line is Systolic data, the bottom Diastolic. So for the first row of data, I would have a data point indicater ("Value") at 143 for the top (Systolic) graphed line and a DPI at 76 for the bottom (Diastolic) graphiced line. Then gap, data point top line at 151, bottom line at 83, gap, DP top line at 163, bottom line 92, etc. If I select Catagory X Axis Labels, select the dates in Column A, then the graph shows up with two graphed lines, the top one starting with a data point from the 4th Row (Series "Systolic", Point "9/6/04" Value "141") then a straight line (Series "Systolic" Point "9/3/04" w/no value) to the end of the graph with a blob of all of the remaining data points at the end of the line (however, the first point of the blob is Series "Systolic" Point "9/3/04" Value "143". The same for the bottom Diastolic line (except, obviously, with the Diastolic data). No "Date" values appear as labels for the X axis. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sort by date problem | scudooder | General Discussion | 6 | July 31st, 2004 12:56 AM |
Problem with date formats | Using Forms | 2 | July 14th, 2004 11:03 PM | |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |
Chart the Top 3 values | Ryan | Charts and Charting | 1 | December 12th, 2003 05:45 PM |
Formatting values on a chart axis | Tushar Mehta | Charts and Charting | 1 | November 24th, 2003 05:42 PM |