How do you stop excel from charting empty cells/null values as zer

[Abe-air]

I'm trying to create several charts from my excel data. Several of the
chart's reference cell (established range) contains formuals. When the
cell's formula returns a null value, chart display's as zero. How do I setup
chart to display null as nothing versus zero?

 

[Kelly O'Day]

The workaround for charting calculated cells when you may have missing data
is to use an If formula that selects NA() if there is missing data.
Something like --

= If( d2 = "", NA(), b2*d2)

You'll need to replace your actual cell references and formula.

This formula will place a #N/A in those cells that can not be calculated
because of your missing data. Excel recognizes #N/A cells in charting and
ignores them.

I have a tutorial and sample file on this that may be helpful.

http://processtrends.com/pg_charts_missing_data.htm

....Kelly

(e-mail address removed)

 

[Daniel CHEN]

Try:
Go to menu Tools>Options>Active Chart, Plot Empty Cells as ...

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================

 

[Jon Peltier]

This only works for blank cells, not if a formula returns a zero-length
string. Kelly's suggestion covers this case.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______