After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 162682 - ROUNDDOWN() ROUNDUP() and others aren't Excel compatible
ROUNDDOWN() ROUNDUP() and others aren't Excel compatible
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: General
git master
Other All
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2005-01-01 19:55 UTC by Nick Lamb
Modified: 2005-02-13 15:50 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Excel test spreadsheet for rounding functions (82.50 KB, application/octet-stream)
2005-01-01 19:56 UTC, Nick Lamb
  Details
Gnumeric 1.2.x version of same test (7.11 KB, application/octet-stream)
2005-01-01 19:56 UTC, Nick Lamb
  Details
makes ROUNDUP() ROUNDDOWN() Excel compatible (2.15 KB, patch)
2005-01-24 03:15 UTC, Nick Lamb
none Details | Review

Description Nick Lamb 2005-01-01 19:55:14 UTC
Please describe the problem:
The Gnumeric 1.2.x documentation describes ROUNDDOWN() ROUNDUP and similar
functions as Excel compatible. They are not.

Steps to reproduce:
Either

1. Load the round.gnumeric file and examine its contents
2. Consult Excel documentation for confirmation

Or

1. Load the round.xls Excel 2003 test file (all tests pass on Excel 2003)
2. Recalculate using Gnumeric
3. Notice serious differences


Actual results:
The rounding functions are wrongly documented and their behaviour is far from ideal

Expected results:
These functions should behave as documented in Excel, with any differences
clearly identified in the Gnumeric documentation.

Does this happen every time?


Other information:
Comment 1 Nick Lamb 2005-01-01 19:56:06 UTC
Created attachment 35346 [details]
Excel test spreadsheet for rounding functions
Comment 2 Nick Lamb 2005-01-01 19:56:42 UTC
Created attachment 35347 [details]
Gnumeric 1.2.x version of same test
Comment 3 Andreas J. Guelzow 2005-01-02 07:33:32 UTC
Well, the test is somewhat misleading: testing equality for floating point
numbers???

