Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Graph by Minute
I have a Excel 2000 spreadsheet were samples were taken randomly through the
day over several months. Some days only a couple samples, other days samples were taken several times an hour, and there were a variety "samples" taken. I completed pulling into Access, but that created more problems. As there are some 20 "types" that are sampled. Time Type 1 Type 2 Type 3 3/25/01 22:00 166 3/25/01 22:08 166 3/26/01 0:00 23 25 3/26/01 2:00 155 6 3/26/01 3:00 17 25 3/26/01 6:15 157 11 3/26/01 8:00 17 25 3/26/01 9:00 47 3/26/01 9:15 3 25 3/26/01 9:25 47 3/26/01 9:30 55 3 9 3/26/01 9:50 55 3/26/01 10:30 92 24 3/26/01 11:30 114 What I now need to do is create a graph that includes all the samples, however when I format at the X axis, I only have options for Days, Months and Years I'm not being allowed to choose by minute. Any help would be appreciated. |
#2
|
|||
|
|||
Lori,
One option would be to add an additional column between the "time" and "type1" columns. The new column will become the X axis chart source. A formula that converts time entries to text will be contained in the new column. For example, insert a new column between the "time" and "type1" columns. Assuming your time entries are in column A, add this formula to the new column which should now be column B: =TEXT(A2,"mm/dd/yy h:mm") Copy the formula down the range. Then reference your chart to the text reference column B instead of the time reference column A. In other words, the chart source should now cover columns B, C, D, and E. Column A, where the real time entries reside, now become a reference column as opposed to a charted column. ---- Regards, John Mansfield http://www.pdbook.com "Lori" wrote: I have a Excel 2000 spreadsheet were samples were taken randomly through the day over several months. Some days only a couple samples, other days samples were taken several times an hour, and there were a variety "samples" taken. I completed pulling into Access, but that created more problems. As there are some 20 "types" that are sampled. Time Type 1 Type 2 Type 3 3/25/01 22:00 166 3/25/01 22:08 166 3/26/01 0:00 23 25 3/26/01 2:00 155 6 3/26/01 3:00 17 25 3/26/01 6:15 157 11 3/26/01 8:00 17 25 3/26/01 9:00 47 3/26/01 9:15 3 25 3/26/01 9:25 47 3/26/01 9:30 55 3 9 3/26/01 9:50 55 3/26/01 10:30 92 24 3/26/01 11:30 114 What I now need to do is create a graph that includes all the samples, however when I format at the X axis, I only have options for Days, Months and Years I'm not being allowed to choose by minute. Any help would be appreciated. |
#3
|
|||
|
|||
The only practical solution would be to create a XY Scatter chart.
For any other kind of chart, you can use John Mansfield's solution but each time sample will be spaced equidistant from its neighbors. The only other solution would be to create the correct spacing by hand. The required interval must accomodate all the time values you have. I wouldn't be surprised if the only such interval is 1 minute. So, if that is indeed the smallest acceptable interval, create: 3/25 22:00 3/25 22:01 3/25 22:02 3/25 22:03 ....you get the idea. Only those rows that correspond to real data would have data values in the other columns. Assuming your data set fits in the limit for the number of data points in an XL series (16,536 I believe) use a 'category' type for the x-axis and you will get what you want. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have a Excel 2000 spreadsheet were samples were taken randomly through the day over several months. Some days only a couple samples, other days samples were taken several times an hour, and there were a variety "samples" taken. I completed pulling into Access, but that created more problems. As there are some 20 "types" that are sampled. Time Type 1 Type 2 Type 3 3/25/01 22:00 166 3/25/01 22:08 166 3/26/01 0:00 23 25 3/26/01 2:00 155 6 3/26/01 3:00 17 25 3/26/01 6:15 157 11 3/26/01 8:00 17 25 3/26/01 9:00 47 3/26/01 9:15 3 25 3/26/01 9:25 47 3/26/01 9:30 55 3 9 3/26/01 9:50 55 3/26/01 10:30 92 24 3/26/01 11:30 114 What I now need to do is create a graph that includes all the samples, however when I format at the X axis, I only have options for Days, Months and Years I'm not being allowed to choose by minute. Any help would be appreciated. |
#4
|
|||
|
|||
Tushar & John~
Thank you both, very helpful. The scatter graph. Da! I should've started there. ~Lori "Tushar Mehta" wrote: The only practical solution would be to create a XY Scatter chart. For any other kind of chart, you can use John Mansfield's solution but each time sample will be spaced equidistant from its neighbors. The only other solution would be to create the correct spacing by hand. The required interval must accomodate all the time values you have. I wouldn't be surprised if the only such interval is 1 minute. So, if that is indeed the smallest acceptable interval, create: 3/25 22:00 3/25 22:01 3/25 22:02 3/25 22:03 ....you get the idea. Only those rows that correspond to real data would have data values in the other columns. Assuming your data set fits in the limit for the number of data points in an XL series (16,536 I believe) use a 'category' type for the x-axis and you will get what you want. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have a Excel 2000 spreadsheet were samples were taken randomly through the day over several months. Some days only a couple samples, other days samples were taken several times an hour, and there were a variety "samples" taken. I completed pulling into Access, but that created more problems. As there are some 20 "types" that are sampled. Time Type 1 Type 2 Type 3 3/25/01 22:00 166 3/25/01 22:08 166 3/26/01 0:00 23 25 3/26/01 2:00 155 6 3/26/01 3:00 17 25 3/26/01 6:15 157 11 3/26/01 8:00 17 25 3/26/01 9:00 47 3/26/01 9:15 3 25 3/26/01 9:25 47 3/26/01 9:30 55 3 9 3/26/01 9:50 55 3/26/01 10:30 92 24 3/26/01 11:30 114 What I now need to do is create a graph that includes all the samples, however when I format at the X axis, I only have options for Days, Months and Years I'm not being allowed to choose by minute. Any help would be appreciated. |
#5
|
|||
|
|||
To anyone who's keeping score, the limits are 32,000 points in a series and 256,000
points in the entire chart. I wish I didn't know that. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: The only practical solution would be to create a XY Scatter chart. For any other kind of chart, you can use John Mansfield's solution but each time sample will be spaced equidistant from its neighbors. The only other solution would be to create the correct spacing by hand. The required interval must accomodate all the time values you have. I wouldn't be surprised if the only such interval is 1 minute. So, if that is indeed the smallest acceptable interval, create: 3/25 22:00 3/25 22:01 3/25 22:02 3/25 22:03 ....you get the idea. Only those rows that correspond to real data would have data values in the other columns. Assuming your data set fits in the limit for the number of data points in an XL series (16,536 I believe) use a 'category' type for the x-axis and you will get what you want. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bar graph values keep changing | Charts and Charting in Excel | |||
Graph Axes | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
convert 100 minute hour to a 60 minute hour | Excel Worksheet Functions |