Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charting Question re data
Hello:
I have set-up a chart that utilizes the techniques described in previous postings regarding Dynamic Charting within Excel (using the non-VBA technique of data labels utilizing the Offset formula). In a prior post a list member described the use of the NA() formula as part of an IF() statement to eliminate non-blank data from appearing on the chart if no data was present. However I am now left with one issue that I need to resolve to make this chart fully usable - being that my X-axis label shows the future periods with no data (in fact I used the same formula and end up with #NA showing on the X-axis where no data is present). Any suggestions on how to resolve this issue? Thanks, Paul |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charting Question re data
Are you using OFFSET formulas with COUNT inside to count how long the series
should be? This will prevent nonnumeric data from even getting into the chart. Keep in mind that the NA() trick only works for marker-type series (line, XY, and I think Radar), not column and area types. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "PS" wrote in message ups.com... Hello: I have set-up a chart that utilizes the techniques described in previous postings regarding Dynamic Charting within Excel (using the non-VBA technique of data labels utilizing the Offset formula). In a prior post a list member described the use of the NA() formula as part of an IF() statement to eliminate non-blank data from appearing on the chart if no data was present. However I am now left with one issue that I need to resolve to make this chart fully usable - being that my X-axis label shows the future periods with no data (in fact I used the same formula and end up with #NA showing on the X-axis where no data is present). Any suggestions on how to resolve this issue? Thanks, Paul |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charting Question re data
If you chart has X-axis for the future points you can enter =NA() for the
y-values of these points. The dynamic chart approach is generally used when the user adds both x- and y-values and want the chart extended. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "PS" wrote in message ups.com... Hello: I have set-up a chart that utilizes the techniques described in previous postings regarding Dynamic Charting within Excel (using the non-VBA technique of data labels utilizing the Offset formula). In a prior post a list member described the use of the NA() formula as part of an IF() statement to eliminate non-blank data from appearing on the chart if no data was present. However I am now left with one issue that I need to resolve to make this chart fully usable - being that my X-axis label shows the future periods with no data (in fact I used the same formula and end up with #NA showing on the X-axis where no data is present). Any suggestions on how to resolve this issue? Thanks, Paul |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charting Question re data
Jon:
Thanks for your quick response you're feedback is helpful unfortunately here is the dilema: 1) Its a Line chart with series data 2) I'm using a CountA formula within the Offset Formula The labels for the X-axis are in a non-numeric format (e.g. Q206, Q3/06) So without the NA() trick I'm still getting an X-axis label but with no data (thanks to the NA() function trick). I'm hoping to iron out the wrinkles in this process since I have a significant number of charts to update. Also once I've worked out this issue would it be worth trying to learn enough VBA to do this process vs. the time consuming process of defining a large number of name ranges. Thanks, Paul Jon Peltier wrote: Are you using OFFSET formulas with COUNT inside to count how long the series should be? This will prevent nonnumeric data from even getting into the chart. Keep in mind that the NA() trick only works for marker-type series (line, XY, and I think Radar), not column and area types. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charting Question re data
So do you not want the category labels when there's no data? Use COUNT() to
define the length of the Y values range, then base the X values range on this: Name: YValues RefersTo: =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) Name: XValues RefersTo: =OFFSET(YValues,0,-1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "PS" wrote in message ups.com... Jon: Thanks for your quick response you're feedback is helpful unfortunately here is the dilema: 1) Its a Line chart with series data 2) I'm using a CountA formula within the Offset Formula The labels for the X-axis are in a non-numeric format (e.g. Q206, Q3/06) So without the NA() trick I'm still getting an X-axis label but with no data (thanks to the NA() function trick). I'm hoping to iron out the wrinkles in this process since I have a significant number of charts to update. Also once I've worked out this issue would it be worth trying to learn enough VBA to do this process vs. the time consuming process of defining a large number of name ranges. Thanks, Paul Jon Peltier wrote: Are you using OFFSET formulas with COUNT inside to count how long the series should be? This will prevent nonnumeric data from even getting into the chart. Keep in mind that the NA() trick only works for marker-type series (line, XY, and I think Radar), not column and area types. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Uniquely Identify Data for Charting with a List Box | Charts and Charting in Excel | |||
Macro question | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Charting Data Series vs. Data Points? | Charts and Charting in Excel | |||
Charting with dynamic data | Charts and Charting in Excel |