The Elusive Feeling of Safety

[Leonard Mada <discoleo@gmx.net>]

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