To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=65221
------- Additional comments from [email protected] Wed Apr 7 20:42:42 +0000 2010 ------- This defect is a major PITA. Compatibility with Excel aside, this is a MAJOR problem. Because of the way that OOo calculates empty cells with these functions, AFAIK there is NO WAY to accurately calculate the number of empty cells within a range if any of those cells contain an equation whose output is an empty string, i.e. ="". In Excel, this can be done one of TWO ways, using either COUNTBLANK(<Range>), or COUNTIF(<Range>,""). I started using COUNTBLANK originally so that spreadsheets which have to count blank/empty cells would be compatible with both Excel and OOo, since COUNTIF(<Range>,"") doesn't work at all in OOo. Now with more advanced spreadsheets which contain equations within these cell ranges, I've found that OOo is simply incapable of handling such simple equations, and I have been unable to find a way to make a spreadsheet that will calculate blank/empty cells correctly in OOo. So you see it is not simply a matter of OOo being incompatible with Excel; rather, it is a matter of OOo being absolutely incapable of performing such a (simple) calculation with its current function set. It is IMPOSSIBLE to count the number of blank cells (cells containing an empty string result ="" in addition to the truly empty cells containing absolutely nothing) using OOo. I've tried this in OOo 3.0 and 3.1, and there is NO WAY to do this as far as I can tell. If there is, please enlighten me. The earliest defect I found regarding this issue dates back to 2003. 7 years later it has yet to be addressed. COUNTIF(<Range>,"") doesn't work at all in OOo and always returns a result of 0. COUNTBLANK(<Range>) will only count cells if they have absolutely nothing in them. IMO COUNTBLANK() should also count cells where an equation returns an empty string "". In fact, it is what I would expect given the fact that by returning an empty string, you are effectively saying "This cell is empty." ISBLANK() returns FALSE for a cell containing the equation ="". I read the Help manual and understand this is the expected behavior of OOo due to the way it specifies 'blank' cells, but WHY? I'd like to know what exactly the usage case is for counting cells containing equations that return an empty string "" as NOT blank? The current behavior and specification does not make any sense whatsoever IMO. There needs to be some simple way to do this. Using a function that is already shared by Excel and OOo would be ideal, because most people use Excel and not OOo. Also, the behavior of Excel for COUNTBLANK() makes far more sense than OOo's behavior. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
