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)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: needsAppleNumbers
Keywords:
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:


Attachments

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 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)
DUR2DAYS
DUR2HOURS
DUR2MILLISECONDS
DUR2MINUTES
DUR2SECONDS
DUR2WEEKS
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)
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.
https://discussions.apple.com/message/28878054#28878054


Examples
=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.


and
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
@raal:
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:
DURATION
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
BIN2DEC
BIN2HEX
BIN2OCT
DEC2BIN
DEC2HEX
DEC2OCT
HEX2BIN
HEX2DEC
HEX2OCT
OCT2BIN
OCT2DEC
OCT2HEX
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."