OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Filling numbers & dates

Filling numbers & dates
 was [Filling dates in Calc]

Dear list-members,

This is a proposal to enhance ODF with a FILL() function. I planed to 
post it long ago, but never got the time for it.

Automatically filling a range of cells with specific values is one of 
the most important and most used features in spreadsheets. Spreadsheets 
have acknowledged this, and there are currently 2 methods to accomplish 
this: a MENU-entry and a direct DRAG-functionality. However, a specific 
function is missing.

Various languages do have such a construct, e.g.:
- the S+ language (used in R):
  > rep( <number> , <repetition> )
  > directly create a vector of incremented numbers:
     3:10 # all integers from 3 to 10
  > seq( <from>, <to>, <by> , <output.length>)

- Mathematica:
  > repetition operators (.. and ...)
  > Range[n] and Range[n1, n2, inc]
     see http://reference.wolfram.com/mathematica/ref/Range.html

- many other languages

Spreadsheets notably lack such a construct.

{ =FILL( <from>, <length>, increment = 0, allow.modify = FALSE ) }
{ =FILL( <from> , <length>/<to> , increment = 1 , type = 
"length"/"range", allow.modify = FALSE ) }
{ =REPEAT( <number> , <repeats>, allow.modify = FALSE ) }

In the second example: IF (<to> - <from>) is NOT a multiple of 
increment, than last number in the sequence is the one less than <to>.

IF increment is 0, than the 2nd parameter specifies the number of 
repetitions. IF increment is different from 0, then the 4th parameter 
specifies if the 2nd parameter specifies the end-value of the sequence 
("range"), or the length of the sequence ("length"). REPEAT() is a 
shortcut to FILL( , , 0 , ). Default for increment is 1. Default for 
type is "length".

allow.modify specifies IF by inserting or deleting one element from the 
results range, the function is updated to accommodate this change (i.e. 
increase/decrease automatically the <length> or <repeats> or <to> 

This feature is very useful because it covers some important use-cases:

1.) automatic updates:
 - elements will be updated automatically when
    inserting/deleting elements within the sequence
 - sequence beyond inserted/deleted element
    is updated automatically to accommodate this change
   [IF allow.modify == FALSE ]
     e.g. when deleting element 5 within FILL(1,10,1, "range"),
     elements 1:4 remain the same, 6:10 become 5:9, and a new
     element is created in position 10
 - IF allow.modify == TRUE, the function is updated as well
    e.g. REPEAT(1, 30) becomes REPEAT(1,29) by deleting
    one row

2.) floating-point accuracy
 - currently, the fp-engine is quite flawed
 - current fill operations insert a constant number which
    cannot improve in the future
 - spreadsheets may well implement more advanced fp-engines
    in the future; these functions would then recalculate these
    automatically-filled in numbers

 - a user clicking on an element, will see the function
    and will better understand the details of that
    particular portion of the spreadsheet
 - having a constant doesn't offer any insight into
   the reason for that number

4.) Ease updating the formulas
 - needs only editing the formula
 - currently, one has to perform multiple steps,
    IF the user wants to change the sequence

 - functions are very suited for automation

These are some of the reasons, why a FILL()/REPEAT() function is useful. 
I hope that everyone agrees that such a function is quite useful and 
very flexible. Prominent languages do have it, and spreadsheets have 
gone the trouble to implement 2 different ways to accomplish this, but 
obviously miss a function.

I haven't covered in this post DATES. Actually, the functions should 
cover both ordinary numbers and dates. When handling dates, it should be 
possible to set the part of the date that gets incremented: "day", 
"week", "month" or "year". It could probably look like:
{ =FILL( <date.from> , <length> , increment = 1 ,
      type = "day" /
                 "week" /
                 "month" /
                 "year", allow.modify = FALSE ) }

allow.modify (or allow.update) is an advanced setting. I could live 
without it, but it really makes sense to implement the whole power of 
modern language designs.



PS: Any discussion should probably proceed only on the OASIS mailing list.

Eike Rathke wrote:
> Hi Leonard,
> On Thursday, 2008-10-30 02:10:33 +0200, Leonard Mada wrote:
>> I will present a more global solution addressing the FILL on the OASIS  
>> list.
> Please note that the OASIS ODF TC works on the file format and the
> formula subcommittee on specifying spreadsheet functions and operators
> and how they behave in expressions. Application behavior such as fill
> operations are not on topic.
>> If I select 2 or more equal dates, and try to fill, the date still gets  
>> incremented. This is not the case with ordinary numbers. This is the  
>> reason, I supposed that the fill is broken (and basically it is, because  
>> it doesn't work as for simple numbers).
> That indeed looks weird. May be worth an issue, if there isn't one
> already.
>   Eike

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]