| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Skipping Dates
Hello all,
I have a chart that is based on dates and I want to be able to skip dates that do not have any data associated with it. Example: A B 1/1/05 1 1/2/05 2 1/3/05 1 1/6/05 3 1/7/05 2 Currently, my X axis has the dates 1-31 and if there is a gap in dates, it plots the missing dates as zero. I want the chart to not not put the missing dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7, etc on the X axis. How do I do that? I have already tried changing the Chart options from Automatic to Time-Scale . Thanks for any help, Sharon Sharon |
|
#2
|
|||
|
|||
|
|
|
#4
|
|||
|
|||
|
Sharon -
Use a dynamic range, which grows as data is added to the range. Here are some examples and links: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Sharon wrote: Okay, we're on the right track now. That worked, but since my series is based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it wants to squish the first 25 lines into the first third of the chart and it plots zero for the cells 26-65. Is there a way I can get it to only plot where there is data? My data source ($BB$3:$BB$65) is a formula. Thanks, Sharon "Don Guillett" wrote: try chart optionsaxiscategory -- Don Guillett SalesAid Software "Sharon" wrote in message ... Hello all, I have a chart that is based on dates and I want to be able to skip dates that do not have any data associated with it. Example: A B 1/1/05 1 1/2/05 2 1/3/05 1 1/6/05 3 1/7/05 2 Currently, my X axis has the dates 1-31 and if there is a gap in dates, it plots the missing dates as zero. I want the chart to not not put the missing dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7, etc on the X axis. How do I do that? I have already tried changing the Chart options from Automatic to Time-Scale . Thanks for any help, Sharon Sharon |
|
#5
|
|||
|
|||
|
I tried that and it seems like it is what I need to do, but when I try to
enter the data source it gives me an error message saying that, "The reference is not valid. References for titles, values or sizes must be a single cell, row or column." My headers consist of rows 1-3 and my data starts on row 4. Cells A1:C2 are merged to create a title and cells A3, B3 and C3 are the headers for the data. I modified the formulas on your website to suit my data as follows: =OFFSET(RYGSeries1ChartValues, 0, -4) =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4) =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4) Do you know what I'm doing wrong here? Thanks so much, Sharon "Jon Peltier" wrote: Sharon - Use a dynamic range, which grows as data is added to the range. Here are some examples and links: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Sharon wrote: Okay, we're on the right track now. That worked, but since my series is based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it wants to squish the first 25 lines into the first third of the chart and it plots zero for the cells 26-65. Is there a way I can get it to only plot where there is data? My data source ($BB$3:$BB$65) is a formula. Thanks, Sharon "Don Guillett" wrote: try chart optionsaxiscategory -- Don Guillett SalesAid Software "Sharon" wrote in message ... Hello all, I have a chart that is based on dates and I want to be able to skip dates that do not have any data associated with it. Example: A B 1/1/05 1 1/2/05 2 1/3/05 1 1/6/05 3 1/7/05 2 Currently, my X axis has the dates 1-31 and if there is a gap in dates, it plots the missing dates as zero. I want the chart to not not put the missing dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7, etc on the X axis. How do I do that? I have already tried changing the Chart options from Automatic to Time-Scale . Thanks for any help, Sharon Sharon |
|
#6
|
|||
|
|||
|
You may have answered it:
"The reference is not valid. References for titles, values or sizes must be a single cell, row or column." =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4) =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4) These two formulas refer to ranges 4 columns wide (the last 4 within the parens). /sig Sharon wrote: I tried that and it seems like it is what I need to do, but when I try to enter the data source it gives me an error message saying that, "The reference is not valid. References for titles, values or sizes must be a single cell, row or column." My headers consist of rows 1-3 and my data starts on row 4. Cells A1:C2 are merged to create a title and cells A3, B3 and C3 are the headers for the data. I modified the formulas on your website to suit my data as follows: =OFFSET(RYGSeries1ChartValues, 0, -4) =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4) =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4) Do you know what I'm doing wrong here? Thanks so much, Sharon "Jon Peltier" wrote: Sharon - Use a dynamic range, which grows as data is added to the range. Here are some examples and links: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Sharon wrote: Okay, we're on the right track now. That worked, but since my series is based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it wants to squish the first 25 lines into the first third of the chart and it plots zero for the cells 26-65. Is there a way I can get it to only plot where there is data? My data source ($BB$3:$BB$65) is a formula. Thanks, Sharon "Don Guillett" wrote: try chart optionsaxiscategory -- Don Guillett SalesAid Software "Sharon" wrote in message ... Hello all, I have a chart that is based on dates and I want to be able to skip dates that do not have any data associated with it. Example: A B 1/1/05 1 1/2/05 2 1/3/05 1 1/6/05 3 1/7/05 2 Currently, my X axis has the dates 1-31 and if there is a gap in dates, it plots the missing dates as zero. I want the chart to not not put the missing dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7, etc on the X axis. How do I do that? I have already tried changing the Chart options from Automatic to Time-Scale . Thanks for any help, Sharon Sharon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using dates for x-axis values as string instead of creating a scale | Charts and Charting in Excel | |||
| Finding Dates in a date range | Excel Discussion (Misc queries) | |||
| Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
| due dates | New Users to Excel | |||
| I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
Linear Mode
