Download it now!
Bug 97334 (Function-iWorksNumbers) - [Numbers] Add iWorks' Numbers missing functions
Summary: [Numbers] Add iWorks' Numbers missing functions
Status: NEW
Alias: Function-iWorksNumbers
Product: Document Liberation Project
Classification: Unclassified
Component: libetonyek (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Whiteboard: needsAppleNumbers
Depends on: 97706 97977
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2016-01-23 16:20 UTC by Dennis Roczek
Modified: 2018-09-23 18:48 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Roczek 2016-01-23 16:20:09 UTC
I have analyzed all Apple's iWorks Numbers functions finally. I will also post my list to the wiki later this weekend.

We do not support all functions of Numbers. The help is accessible at

Basically all functions do explain themself, some remarks in brackets:
BASETONUM (similar to hex2bin etc. just with another base)
STRIPDURATION (similar to DUR2DAYS, seems to be included for compatibility with other spreadsheet software, dunno which one it is or where the difference to DUR2DAYS is)

I think we should support these functions for compatibility reasons (and for importing .number pages in the DLP project)
Comment 2 Dennis Roczek 2016-01-24 15:18:25 UTC
A further analysis revealed that our CONVERT_ADD is very similar to Numbers' CONVERT, but that Apple also allows to convert to weeks.
Comment 3 Dennis Roczek 2016-01-25 18:25:20 UTC
Ah, I found the STRIPDURATION difference in Apple's help forums.

=STRIPDURATION(“1w”) returns 7, the equivalent of one week in days.
=STRIPDURATION(12) returns 12, because 12 is not a duration value.
=STRIPDURATION(“abc”) returns “abc”, because “abc” is not a duration value.

This function may be automatically inserted when a Numbers ’08 document is upgraded, or an Excel or AppleWorks document is imported. It is removed in any copy of the file saved as a Numbers ’08 or Excel document.
Comment 4 Eike Rathke 2016-02-05 14:12:48 UTC
Why would this be Document Liberation Project / General instead of LibreOffice / Calc? The functions would have to be implemented in Calc. Unless someone came up with a really nifty idea ;-)
Comment 5 Eike Rathke 2016-02-05 14:15:27 UTC
Well, the Numbers filter part could be DLP, true.
Comment 6 Dennis Roczek 2016-02-05 19:52:26 UTC
well I thought the same original. There are two parts: 

* (maybe) libetonyek import filter modifications (at least for stripduration)
* integrate the other missing functions

So I switch that to libetonyek (the correct part) first and afterwards we can change it back to Calc's formula engine.
Comment 7 Dennis Roczek 2016-02-12 09:57:53 UTC
Another function which is missing: DURATION

Apple's DURATION is giving actually a duration (e.g. 1h).

From Apple's help file:
The DURATION function combines separate values for weeks, days, hours, minutes, 
seconds, and milliseconds and returns a duration value.
DURATION(weeks, days, hours, minutes, seconds, milliseconds)

weeks: A value representing the number of weeks. weeks is a number value.
days: An optional value representing the number of days. days is a number value.
hours: An optional value representing the number of hours. hours is a number value.
minutes: An optional value representing the number of minutes. minutes is a 
number value.
seconds: An optional value representing the number of seconds. seconds is a 
number value.
milliseconds: An optional value representing the number of milliseconds. 
milliseconds is a number value.
Usage Notes
 An argument that is 0 can be omitted, but the comma must be included if later   Â
values are included. For example, =DURATION(, ,12, 3) would return a duration value 
of 12h 3m (12 hours and 3 minutes).
Negative values are permitted. For example, =DURATION(0, 2, -24) would return a   Â
duration of 1 day (2 days minus 24 hours).

Our DURATION (and MDURATION and DURATION_ADD) are calculation a rates (financial stuff).
Comment 8 Dennis Roczek 2016-02-15 23:16:00 UTC
Excel and Numbers both calculate =MODE(6; 6; 4; 2; 2) as "6". ODF defines 2 as a correct value. I guess we additionally need something link MODE.XCL or MODE_ADD for compatibility reasons, too. :-(
Comment 9 Dennis Roczek 2016-10-23 17:29:36 UTC
have a second optional parameter to add padding 0s

From the Apple help: "convert-length:  An optional value specifying minimum length of the number returned. convert-length is a number value and must be in the range 1 to 32. If omitted, it is assumed to be 1. If included, convert-string is padded with leading zeros, if necessary, so that it is at least the length specified by convert-length."