[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. 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 >
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]