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.
RATIONALE:
==========
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.
COMPETITIVE ANALYSIS:
=====================
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.
PROPOSED FUNCTION
==================
{ =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>
parameters).
USEFULNESS
===========
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
3.) EXPLANATORY- Role
- 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
5.) AUTOMATION
- 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.
Sincerely,
Leonard
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
