 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / October 2003error bar problem
I'm using excel 2000 I have a sheet that I want to plot column G vs Col P, Col P has a large number of blank cells. In the spreadsheet, when calculate the mean and standard deviation, excel is "smart" enough to ignore the blank cells. When I make the plot, excel is "too stupid" to realize those are "missing values" but rather treats them as 0 which is clearly not correct. so, 2 questions... Did I miss an option somewhere that I can use to specify what to do with blank cells when plotting? Can I do an "add series" in source data where there are multiple X values from the spreadsheet and a SINGLE Y value, also from the sheet (I know how to do it for a constant) Thanks Bruce  Signature+-------------------+---------------------------------------------------+ Bruce Bowler | Next to Sinatra, I have the most hostile press in 1.207.633.9600 | America. - Howard Cosell bbowler@bigelow.org | +-------------------+---------------------------------------------------+ Bruce - Are the "blank" cells really blank? Or are they formulas that return ""? If they are truly blank, then select Options from the Tools menu, click the Chart tab, and pick the "not plotted" option for Plot Blank Cells As.... If they are the result of a formula, change "" to NA() in the formula, which changes the pseudoblank to #N/A. This makes the chart look better, but it might hose your other formulas.. In this case, you may want to have two columns of formulas, one for subsequent calculations, the other for charting. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ > I'm using excel 2000 > [quoted text clipped - 15 lines] > Thanks > Bruce Jon, They are formulae, and yes, na() does "mess up" other formulae so isn't a practical solution. The 2 column solution, while it does work (and is what I'll do) is, IM(not so)HO, ugly... Why, oh why, can't microsoft "do it right"??? (don't answer that one :-) Bruce > Bruce - > [quoted text clipped - 34 lines] >> Thanks >> Bruce Signature+-------------------+---------------------------------------------------+ Bruce Bowler | Civilization is a race between education and 1.207.633.9600 | catastrophe. - H. G. Wells bbowler@bigelow.org | +-------------------+---------------------------------------------------+ > Jon, > > They are formulae, and yes, na() does "mess up" other formulae so isn't a > practical solution. The 2 column solution, while it does work (and is > what > I'll do) is, IM(not so)HO, ugly... You have one other option. In the other formulae that are messed up, precondition them like this: =IF(ISERROR(cell with N/A), <appropriate response for error condition>, <your formula>) SignatureDave dvt at psu dot edu |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.