Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3 hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

Click on the chart
In the Chart Tools tabs open Design;
In the Data group, open Select Data
Bottom left corner of dialog open: Hidden and Empty Cells
Make your selection

I would be interested in why you refer to the use of NA() as a 'hack'
My experience suggests this to be the simper way
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jack Tripper" wrote in message
...
Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3
hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

Hi,

Regarding the =NA() comment: The use of this is mainly when one has a
formula that return a blank to one or more of the cells being plotted. You
generally don't just type =NA() in a cell in the data range, what you do is
modify a formula that might return "" so that it return NA when it would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because it is
not even applicable. Meaning if the formula returns "" it is not consider
empty by Excel. The empty cell are cells that really have nothing in them.

Cheers,
Shane Devenshire

"Jack Tripper" wrote:

Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3 hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

Hi guys,

I'm finding that I have to use IF(A1="","",A1) in columns where I need the
following formulas to operate while ignoring "blanks":
average() ,stdev(),skew(),and kurt()
I canot use NA() here because the above formulas fail upon encountering any
#N/As

I of course then find the same problem as you guys that these "" are
interpreted as zeros by Excel charts rather than blanks to be ignored.

Is there something other than "" or NA() that I could use in my
IF(A1="","",A1)
formulas so that both charts and the abobe statistical functions can work?

Thanks so much

"Shane Devenshire" wrote:

Hi,

Regarding the =NA() comment: The use of this is mainly when one has a
formula that return a blank to one or more of the cells being plotted. You
generally don't just type =NA() in a cell in the data range, what you do is
modify a formula that might return "" so that it return NA when it would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because it is
not even applicable. Meaning if the formula returns "" it is not consider
empty by Excel. The empty cell are cells that really have nothing in them.

Cheers,
Shane Devenshire

"Jack Tripper" wrote:

Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3 hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

The best bet is to use one series for your statistical functions and a
different series as source data for your chart.
--
David Biddulph

"AllSensibleNamesTaken"
wrote in message ...
Hi guys,

I'm finding that I have to use IF(A1="","",A1) in columns where I need the
following formulas to operate while ignoring "blanks":
average() ,stdev(),skew(),and kurt()
I canot use NA() here because the above formulas fail upon encountering
any
#N/As

I of course then find the same problem as you guys that these "" are
interpreted as zeros by Excel charts rather than blanks to be ignored.

Is there something other than "" or NA() that I could use in my
IF(A1="","",A1)
formulas so that both charts and the abobe statistical functions can work?

Thanks so much

"Shane Devenshire" wrote:

Hi,

Regarding the =NA() comment: The use of this is mainly when one has a
formula that return a blank to one or more of the cells being plotted.
You
generally don't just type =NA() in a cell in the data range, what you do
is
modify a formula that might return "" so that it return NA when it would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because it
is
not even applicable. Meaning if the formula returns "" it is not
consider
empty by Excel. The empty cell are cells that really have nothing in
them.

Cheers,
Shane Devenshire

"Jack Tripper" wrote:

Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3
hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

