Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I stop graphs reading zero in Excel from cells with ""?
Hi.
I am making a model in Excel which is takes data updates from an outside source and then processes the information to develop charts on the data. There are a lot of charts so I have developed the model to process the data automatically as the data comes (down the sheet). This processing means that there are formulas in place, waiting for the data to come in. I use a =if(a1="","",<operation) notation to do it for me. If there is data then the if statement is false and the data can be processed; if the data hasn't arrived yet then instead of getting an error message you get a "". This works fine and I have set up the graphs so that there is a little leeway for additional data to come in and for the information to fit in the array read by the chart. However, my problem is that when the data hasn't arrived yet and Excel encounters "" in a cell (i.e., the if statement is true), which to me means <blank or <empty, it reads it as a zero. Therefore my nice charts have a large vertical line back down to zero at the end of every data series. One option is to redo the arrays of the chart whenever I use the model but there are probably 200 charts so this is uneconomic! What I am looking for is a way to deal with the formulas so the Excel reads my "" as <empty rather than a zero. Has anyone come across this problem before? Does anyone know how I might solve this problem? Thanks for your help. Tom |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I stop graphs reading zero in Excel from cells with ""?
Crossposting is OK but unnecessary. Multiposting is a no-no
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ThomasStudd" wrote in message ... Hi. I am making a model in Excel which is takes data updates from an outside source and then processes the information to develop charts on the data. There are a lot of charts so I have developed the model to process the data automatically as the data comes (down the sheet). This processing means that there are formulas in place, waiting for the data to come in. I use a =if(a1="","",<operation) notation to do it for me. If there is data then the if statement is false and the data can be processed; if the data hasn't arrived yet then instead of getting an error message you get a "". This works fine and I have set up the graphs so that there is a little leeway for additional data to come in and for the information to fit in the array read by the chart. However, my problem is that when the data hasn't arrived yet and Excel encounters "" in a cell (i.e., the if statement is true), which to me means <blank or <empty, it reads it as a zero. Therefore my nice charts have a large vertical line back down to zero at the end of every data series. One option is to redo the arrays of the chart whenever I use the model but there are probably 200 charts so this is uneconomic! What I am looking for is a way to deal with the formulas so the Excel reads my "" as <empty rather than a zero. Has anyone come across this problem before? Does anyone know how I might solve this problem? Thanks for your help. Tom |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I stop graphs reading zero in Excel from cells with ""?
To not have it plotted, change your formula from "" to NA()
To avoid the #NA! from being seen, use conditional formatting on such cells to if the formula is = NA() then format the font color to be the same as the background color. Example: If it's cell C57 that has the formula in it to return NA() rather than the first box of Condition 1 would be "Formula Is", and the second box would be "=ISNA(C57)". Of course, you don't include the double quotes within the dialog box. Formula in C57 would be: =if(a1="",NA(),<operation) Also, when you have the chart selected, within ToolsOptionsChart, make sure you have it set to either NOT plot empty cells or to Interpolate empty cells. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "ThomasStudd" wrote in message ... Hi. I am making a model in Excel which is takes data updates from an outside source and then processes the information to develop charts on the data. There are a lot of charts so I have developed the model to process the data automatically as the data comes (down the sheet). This processing means that there are formulas in place, waiting for the data to come in. I use a =if(a1="","",<operation) notation to do it for me. If there is data then the if statement is false and the data can be processed; if the data hasn't arrived yet then instead of getting an error message you get a "". This works fine and I have set up the graphs so that there is a little leeway for additional data to come in and for the information to fit in the array read by the chart. However, my problem is that when the data hasn't arrived yet and Excel encounters "" in a cell (i.e., the if statement is true), which to me means <blank or <empty, it reads it as a zero. Therefore my nice charts have a large vertical line back down to zero at the end of every data series. One option is to redo the arrays of the chart whenever I use the model but there are probably 200 charts so this is uneconomic! What I am looking for is a way to deal with the formulas so the Excel reads my "" as <empty rather than a zero. Has anyone come across this problem before? Does anyone know how I might solve this problem? Thanks for your help. Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Merging cells in Excel 2003 | Excel Discussion (Misc queries) | |||
How do I stop expanding cells in excel | Excel Discussion (Misc queries) | |||
How to stop Excel from automatically selecting cells? | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) |