Dear list members,
I incidentally stumbled upon a *major spreadsheet issue* while
examining another error.
This affects storing floating-point keyboard input and automatic filling
using floating point numbers.
INTRO
=====
While analysing an OOo Calc issue
(http://www.openoffice.org/issues/show_bug.cgi?id=88429), I "found"
initially that Calc wrongly computes: =ROUNDDOWN( (8.x-8) * 10 ) for
every decimal x from 1 to 9.
This was a little bit unexpected, because 8.5 can be represented
accurately as a float and some of the float-representations of those
numbers actually exceed the actual 8.x number, so the ROUNDDOWN() should
have computed the correct result.
This was even more intriguing, because Calc performs extensive rounding
even for numbers less close to an integer. An initial more extensive
evaluation did confirm these results, but I missed to see that entering
the values 8.1- 8.9 manually through the keyboard changed the results.
EXPLANATION
============
1.) When I entered 8.0 - 8.9, I did use the automatic fill capability
present in every spreadsheet application.
2.) Unfortunately, this handy usability feature brings a deceitful safety:
- the resulting numbers DO NOT equal the equivalent keyboard-input
- therefore keyboard "8.5" != automatic "8.5"
By generating the automatic series, actually I got floating point
numbers very different from the numbers that would have resulted from
keyboard-input.
PROBLEM
========
1.) The real big problem is that every spreadsheet user uses the
automatic filling on a daily basis. It is a very used and very useful
feature, one cannot conceive spreadsheets without it.
2.) How is the direct keyboard input stored within a spredsheet?
I do hope that "8.1" keyboard input gets stored as "8.1", even IF
subsequent calculations do use the floating point representation of
"8.1", which will be slightly less than "8.1" on current machines. [But
please note, this might change in the future.]
3.) How does the autofill increment this "8.1"?
If it takes the float "8.1" and adds the float "0.1", then this is *very
BAD*, because:
- 8.3 to 8.9 all will be less than the corresponding
float representation of the keyboard inputted "8.x"
- the results are NOT formulas, BUT rather values *hardcoded*
into the resulting spreadsheet!!!
So, even though later / future processors / float handling might improve
the calculations using float numbers, and formulas get recalculated,
these numbers DO NOT change and will remain for eternity wrong.
SOLUTION
=========
I hope that this issue gets addressed within ODF. It is much too
important to be left to spreadsheet implementations and it is something
all users count on without realising its pitfalls.
My proposal is:
- keyboard input is stored as such
(this is probably so, BUT should be explicitly mentioned in ODF)
- autoincrement / autofill:
-- when performed on a direct keybord-inputted cell:
this is an accurate number, so the spreadsheets shall calculate
accurate increments and store accurate numbers, i.e.
8.1 + 0.1 = accurate 8.2
8.4 + 0.1 = accurate 8.5
-- on formulas: silly, it doesn't work; problem solved ;-)
[formulas get copied to the various cells]
Obviously, the implementation of this requirement will have to use a
different mechanism than a floating point addition. Determining the
last decimal and incrementing it using integer arithmetic is an option,
but others might come with other solutions.
Sincerely,
Leonard Mada
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
