Issue 38759 - ODFF: MIN/MINA MAX/MAXA should return 0 if all parms are empty
Summary: ODFF: MIN/MINA MAX/MAXA should return 0 if all parms are empty
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 1.0.3
Hardware: All All
: P4 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: ms_interoperability
: 63177 (view as issue list)
Depends on:
Blocks: 72764
  Show dependency tree
Reported: 2004-12-09 17:22 UTC by jldavid
Modified: 2013-08-07 15:15 UTC (History)
10 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

Patch. (2.30 KB, patch)
2006-03-13 14:21 UTC, muthusuba
no flags Details | Diff
Patch (4.50 KB, text/plain)
2007-12-10 07:20 UTC, lvyue
no flags Details
Patch (3.96 KB, text/plain)
2007-12-12 05:26 UTC, lvyue
no flags Details
Patch3 from lvyue (3.77 KB, text/plain)
2007-12-18 07:14 UTC, lvyue
no flags Details
Patch4 (2.99 KB, text/plain)
2008-01-07 09:02 UTC, lvyue
no flags Details
TestCaseSpecification (6.94 KB, text/html)
2008-02-26 09:53 UTC, oc
no flags Details
Testdocuments for Test Case Specification (9.05 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-02-26 09:53 UTC, oc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jldavid 2004-12-09 17:22:57 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.
Comment 1 frank 2004-12-10 08:30:51 UTC
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.

Comment 2 abdul 2005-01-07 10:06:34 UTC
Confirming the issue..
Comment 3 oc 2005-01-17 10:58:51 UTC
Changing the current behaviour has to be discussed by User Experience
Comment 4 jodygoldberg 2006-01-02 20:52:10 UTC
This is an MS Excel compatibility issue.
Muthu is working on a patch.
Comment 5 muthusuba 2006-03-13 14:21:58 UTC
Created attachment 34828 [details]
Comment 6 hwoarang 2006-03-15 02:00:47 UTC
*** Issue 63177 has been marked as a duplicate of this issue. ***
Comment 7 kendy 2006-03-16 10:48:18 UTC
This is a patch, set it as such... 
Comment 8 kendy 2006-03-16 10:51:03 UTC
Re-assign to the module owner. 
Comment 9 niklas.nebel 2006-03-17 14:36:12 UTC
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).
Comment 10 niklas.nebel 2006-06-23 17:22:46 UTC
The previous comment is still valid, but the issue's target should be 2.x, not 3.0.
Comment 11 muthusuba 2007-02-24 12:11:52 UTC
  (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).

Comment 12 niklas.nebel 2007-05-23 17:16:03 UTC
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.
Comment 13 niklas.nebel 2007-09-06 19:08:55 UTC
muthusuba, are you still involved with this? I see it as secondary at the
moment, as it's not needed for ODF formula implementation.
Comment 14 muthusuba 2007-09-07 06:18:20 UTC

  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)

Comment 15 vhshah 2007-11-10 10:06:02 UTC
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.
Comment 16 jldavid 2007-11-10 13:16:47 UTC
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.
Comment 17 Regina Henschel 2007-11-29 10:43:15 UTC
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.
Comment 18 kyoshida 2007-11-29 14:26:15 UTC

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. :-)
Comment 19 jldavid 2007-11-29 14:29:04 UTC
> 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.
Comment 20 ooo 2007-11-29 17:21:47 UTC
@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.
Comment 21 niklas.nebel 2007-12-04 18:25:35 UTC
Target 3.0
Comment 22 lvyue 2007-12-10 07:20:03 UTC
Created attachment 50197 [details]
Comment 23 ooo 2007-12-10 11:56:21 UTC
@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.

Comment 24 ooo 2007-12-10 12:01:08 UTC
Grabbing issue, ODFF relevant, settting ms_interoperability keyword.
Comment 25 muthusuba 2007-12-10 13:19:45 UTC
 do you still want me to add the matrix part? i can look at it today.

Comment 26 ooo 2007-12-10 13:53:09 UTC
@muthusuba: Thanks, but now leave that to Lv Hue please as an exercise.
Comment 27 ooo 2007-12-10 13:59:20 UTC
@muthusuba: Thanks, but now leave that to Lv Hue please as an exercise.
Comment 28 muthusuba 2007-12-10 14:43:10 UTC
@er: sure!
Comment 29 lvyue 2007-12-12 05:26:04 UTC
Created attachment 50249 [details]
Comment 30 lvyue 2007-12-18 07:14:34 UTC
Created attachment 50407 [details]
Patch3 from lvyue
Comment 31 maxx99 2008-01-02 13:39:10 UTC
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.
Comment 32 lvyue 2008-01-07 09:02:54 UTC
Created attachment 50697 [details]
Comment 33 ooo 2008-01-08 15:46:47 UTC
Yeah, that's it! Committed to cws odff:


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.
Comment 34 aliby 2008-02-10 15:53:14 UTC
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".
Comment 35 ooo 2008-02-12 12:50:52 UTC
@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.
Comment 36 ooo 2008-02-18 17:16:46 UTC
Reassigning to QA for verification.
Comment 37 oc 2008-02-26 09:53:03 UTC
Created attachment 51717 [details]
Comment 38 oc 2008-02-26 09:53:41 UTC
Created attachment 51718 [details]
Testdocuments for Test Case Specification
Comment 39 oc 2008-02-26 09:54:26 UTC
verified in internal build cws_odff
Comment 40 oc 2008-05-23 11:05:36 UTC
closed because fix available in OOH680_m15 and DEV300_m13