Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you stop excel from charting empty cells/null values as zer
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you stop excel from charting empty cells/null values as zer
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 "Abe-air" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you stop excel from charting empty cells/null values as zer
Try:
Go to menu ToolsOptionsActive 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 ================================= "Abe-air" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you stop excel from charting empty cells/null values as zer
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 _______ "Daniel CHEN" wrote in message ... Try: Go to menu ToolsOptionsActive 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 ================================= "Abe-air" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in excel..:can't empty clip are" but already empty | Excel Discussion (Misc queries) | |||
How to stop Excel from automatically selecting cells? | Excel Discussion (Misc queries) | |||
Stop text from stringing into next field when empty | Excel Worksheet Functions | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Missing values in Excel Line Chart | Charts and Charting in Excel |