Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
PS PS is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.charting
PS PS is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Uniquely Identify Data for Charting with a List Box Takeadoe Charts and Charting in Excel 1 July 15th 06 10:07 PM
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
Charting Data Series vs. Data Points? Tommy Z. Charts and Charting in Excel 4 November 30th 05 12:23 AM
Charting with dynamic data Jon Charts and Charting in Excel 5 February 18th 05 02:51 AM


All times are GMT +1. The time now is 10:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"