Some questions about Excel 2007 Charts

[fujing1003]

1. In Excel 2003, the line chart won't draw the value of #N/A and
leaving it as an empty point. but in Excel 2007, it deal the value of
#N/A as zero. In such condition. I could not drawing some conditional
charts which could associate different scopes of value with different
series and format them differently.

2.the dynamic chart in Excel 2007 seems to be disappointed, I could
not combine the chart with form control. and the relative reference
names( such as data1=OFFSET(Sheet1!$B11,0,0,1,COUNTA(Sheet1!$1:$1)-1)
could not function too. maybe you could find it by the sample of John
Walkenbach's "define series based on active cell - F9.xls".

3.The mouse's double-clicking function couldn't work too. I used to
format chart element by double-clicking it and set the format options
in its format dialog box. but now I have to rely on ribbon or right-
clicking pop-up menu.

and the other questions is still there too. Could you give me any
suggestion, Thanks in advance.

Yours,fujing

 

[Bernard Liengme]

Q1: You are mistaken here. If a cell in the y-range contains a formula such
as =NA() [or something more complex like IF(test, value, NA()], XL2007
ignores the point and interpolates. You cannot use just the text #N/A in any
version of Excel and hope the chart knows what you mean.

Q2: Why even bother with OFFSET? Turn the chart data into an Excel Table
[see Help] and anything you add to the Table will appear in the chart. You
could do this with XL2003 by making a List.

Q3: Correct, double-click does not open the format dialog. I suppose I am
lucky that I got into the habit of right clicking. You can also use CTRL+1

best wishes

 

[Jon Peltier]

Fujing -

1. In Excel 2003, the line chart won't draw the value of #N/A and
leaving it as an empty point. but in Excel 2007, it deal the value of
#N/A as zero. In such condition. I could not drawing some conditional
charts which could associate different scopes of value with different
series and format them differently.

A quick test shows me that line charts in Excel 2007 and in Excel 2003 treat
#N/A in the same manner. If somehow the #N/A is entered as text ('#N/A or
="#N/A") rather than as an error value, it will be treated by the line chart
as zero.

If this is not the case, provide a more detailed description of the specific
use of #N/A.

2.the dynamic chart in Excel 2007 seems to be disappointed, I could
not combine the chart with form control. and the relative reference
names( such as data1=OFFSET(Sheet1!$B11,0,0,1,COUNTA(Sheet1!$1:$1)-1)
could not function too. maybe you could find it by the sample of John
Walkenbach's "define series based on active cell - F9.xls".

I also could not make this dynamic chart work. I will post a bug report.

3.The mouse's double-clicking function couldn't work too. I used to
format chart element by double-clicking it and set the format options
in its format dialog box. but now I have to rely on ribbon or right-
clicking pop-up menu.

The Microsoft Office team has decided that double clicking will not bring up
the format dialog, but will instead bring up what Excel guesses is the tab
you probably want. I find this guess is usually incorrect. I also think this
was an unfortunate change to the interface design, and hope they will revert
to a more logical and more expected behavior.

- Jon

 

[Jon Peltier]

Bernard -

Re Q2, the tables in Excel 2007 (and the lists in Excel 2003) are great if
what you are plotting is an entire table column. If you are using OFFSET to
define a portion of a column, for example, focusing on a range of dates
instead of the whole range, then the Table doesn't do it, and you must keep
dynamic ranges in your arsenal. Such dynamic ranges work fine in Excel 2007
charts.

More to the point, the situation the OP cited is John Walkenbach's dynamic
range name example which defines the row containing the active cell. These
names correctly define the ranges to be plotted in 2007, but the charts do
not plot them correctly. I've looked into this and submitted the bug to the
authorities.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Q1: You are mistaken here. If a cell in the y-range contains a formula
such as =NA() [or something more complex like IF(test, value, NA()],
XL2007 ignores the point and interpolates. You cannot use just the text
#N/A in any version of Excel and hope the chart knows what you mean.

Q2: Why even bother with OFFSET? Turn the chart data into an Excel Table
[see Help] and anything you add to the Table will appear in the chart. You
could do this with XL2003 by making a List.

Q3: Correct, double-click does not open the format dialog. I suppose I am
lucky that I got into the habit of right clicking. You can also use CTRL+1

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

1. In Excel 2003, the line chart won't draw the value of #N/A and
leaving it as an empty point. but in Excel 2007, it deal the value of
#N/A as zero. In such condition. I could not drawing some conditional
charts which could associate different scopes of value with different
series and format them differently.

2.the dynamic chart in Excel 2007 seems to be disappointed, I could
not combine the chart with form control. and the relative reference
names( such as data1=OFFSET(Sheet1!$B11,0,0,1,COUNTA(Sheet1!$1:$1)-1)
could not function too. maybe you could find it by the sample of John
Walkenbach's "define series based on active cell - F9.xls".

3.The mouse's double-clicking function couldn't work too. I used to
format chart element by double-clicking it and set the format options
in its format dialog box. but now I have to rely on ribbon or right-
clicking pop-up menu.

and the other questions is still there too. Could you give me any
suggestion, Thanks in advance.

Yours,fujing