Apache OpenOffice (AOO) Bugzilla – Issue 38759
ODFF: MIN/MINA MAX/MAXA should return 0 if all parms are empty
Last modified: 2013-08-07 15:15:24 UTC
A construct such as =MAX(A1:A3) or =MIN(A1:A3) will give an Err:502 if all of A1:A3 are blank. This behavior is inconsistent with other functions such as =SUM which will return a 0 if all the cells in the range are blank. =MAX (=MIN) and =MAXA (=MINA) should also simply return a 0. If you disagree that =MAX should return a 0, surely =MAXA should return a 0 as a blank cell should be treated as a text entry would be treated.
This is in no way a Prio 1 task. Prio 1 stays for not useable Application. Have a look at the help on Prioritys by clicking the Priority link above. Setting the Prio to a more realistic value. Frank
Confirming the issue..
Changing the current behaviour has to be discussed by User Experience
This is an MS Excel compatibility issue. Muthu is working on a patch.
Created attachment 34828 [details] Patch.
*** Issue 63177 has been marked as a duplicate of this issue. ***
This is a patch, set it as such...
Re-assign to the module owner.
The svSingleRef case fails if there is no ScBaseCell (possible if functions like INDIRECT are used). A range with only text cells is handled differently for svDoubleRef and svMatrix (of course, the issue title doesn't say anything about that case, so as long as you don't specify anything, you can always claim it's intended that way). Anyway, these changes that modify existing calculations' results have to be collected and combined with some way to ensure compatibility (a flag, version number, whatever - that isn't clear yet).
The previous comment is still valid, but the issue's target should be 2.x, not 3.0.
nn, (sorry to disturb you again.) I can update the patch if you can tell me what exactly the patch is missing. What I understand: 1. The svSingleRef case fails if there is no ScBaseCell (possible if functions like INDIRECT are used). Thanks, muthusuba
What I meant with svDoubleRef/svMatrix was that with your patch, MIN of an all-text cell range is 0, but MIN of an all-text matrix gives an error.
muthusuba, are you still involved with this? I see it as secondary at the moment, as it's not needed for ODF formula implementation.
nn, yes, i am still looking at it or rather it is there on my list of to-dos. i will try to look at this, this weekend. just to recollect: 'the patch misses the matrix part', right? (and probably 'indirect' cell cases) muthusuba
Any word on this? This still does not work in OpenOffice 2.3. We have chosen to make the shift from MS environment to Oo and have a bunch of files (100s) that use MAX function. All those files now show Err:502 in cells where MAX function is used where the reference is blank. If the patch is available, it will save me a lot of time to rectify the formula in all those files.
It will not only save vhshah a lot of work, it will save a LOT of people a lot of work. The response on this has been pitiful for such a trivial change. The responses by those who could change this is so typical of how software developers think rather than how users think. (And I know - I've worked as one and with software developers for almost 30 years). Just because YOU developers think it is unimportant shows a level of arrogance and ignorance of real user issues that is all too common. Open Office will NEVER be taken seriously by real-world decision makers as long as crap like this keeps happening. It has been almost THREE YEARS since I submitted this bug (and it IS a bug, not simply a "compatibility issue") and 20 MONTHS since a patch was proposed and still NOTHING has happened. This is trivial. Just fix it already.
I need negative votes. I disagree with this proposal. Empty cells are empty and are neither a number nor a text. The current behavior of MIN/MINA/MAX/MAXA is correct. Indeed it is inconsistent with SUM, but the error is in the SUM function. SUM should return an error in this situation. I think the behavior on empty cells should be discussed in UX project in general before changing anything.
@regina, One thing worth noting is that, even if we decide to return empty instead of value 0 on empty range input, we'll still need to have a version that returns 0 to maintain Excel compatibility. As much as we don't like to admit it, the rest of the world is massively dependent upon Excel compatibility for migrating from Excel to Calc, so we need to provide some kind of bridge for that. OTOH, for INDIRECT function, for instance, we _may_ opt to have two separate versions of the same function INDIRECT and INDIRECT_XL, so we could have two versions with slightly different behaviors. But keeping two versions of the same cell function requires more maintenance (i.e. doubles the amount of function name localization & of course the implementation code). Just a food for thought. :-)
> I need negative votes. I disagree with this proposal. Only because you are an ivory-tower theorist who knows absolutely NOTHING about how spreadsheets are used in the real world. > Empty cells are empty and are neither a number nor a text. That is correct. Therefore, at best they should be ignored in a calculation and not generate an error. What should really happen is that the behavior should be consistent with every other spreadsheet program that has ever been written. > The current behavior of MIN/MINA/MAX/MAXA is correct. No it isn't and runs counter to 25+ years of user spreadsheet experience. > Indeed it is inconsistent with SUM, but the error is in the SUM > function. SUM should return an error in this situation. What planet do you live on? This may be correct in some theoretical, ivory tower programming universe, but it runs counter to what EVERY spreadsheet from Visicalc to Lotus 1-2-3 to Quattro Pro to Excel has EVER done over the past 25+ years. There's a real good reason for that. In the real world, people design spreadsheets with blank cells to accommodate future data. These cells are manipulated with functions like SUM, AVERAGE, MIN, MAX, etc. As the data accumulates, it is often summarized. If these functions return errors when encountering blank cells, then the summaries contain errors instead of doing the logical thing, which is (usually) to ignore blank cells in calculations. The only issue is then what to do if a range contains ALL blank cells. This is not difficult. If you are summing blanks, it is logical FROM A USER PERSPECTIVE to return a 0. This is what Calc currently does and is consistent with every other spreadsheet ever written. It should be the same for MAX. For MAXA, the logical thing to do FROM A USER PERSPECTIVE would be to return a blank (or a 0, take your pick). > I think the behavior on empty cells should be discussed in UX > project in general before changing anything. Yes, and if you do keep in mind how others have done it before you. Breaking new ground for some theoretical reason is idiotic. This is simple and to think you have the "right" answer in the face of how developers have done it and how users have used it over the entire history of spreadsheets is ARROGANT and WRONG.
@jldavid: You completely missed the point. This issue is not about whether empty cells are to be ignored (they are already) or should generate an error. This issue is about the condition when there is no value cell content at all in the area referenced. Strictly spoken a minimum or maximum of nothing is not 0, it's just bad habit of "25+ years of user spreadsheet experience" that people expect it to be so. And, btw, there's no reason to get abusive and accuse helpful people of whatever.
Target 3.0
Created attachment 50197 [details] Patch
@lvyue: Thanks for the patch, but it looks like we need some clarification here. Maybe there was some misunderstanding because of confusing comments in this issue. There is no need to determine the count of cells or matrix elements with textual content or count of empty matrix elements, nor whether single arguments to the function refer an empty range/matrix. The extra aValIterWithText and nEmpty... are not needed. All that is required is to check whether there was any numeric value (or textual content if bTextAsZero==TRUE) during the entire iteration over all arguments (be it cells or matrix elements), and if there was none set the result to 0 instead of setting an error. Thanks Eike
Grabbing issue, ODFF relevant, settting ms_interoperability keyword.
er, do you still want me to add the matrix part? i can look at it today. thanks, muthusuba
@muthusuba: Thanks, but now leave that to Lv Hue please as an exercise.
@er: sure!
Created attachment 50249 [details] Patch
Created attachment 50407 [details] Patch3 from lvyue
I hope, this will never realised. SUM(<blanc cells>) may return a 0, because 0 is the counter of an empty set. But if there no values, how can the return of MAX/MIN be a value? The return should only be an Err:502 (or an ERR:519, aka #VAL). Otherwise you never know about the "0" as the true maximum or minimum of real values in the range or only all cells are empty. The same with MAXA/MINA, a cell without an number or a text has no value. The result of choosing one special value out of an empty set of values can never be a value. And number "0" is a value.
Created attachment 50697 [details] Patch4
Yeah, that's it! Committed to cws odff: sc/source/core/tool/interpr1.cxx 1.51.38.8 Slightly modified to adapt to new parameter loop present in the cws, cleaned up indenting and removed the unnecessary nTmpVal again, that wasn't needed.
This is actually an issue that nullifies compatibility between Excel and OOo. Fancy IF statements are required at present to ensure backwards compatibility. The argument against a fix remains academic, but the solution ensures that a formulae remains "error" free and can provide a solution even before all values are populated. The main frustration is compound MIN/MAX as the error is carried through. viz: =MIN(MIN(A1:A4),MIN(A10:A14)); will error if A10:A14 is empty even if A1:A4 has a valid solution. It seems to be an issue that can and has been resolved with Patches. Surely it doesn't have to wait until version 3.0? This bug and 71776 both confirm the need for a "Fix".
@aliby: 3.0 will be the next release, 2.4 already has code freeze and there is no other release in between scheduled. Regarding =MIN(MIN(A1:A4),MIN(A10:A14)), you can write that as =MIN(A1:A4;A10:A14), on the other hand your's is a perfect example why strictly spoken returning 0 is nonsense because with A10:A14 being empty your minimum now will be 0 no matter what the values in A1:A4 really were, if positve.
Reassigning to QA for verification.
Created attachment 51717 [details] TestCaseSpecification
Created attachment 51718 [details] Testdocuments for Test Case Specification
verified in internal build cws_odff
closed because fix available in OOH680_m15 and DEV300_m13