I have a followup question related to this thread - I created a second data
series, as David suggested, so Series 1 contains empty cells for missing data
points and Series 2 contains NA() (which displays as #N/A) for missing data
points. When I graph Series 1 with Tools-Options-Chart-Plot Empty Cells
set to "Not Plotted (leave gaps)" it leaves gaps in my line chart for the
empty cells as expected. However, when I copy the chart and point it at data
Series 2 (same data, just contains #N/A in place of empty cells) the graph
does not display gaps for data points with a value of #N/A. If data points 2
and 3 are missing, the graph connects point 1 to 4 as if it is interpolating
rather than "Not Plotted" which is the current setting. Do #N/A values in a
data series somehow override the Tools-Options-Chart settings? Is there a
value I can input into a calculated cell that plots on a graph exactly like
an empty cell would?

I am running Excel 2003 SP3. Thanks for any insights.

Phil Platt

"David Biddulph" wrote:

The best bet is to use one series for your statistical functions and a
different series as source data for your chart.
--
David Biddulph

"AllSensibleNamesTaken"
wrote in message ...
Hi guys,

I'm finding that I have to use IF(A1="","",A1) in columns where I need the
following formulas to operate while ignoring "blanks":
average() ,stdev(),skew(),and kurt()
I canot use NA() here because the above formulas fail upon encountering
any
#N/As

I of course then find the same problem as you guys that these "" are
interpreted as zeros by Excel charts rather than blanks to be ignored.

Is there something other than "" or NA() that I could use in my
IF(A1="","",A1)
formulas so that both charts and the abobe statistical functions can work?

Thanks so much

"Shane Devenshire" wrote:

Hi,

Regarding the =NA() comment: The use of this is mainly when one has a
formula that return a blank to one or more of the cells being plotted.
You
generally don't just type =NA() in a cell in the data range, what you do
is
modify a formula that might return "" so that it return NA when it would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because it
is
not even applicable. Meaning if the formula returns "" it is not
consider
empty by Excel. The empty cell are cells that really have nothing in
them.

Cheers,
Shane Devenshire

"Jack Tripper" wrote:

Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for 3
hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html

Hi,

Cells with formula are not empty so therefore the Option plot as zero does
not apply.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"pplatt" wrote in message
...
I have a followup question related to this thread - I created a second data
series, as David suggested, so Series 1 contains empty cells for missing
data
points and Series 2 contains NA() (which displays as #N/A) for missing
data
points. When I graph Series 1 with Tools-Options-Chart-Plot Empty Cells
set to "Not Plotted (leave gaps)" it leaves gaps in my line chart for the
empty cells as expected. However, when I copy the chart and point it at
data
Series 2 (same data, just contains #N/A in place of empty cells) the graph
does not display gaps for data points with a value of #N/A. If data
points 2
and 3 are missing, the graph connects point 1 to 4 as if it is
interpolating
rather than "Not Plotted" which is the current setting. Do #N/A values in
a
data series somehow override the Tools-Options-Chart settings? Is there
a
value I can input into a calculated cell that plots on a graph exactly
like
an empty cell would?

I am running Excel 2003 SP3. Thanks for any insights.

Phil Platt

"David Biddulph" wrote:

The best bet is to use one series for your statistical functions and a
different series as source data for your chart.
--
David Biddulph

"AllSensibleNamesTaken"
wrote in message
...
Hi guys,

I'm finding that I have to use IF(A1="","",A1) in columns where I need
the
following formulas to operate while ignoring "blanks":
average() ,stdev(),skew(),and kurt()
I canot use NA() here because the above formulas fail upon encountering
any
#N/As

I of course then find the same problem as you guys that these "" are
interpreted as zeros by Excel charts rather than blanks to be ignored.

Is there something other than "" or NA() that I could use in my
IF(A1="","",A1)
formulas so that both charts and the abobe statistical functions can
work?

Thanks so much

"Shane Devenshire" wrote:

Hi,

Regarding the =NA() comment: The use of this is mainly when one has
a
formula that return a blank to one or more of the cells being plotted.
You
generally don't just type =NA() in a cell in the data range, what you
do
is
modify a formula that might return "" so that it return NA when it
would
return "", for example =IF(A1="",NA(),A1)

In these situations you don't use the Plot empty cells option, because
it
is
not even applicable. Meaning if the formula returns "" it is not
consider
empty by Excel. The empty cell are cells that really have nothing in
them.

Cheers,
Shane Devenshire

"Jack Tripper" wrote:

Excel has the option under
Tools - Options - Chart tab

Plot empty cells has
- Not plotted (leave gaps)
- Zero
- Interpoloated

i cannot find the option in Excel 2007, after hunting off and on for
3
hours.

Where is it?

i've already dismissed the hack of filling values with the formula
=NA()

References
http://support.microsoft.com/kb/302672
http://j-walk.com/ss/excel/usertips/tip024.htm
http://www.excelforum.com/excel-char...ng-values.html