I started looking at the `err' and immediately run into  H23=H24 ?
Well H23 is 1.900000000000001 and H24 is 1.900000000000000, so this is
considered a difference?
Comment 4 Nick Lamb 2005-01-03 12:44:49 UTC
Feel free to fix up the test with a small epsilon factor if it will make you
feel warm and fuzzy, there are lots of tests that still fail (even if we don't
include those where Gnumeric and Excel don't agree on the error code to return).

The ROUNDUP() and ROUNDDOWN() functions are what this bug is concerned with,
explicitly all the odd rows of K43:Q54 show non-epsilon mistakes.

However frankly the epsilon shouldn't be needed. There's no disagreement about
which of 1.900000000000001 and 1.9 is correct for this function, nor about how
you could calculate the correct one, and it's disgraceful if Gnumeric hides
behind "floating point numbers aren't exact" as an excuse in such cases. Feel
free to open a new bug, referencing this one but with a lower priority to cover
fixing such problems.
Comment 5 Morten Welinder 2005-01-03 15:19:35 UTC
> There's no disagreement about which of 1.900000000000001 and 1.9 is correct
for > this function

There is no "1.9" in the number system used by the cpu: 0.1 does not have a
finite binary representation.    Excel does not give you 1.9 -- it cannot.

Thus the choice is between 1.899999999999... and 1.900000000000...  Hopyfully
we only differ by one bit.

However, we obviously do have some problems with, for example, a zero second
argument.
Comment 6 Morten Welinder 2005-01-03 15:35:46 UTC
For the record, on sparc I have "1.9" shown in both H23 and H24, yet they do
not compare equal.  When either number is multipled by 10 (exact) the result
is 19 (exact).
Comment 7 Andreas J. Guelzow 2005-01-03 17:42:59 UTC
I personally think we should remove all references to XL compatibility. I think
it is more important to be correct, eg:

According to the test file XL has
CEILING(0.1,0) equal to 0.
Ceiling should never be less than its first argument, so 0 cannot be right!

On the other hand it seems that in XL
FLOOR(0.1,0) is #DIV/0
where 0 would make lot's of sense. (And Gnumeric in my mind is wrong too.)

So I think we should really only worry about the cases where Gnumeric is wrong,
whether or not it coincides with XL.
Comment 8 Nick Lamb 2005-01-04 15:24:31 UTC
Correctness is important, where Excel is actually wrong we should try to be
right (as has happened for some financial and statistical functions) and we
could expect that future Excel versions might do so too, where Excel is less
precise than we are, there is no reason to make Gnumeric less precise, and so on.

However the issue Andreas describes with CEILING(x,0) and FLOOR(x,0) isn't a
clear case of correctness, it's a corner case where Excel provides a clearly
documented rationale and Gnumeric provides... nothing. In such cases it makes
sense for Gnumeric to be consistent, that is consistent with other popular
spreadsheet programs such as Excel, because this is in line with the principle
of least surprise. If someone needs a rounding function that's not actually
included in Excel, let them by all means add it to Gnumeric under some other
function name. Don't call it CEILING or FLOOR, or ROUNDUP or ROUNDDOWN for that
matter.

Several times now I've been witness to arguments that essentially boil down to
"Excel compatibility is hard, let's not bother and hope users don't care". Well,
users do care. The person who caused me to file this bug isn't using Gnumeric
any more because he needs to load XLS files which use rounding, and he may have
to tell other people to stop using Gnumeric too because it's effectively
corrupting their data. We should diverge from XLS compatibility only where we're
absolutely sure it serves the users better.

Even if you disagree, please go fix ROUNDUP/ ROUNDDOWN and file a different bug
about your philosophy concerning Excel compatibility.
Comment 9 Nick Lamb 2005-01-04 15:46:13 UTC
Re: Numeric representation, Morten is right, this is actually an LSB error,
whereas I had thought (judging from the post I replied to) that it was an order
of magnitude larger. Therefore it's less serious than I had thought.

However I don't think it's hard to guess how to match Excel's correctness (and
their answer _is_ more correct, no doubt about that), and that's what we should
aim for. If you want to divide this epsilon correctness stuff off into a lower
priority bug, go right ahead as I said before.
Comment 10 Morten Welinder 2005-01-04 16:35:40 UTC
Here's the floor situation: The arguments are (exactly)

a1 = 1.9499999999999999555910790149937383830547332763671875
a2 = 0.1000000000000000055511151231257827021181583404541015625

i.e., the two closest representable numbers to the decimal arguments "1.95" and
"0.1".  The results are (exactly)

r_gnum = 1.9000000000000001332267629550187848508358001708984375
r_excel = 1.899999999999999911182158029987476766109466552734375

r_gnum is exactly 19 * a2.  That is the correct result.

It is Excel that is incorrect here.  Try floor(a1,a2) in Excel -- both numbers
are perfectly representable so there is no rounding errors involved -- and you
will still get r_excel above, i.e., Excel is not as accurate as it should have
been.
Comment 11 Morten Welinder 2005-01-04 16:58:12 UTC
Minor correction:

r_exact = 19*a2 = 1.9000000000000001054711873393898713402450084686279296875
r_gnum is the closests representable number to r_exact.
Comment 12 Andreas J. Guelzow 2005-01-04 19:09:03 UTC
Nick wrote:
"However the issue Andreas describes with CEILING(x,0) and FLOOR(x,0) isn't a
clear case of correctness, it's a corner case where Excel provides a clearly
documented rationale and Gnumeric provides... nothing."

This is not a corner case! It si simply a question of correctness We should
always have CEILING(x,n) >= x  and FLOOR(x,n) <= x. Anything else is
mathematical nonsense. CEILING and FLOOR have well established mathematical
meaning (from well before MS came around and implemented it in XL incorrectly.)

Similarly with ROUNDUP and ROUNDDOWN were XL provides mathematical nonsense for
negative numbers.
Comment 13 Helmut Wollmersdorfer 2005-01-04 22:24:31 UTC
Andreas wrote:
"Similarly with ROUNDUP and ROUNDDOWN were XL provides mathematical nonsense for
negative numbers."

I don't know if ROUNDUP and ROUNDDOWN has another meaning in English than the
corresponding words in German, but ABRUNDEN (=ROUNDDOWN) is defined in maths as
keeping the digit n-1, if digit[n]={0|1|2|4}, and AUFRUNDEN (=ROUNDUP) is
defined as adding 1 to the _digit_ n-1, if digit[n]={5|6|7|8|9}. This has
nothing to do with positive or negative signs in front of a number.



Comment 14 Nick Lamb 2005-01-05 05:08:16 UTC
"r_gnum is exactly 19 * a2.  That is the correct result."

I think you mean approximately rather than exactly, and that seems like a
particularly useless interpretation (the user enters decimal fractions, not IEE
754 floating point numbers) but I can live with it, at least for now.

Could you spare a similar amount of thought power to investigate INT(-1) ?

--------

"Similarly with ROUNDUP and ROUNDDOWN were XL provides mathematical nonsense for
negative numbers."

ROUNDUP() is a function which rounds away from zero, and ROUNDDOWN() rounds
towards zero. I don't think that's "mathematical nonsense" although it's
possible that with a bit of thought the functions could have been better named.

There is no two parameter function ceiling (or floor for that matter) in common
use for mathematics. Are you sure you understand what that second parameter does
in Excel?
Comment 15 Andreas J. Guelzow 2005-01-05 05:36:24 UTC
Nick wrote "There is no two parameter function ceiling (or floor for that
matter) in common use for mathematics. Are you sure you understand what that
second parameter does in Excel?"

Gnumeric's two parameter version of CEILING and FLOOR are straight forward
generalization of teh standard mathematical ones (which correspond to the case
of the second parameter being 1).

Are you sure you have any acceptable mathematical training? (I know that this is
a quite insulting statement, but so is your last one.)
Comment 16 Andreas J. Guelzow 2005-01-05 05:52:11 UTC
Just some observation:
on MS's support site:
"SQL Server and Excel both have a function called Ceiling(), which always rounds
fraction values up (more positive) to the next value."

-5 is _not_ more positive than -4.5 so they wish that ceiling would do the right
thing, but XL's CEILING apparently isn't doing that. 

Another quote from the same MS site:
"The Round() function is not implemented in a consistent fashion among different
Microsoft products for historical reasons."

And some further observation on the same site:
"Floating Point Limitations
All of the rounding implementations presented here use the double data type,
which can represent approximately 15 decimal digits.
Since not all fractional values can be expressed exactly, you might get
unexpected results because the display value does not match the stored value.
For example, the number 2.25 might be stored internally as 2.2499999..., which
would round down with arithmetic rounding, instead of up as you might expect.
Also, the more calculations a number is put through, the greater possibility
that the stored binary value will deviate from the ideal decimal value.
If this is the case, you may want to choose a different data type, such as
Currency, which is exact to 4 decimal places.
You might also consider making the data types Variant and use CDec() to convert
everything to the Decimal data type, which can be exact to 28 decimal digits."

So are the "Excel values" really the tru values Excel calculates or did your
formatting chnage them?

Comment 17 Andreas J. Guelzow 2005-01-05 06:03:48 UTC
In OpenOffice Calc 1.1
CEILING(-0.5;-1) evaluates as 0 !
(They do have an optional mode argument to force CEILING to provide the
incorrect XL value, CEILING(-0.5;-1;1) evaluates to -1.)
Comment 18 Nick Lamb 2005-01-05 13:52:28 UTC
For the Microsoft documentation you're consulting a citation would be helpful,
the quote doesn't sound like Excel documentation to me since it mentions several
non-Excel features like Variant and Decimal data types, SQL Server?

The "Excel values" are Paste Special... Values from the original Excel results,
the version of Excel used for testing was stated in the original bug report. The
equality test (without epsilon) applied in the XLS and Gnumeric files passes
with no problems on Excel.

You don't insult me, but it's not very productive to try. I don't have a degree
in Mathematics although my qualifications are in numerate disciplines. If I need
to be able to do complex integrations without machine assistance to file bug
reports against Gnumeric then I'm obviously in the wrong place, and I'm sure
Jody will let me know. I think you've yet to explain why you think CEILING(x,0)
has a clear non-zero answer, when CEILING(a,b) is defined to be foo times b
where foo is the smallest natural number such that the magnitude of foo times b
is larger than a. We cannot satisfy this constraint when b is zero and a is
non-zero, which is why I called it a corner case. Excel arbitrarily declares
(and unlike Gnumeric, documents) that the result is zero. An error return might
be acceptable, but I strongly recommend that Gnumeric be consistent with Excel
where possible instead.

Adding a three parameter CEILING() to toggle a behaviour compatible with MS
Excel could be an acceptable outcome, providing it was clearly documented and
the XLS import code modified to import functions correctly (ie setting the third
parameter to compatible). If OO.org has such a feature for some of these
rounding functions we might want to clone it for OO.org compatibility anyway.
Comment 19 Nick Lamb 2005-01-05 15:03:14 UTC
OK, I was able to find the quote for myself, it's part of the VBA/ SQL Server
documentation, please read the Excel documentation instead.
Comment 20 Morten Welinder 2005-01-05 15:11:14 UTC
Too much in one bug.

INT(-1) is now bug 163019.
Comment 21 Nick Lamb 2005-01-05 18:11:04 UTC
In comment 18 "foo times b is larger than a" should read larger than or equal
to, rather than just larger than. I'm sure Andreas will have guessed that, but
just in case...
Comment 22 Morten Welinder 2005-01-06 16:38:43 UTC
Andreas: there is an indepented value in following strange Excel behaviour
such as for rounding to zero precision.  It cuts down the number of Excel
workbooks that mysteriously do not work with Gnumeric.  Arguing over it is
a bit like griping over libc's pow(0,0)==1.

If we feel a function is bogus we can document it and if there is a use case
for it, provide a fixed version.
Comment 23 Andreas J. Guelzow 2005-01-06 21:20:24 UTC
Breaking a correctly behaving function and creating a fixed separate function
one does not help existing gnumeric files.

But of course some would consider it a good business decision to alienate
current users in favour of gaining new users.
Comment 24 Nick Lamb 2005-01-09 16:25:50 UTC
Andreas, if you want to change Gnumeric policy on compatibility this is the
wrong place, you should perhaps take it to the mailing list.

A week ago I filed a bug on behalf of an (ex-)Gnumeric user. I want to see that
bug fixed. If as a side effect we get a half-dozen extra rounding functions
(there are at least that many more in use in the world) and the documentation is
improved (e.g with examples), all the better.

BTW Thanks Morten for spinning off bug 163019 and fixing it, would you prefer
that I split the rest of this bug up in the same way?
Comment 25 Morten Welinder 2005-01-10 17:13:39 UTC
I've made FLOOR(0,0)==0.  That shouldn't offend anyone.
Comment 26 Andreas J. Guelzow 2005-01-13 06:44:17 UTC
Nick, my understanding of Gnumeric's policy is to be first of all correct and
only secondly compatible to XL. Breaking a correctly behaving function does not
fit into this.
Comment 27 Nick Lamb 2005-01-13 10:12:28 UTC
Please be more clear Andreas, we've discussed five or six functions in this bug,
but the bug itself is about ROUNDDOWN() and ROUNDUP() which are currently broken
because instead of rounding away from or towards zero, as they are defined, they
round towards negative or positive infinity which is arguably just as useful,
but wrong. If you meant some other function, please say so and if necessary file
a separate bug for cleanup.

Once again, feel free to file a bug tracking the implementation of more rounding
functions, but arbitrarily declaring the current ROUNDDOWN() and ROUNDUP()
behaviour as "correct" makes no sense.
Comment 28 Andreas J. Guelzow 2005-01-17 19:37:20 UTC
Nick,

"ROUNDUP function rounds a given number up." This means that the result must be
larger or equal to the argument. The current implementation behaves correctly.
You apparently wnat a function that rounds away from 0. ROUNDUP may be defined
that way in Xl, it is not in Gnumeric. As a Gnumeric user I object to the
definitions and implementation of existing gnumeric functions being changed
since they would break existing gnumeric fiiles.

Same for "ROUNDDOWN".

I agree that the statement "This function is Excel compatible." is a bug since
Excel does not appear to have a function with this definition.

Clear enough?
Comment 29 Nick Lamb 2005-01-18 15:26:56 UTC
Neither "rounds up" nor "must be larger or equal" are unambiguous for negative
numbers. To be quite sure I'm not mad I just asked a room of technical people
from a variety of backgrounds what they expected to happen if they put -1.1 into
a function called ROUNDUP()

A few expected -2, a few said -1, and the vast majority wanted to know what the
documentation said, "it says the function rounds a given number up" I told them,
smiling. They were not amused.

It turns out (examining CVS) that back in 1999 the same person who implemented
this function also added examples to the function text which made the problem
plain, but since there is no way to view these examples in the Gnumeric 1.2.x
series so far as I can see, this problem lay hidden.

And then I see someone reporting this same problem (and you responding, but
apparently not doing anything about it) back in June.

Perhaps it would satisfy both our expectations if Gnumeric retains the ROUNDUP
and ROUNDDOWN functions as deprecated for compatibility with existing
spreadsheets, while introducing four new rounding functions which are clearly
named and documented from the outset, two which round toward/ away from zero and
two which round toward negative and positive infinity respectively.

In this way...

1. Existing Gnumeric spreadsheets would still load and work as before
2. Users would be encouraged to explicitly choose what they want, rather than
using an ill-named function like ROUNDUP which has a 50% chance of doing
something they didn't expect no matter what we do (from my small sample)
3. Excel import and export would translate as appropriate (as far as I
understand Gnumeric handles this for a few other cases already).

If this is an acceptable way forwards (let me assure you that "document that
this function is arbitrarily incompatible with Excel" is not an acceptable fix)
then could you perhaps suggest what the four functions should best be called ?
Comment 30 Andreas J. Guelzow 2005-01-19 22:48:50 UTC
Nick, the problem I see with your sample of technical people is that many may be
polluted by their experience with EXCEL. 

If I ask any of my students whether a temperature change from -20C to -10C means
that the temperature went up or down, they all whole-heartedly agree that it
went up.
Comment 31 Nick Lamb 2005-01-20 01:12:00 UTC
Andreas, if you want to change Gnumeric policy on compatibility this is the
wrong place, once again you should perhaps take it to the mailing list.

Nearly three weeks ago I filed a bug on behalf of an (ex-)Gnumeric user. I still
want to see that bug fixed.
Comment 32 Morten Welinder 2005-01-20 01:48:33 UTC
The problem is that the description string says two things:

1. ROUNDUP(-1.1) == -2.
2. ROUNDUP(-1.1) == -1.

(1) in the form of XL compatibility, (2) in more or less plain English.
[And who knows what the translations say.]

We cannot keep those two promises, i.e., we are going to make someone upset.
People legitimately have workbooks using either interpretation.

You two need to stop trading insults and start getting a little more
construtive in terms of how we get out of this mess.

Nick: three weeks is peanuts in this game.  Doing things right, whatever that
is, can take time.
Comment 33 Morten Welinder 2005-01-20 01:58:36 UTC
One solution I have been toying with is...

1. Introduce ROUNDZERO and ROUNDINF.
2. Keep the current functionality of ROUNDUP and ROUNDDOWN.
3. Change the description of ROUNDUP and ROUNDDOWN to say that they are not
   Excel compatible, but that Gnumeric's ROUNDINF is compatible with Excel's
   ROUNDUP, etc.
4. Fix Excel import to make ROUNDUP in a .xls to ROUNDINF (etc).  (That is
   done simply by changing the name in the ordinal-name list, I think.)

Consequences...

A. foo.gnumeric keeps its semantics.
B. foo.xls (as saved by Excel) keeps its semantics.
C. foo.xls (as saved by Gnumeric) has its semantics changed to match what
   Excel would think of the same file.

Issues...

* What about a workbook that contains the new ROUNDUP and is being saved to
  xls format?
Comment 34 Nick Lamb 2005-01-20 14:56:40 UTC
It's been assumed up to this point by everyone, including me, that there
actually are Gnumeric users expecting the current behaviour of ROUNDUP() and
ROUNDDOWN() because Andreas has said that its more correct in some sense than
the Excel behaviour. However at least on Gnumeric 1.2.13, I get anomalies like

ROUNDDOWN(-1,0) = -2 and ROUNDUP(-1,0) = 0

My suspicion is that these functions have been basically useless up until now
for negative values (perhaps until Morten fixed bug 163019). Is that true?

If it is true then we've been on a wild goose chase, the original fix stands.
Gnumeric can simply follow Excel and existing Gnumeric spreadsheets which use
these functions on negative values will now get consistent, but different
results, instead of garbage as before. The documentation would need updating of
course.

If this problem is unique to 1.2.13, or was fixed soon afterwards, can someone
track down when it was fixed please?
Comment 35 Morten Welinder 2005-01-21 22:12:30 UTC
Bug 163019 caused lots of stupidity for integers.  All that really should be
gone by now.
Comment 36 Nick Lamb 2005-01-22 00:30:45 UTC
That's fine. I'm glad this has turned out to be as simple as I first expected.
How about the following changes (and their counterparts in ROUNDUP)

"ROUNDDOWN function rounds a given @number down."
becomes
"ROUNDDOWN function rounds a given @number towards zero."

"ROUNDDOWN(-3.3) equals -4.\n"
becomes
"ROUNDDOWN(-3.3) equals -3.\n"

and then in the implementation a branch something like

if (number > 0.0)
  return value_new_float (gnumeric_fake_floor (number * p10) / p10);
else 
  return value_new_float (gnumeric_fake_ceil (number * p10) / p10);

I think I got those the right way around - anyway as appropriate if there's a
cleaner way to get the same effect, I'm not completely familiar with this code.
The spreadsheets attached to this bug should help in testing.

I'm aware that there has now been a branch, presumably this fix would be
suitable for backporting to go with bug 163019.
Comment 37 Andreas J. Guelzow 2005-01-23 07:31:47 UTC
Changing the meaning of ROUNDDOWN to mean sometimes up and sometimes down will
create new bug reports from previous users. (And there are current gnumeric
users you are using ROUNDOWN.)
Comment 38 Nick Lamb 2005-01-24 03:15:04 UTC
Created attachment 36434 [details] [review]
makes ROUNDUP() ROUNDDOWN() Excel compatible
Comment 39 Nick Lamb 2005-01-24 03:46:14 UTC
The semantic argument was dispensed with earlier by asking real users. The
result was that their expectations are confused at best, so we should simply
clone Excel unless there are very good reasons not to, as well as ensuring our
documentation is as clear as possible.

The bug workload is not likely to be a problem, Gnumeric was previously broken
by any definition regarding negative inputs to ROUNDDOWN() and ROUNDUP() for
about five years. Yet those problems apparently weren't even detected until this
bug report was filed, therefore existing use of ROUNDUP() and ROUNDDOWN() in
this part of their domain must be very sparse or non-existent among existing
Gnumeric users.

I hereby volunteer to deal with any genuine upgrade compatibility Bugzilla bugs
arising from my suggested fix. I don't expect there to be any, but I want to
account for the concern Andreas has shown on this point. Simply assign any such
bugs to me and add a note pointing to this bug.

I've also attached the proposed patch in unified yada yada format.
Comment 40 Andreas J. Guelzow 2005-01-24 16:39:54 UTC
"The semantic argument was dispensed with earlier by asking real users." That is
simply incorrect. Our real users expect that down means down and up means up,
not that down means towards zero. 
Comment 41 Jody Goldberg 2005-01-27 04:04:40 UTC
Here are some items that seem clear
- The function names ROUNDUP and ROUNDDOWN are ambiguous
- Our docs about being XL compat were wrong
- In all versions before 1.4.2 the fake rounding for negatives was broken

Where does that leave us ?
For xls import we can easily map to functions which are XL compat.
xls export is less clear.  We can map the XL compat routines to their XL
counterparts, but there's not much recourse for expressions that use the round
to plus/minus infinity.  We'd also hit trouble for XL users, or newbies using XL
oriented manuals, that use the functions assuming that the same name means the
same semantics.

Changing the semantics to be XL compat is not a panancea either.  Existing
spreadsheets authored in gnumeric could depend on the current behavior.  The
fake rounding bugs weaken this somewhat, but not completely.

Morten : your #33 proposal seems like it would run into trouble with workbooks
that changed formats.   eg something that started as xls and became gnumeric we
wouldn't know to map.  Now would we know not to map a workbook written in
gnumeric and previously saved as xls.

Andreas : I don't see alot of utility in trying to assign new meanings to these
function names.  If we want a clearer name we need to use something completely
new to avoid confusion with the massive user and documentation base from XL.

As I see it we have a few options
1) Make round{up,down} XL compat and screw existing users that may have depended
on the pre-1.4.2 partially broken implementations

2) Replace the existing functions with 4 others with better names and some
mapping for xls/OO import export

3) Keep the existing names and incompatible implementation.  Remove the XL
compat flag, and provide some sort of tooltip when the function is entered.  We
want to support function argument tooltips anyway.

At this point (1) seems the most persuasive.  Although the bug in older versions
was small, I suspect that anyone rounding was likely rounding near the target
value, which is where the bug would bite.  Which leads me to think that there
weren't many users that actually cared.

Andreas : other than the names being poor, do you see any problems we have not
addressed ?
Comment 42 Andreas J. Guelzow 2005-01-27 23:45:40 UTC
One note:
"In all versions before 1.4.2 the fake rounding for negatives was broken"
is misleading, this only happened for values that were exactly at the rounding
limit, ie. for integers only in the 1 argument version. As a consequence these
functions have seen use also for negative values. They in fact worked _near_ the
target value: rounddown(-1.9999) was correctly -2. The bug only occurred at
exact integer values.

The problem with (1) is that I know quite a few users that have used the
gnumeric implementation of rounddown that will be disgusted with fix (1). The
bug fixed in 1.4.2 never seemed to crop up.

I also like to point out that we already have an XL-compatible rounddown
function. Well, at least for non-negative "digits". It is called trunc.
trunc(-1.534,1) is exactly what XL-compatible rounddown(-1.534,1) is supposed to do.

If for some reason (1) does become the favorite solution, then I think we should
bump the gnumeric file format version and convert all rounddown/roundup calls
stored in old files to the new gnumeric version, for example rnddown/rndup. It
would also be helpful if those XL-compatibility functions were to be moved into
their own plugin so that one could choose not to load the plugin, thereby avoid
to enter those erroneous functions. (I gather providing 2 implementations for
each of rounddown and roundup in different plugins would probably be a user
support nightmare, although it would allow us to address Bug  
96737 )
Comment 43 Nick Lamb 2005-01-28 04:45:21 UTC
There is no reason for anyone to be "disgusted" with this fix or to call the
Excel functions "erroneous" or "mathematical nonsense". Both the behaviour
Andreas prefers and Excel's behaviour are equally reasonable interpretations.

One way to think about rounding 1.9 up to 2 is that you're rounding up the
decimal fraction. This way of thinking agrees with Excel's behaviour. The other
way to think about it is to imagine that you're finding the next round number
"up", that is to say towards positive infinity, which is the behaviour Andreas
prefers. They both have the same results for positive values, but different
results on negative values.
Comment 44 Andreas J. Guelzow 2005-01-28 18:35:12 UTC
If XL's rounddown is so reasonable, perhaps you could explain why it needs both
a rounddown and trunc function? 
Comment 45 Nick Lamb 2005-01-28 19:48:04 UTC
Unfortunately I'm not one of the Excel developers, so I can't really speculate.
It would certainly be harder for users to guess that the "opposite" of ROUNDUP()
was TRUNC(), or vice versa.

There are often little inconsistencies in the design of complex software like
spreadsheets, take the single argument variant of FLOOR() in Gnumeric. You'd
expect that it would have a counterpart, a single argument variant of CEILING()
and that it would implement the mathematical function floor(x), but in fact
neither is true.

Instead we have CEIL() which take a single argument and implements the
mathematical function ceiling(x), and the single argument version of FLOOR()
which implements something akin to Excel's ROUNDDOWN(x). Go figure.
Comment 46 Andreas J. Guelzow 2005-01-28 20:07:04 UTC
FLOOR and CEILING unfortunately are XL functions and so they are apparently
supposed to behave strangely to be XL compatible. 

CEIL() of course implements the mathematical function ceiling(), unfortunately
the name ceiling isn't available. Thanks XL!

And thanks NIck for just making the argument why we shouldn't also try to make
rounddown/roundup XL compatible. 
Comment 47 Nick Lamb 2005-01-29 03:26:26 UTC
There are no single argument functions CEILING() and FLOOR() in Excel to be
compatible with. The single argument FLOOR() seems to be a Gnumeric invention.

We're getting side tracked again here. The idea is to fix the bug. We have a
potential fix, and Andreas has stated or re-stated some more objections to that
fix, which I've answered.
Comment 48 Andreas J. Guelzow 2005-02-11 15:03:17 UTC
I am closing this report with the `fixed' keyword although `broken' may be more
appropriate, but unfortunately is not available.
Comment 49 Nick Lamb 2005-02-13 15:50:09 UTC
Thanks. I'll pass this good news on to the original reporter, and hopefully we
can get Gnumeric back in his good books for the next stable series.