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

0
9/19/2006 8:41:59 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
560 Views

Similar Articles

[PageSpeed] 7

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" <paul_steep@scotiacapital.com> wrote in message 
news:1158698519.193360.212160@m7g2000cwm.googlegroups.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
> 


0
jonxlmvpNO (4558)
9/19/2006 9:23:53 PM
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" <paul_steep@scotiacapital.com> wrote in message 
news:1158698519.193360.212160@m7g2000cwm.googlegroups.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
> 


0
bliengme5824 (3040)
9/19/2006 9:30:57 PM
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
> _______

0
9/19/2006 9:40:52 PM
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" <paul_steep@scotiacapital.com> wrote in message 
news:1158702052.623051.240480@i3g2000cwc.googlegroups.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
>> _______
> 


0
jonxlmvpNO (4558)
9/20/2006 4:44:27 PM
Reply:

Similar Artilces:

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

Re: teach me how to send friends email
<jim.cooke@cox.net> wrote in message news:... > how do i send email? ...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

CSliderCtrl Dynamic Orientation
Is it possible to change the orientation of a slider after creation? I cannot figure anything out besides making a new window with a new slider. Any help would be greatly appreciated. Thanks, Greg I can change the slider control orientation using CWnd:ModifyStyle() but when I proceed to adjust the dimensions of the CSliderCtrl using SetWindowPos the slider vanishes even with a repaint. Any ideas? "GBayard" wrote: > Is it possible to change the orientation of a slider after creation? I > cannot figure anything out besides making a new window with a new slider. >...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Updating a chart Automatically
Hi, i update a spreadsheet with new data from Access everyweek. I have a chart that displays the last thrity days, how do i do this automatically, so i dont have to keep updating the chart manually ? Thanks in advance Jay Jay Richardson wrote: > Hi, i update a spreadsheet with new data from Access > everyweek. I have a chart that displays the last thrity > days, how do i do this automatically, so i dont have to > keep updating the chart manually ? > > Thanks in advance Jay See www.tushar-mehta.com, Dynamic Charts. Dave dvt at psu dot edu ...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

How do I set up a 98% baseline across my chart?
I am trying to show on a bar chart the baseline that represents SLA requirement of system up time of 98%. How do I do this? Hi, You can find information on adding a datum line here. http://peltiertech.com/Excel/Charts/AddLine.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Deb" <Deb@discussions.microsoft.com> wrote in message news:11C7073F-1AC3-4FFD-BE4B-88605C7E0EF8@microsoft.com... >I am trying to show on a bar chart the baseline that represents SLA > requirement of system up time of 98%. How do I do this? ...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Configure Microsoft Dynamics CRM for Outlook
While trying to configure Ms CRM for outlook throws an error message HTTP Status 401: Unauthorized.Can anyone put me out of this issue. ...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

Can't re-enter a previously deleted User ID
We changed the spelling of a User ID (applewicks to appelwicks) and then deleted it (since he couldn't remember his password and the button for password was greyed out so we couldn't change it.) And now we can't re-enter the same user ID even though it doesn't appear in the window any longer. Here is the error we get: ODBC SQL server driver: The log in appelwicks already exists. Thanks! I believe you have to delete the old ID through Enterprise Manager as well. "cliffs" wrote: > We changed the spelling of a User ID (applewicks to appelwicks) and then ...

China + Dynamics GP
We are opening a distribution center in China and was wondering if anyone else is currently using Dynamics GP and any form of EDI process with China. We are considering what would be the best way of handling. Due to security risks, we are wary of allowing RDP/term serv access. So one idea was to give them a laptop with GP/Sql Server/Integration Manager. Since they couldn't connect to us they would have a separate database. This would then involve exports and imports between the 2 company locations to a neutral site (such as FTP). Not sure yet if we will be mirroring the datab...

ANN: Optimizing the Performance of Microsoft Dynamics CRM 3.0 white paper
Hi, the White Paper "Optimizing the Performance of Microsoft Dynamics CRM 3=2E0" is now available: http://go.microsoft.com/fwlink/?LinkID=3D80916=20 Regards Nicolas F=FChrs ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

Excel 2007 Line Chart
Hello, Is it possible to configure a line chart in Excel 2007 to ignore the intervals and graph straight to the next value. For example if I have the periods: Jan with the value 1000 Feb with the value 900 March with the value 500 April with the value 0 May with the value 0 June with the value 0 I want the March value to drop directly from 500 to 0 ignoring the interval to April, I do not want a curved line it must drop directly to zero then the line is straight across to April. I have no idea if this is possible, any ideas? Thanks, Brett On Tue, 11 Oct 2011 15:40:45 -0700 (PDT), TyreDu...

Chart Linking
How can a cell in one workbook be linked to data in another workbook. Kenneth Hi try the following: - open both workbooks - in the target workbook enter the equatrion sign '=' - now select with your mouse the source cell in the other workbook and hit ENTER -- Regards Frank Kabel Frankfurt, Germany "Kenneth" <pby5acat@tpg.com.au> schrieb im Newsbeitrag news:40b4a089@dnews.tpgi.com.au... > How can a cell in one workbook be linked to data in another workbook. > Kenneth > > Or copy the source cell, select the target cell, choose Paste Special from the Ed...