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 https://manuals.info.apple.com/en_US/Formulas_and_Functions_User_Guide.pdf
Basically all functions do explain themself, some remarks in brackets:
BASETONUM (similar to hex2bin etc. just with another base)
DAYNAME (DAYNAME(1) = Sunday)
MONTHNAME (MONTHNAME(11) = November)
NUMTOBASE (see BASETONUM)
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)
A further analysis revealed that our CONVERT_ADD is very similar to Numbers' CONVERT, but that Apple also allows to convert to weeks.
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.
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 ;-)
Well, the Numbers filter part could be DLP, true.
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.
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
seconds: An optional value representing the number of seconds. seconds is a
milliseconds: An optional value representing the number of milliseconds.
milliseconds is a number value.
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).
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. :-(
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."