Re: [office-formula] Call for volunteers, undefined functions,plan for this week... and a THANKS to Andreas Guelzow

[Eike Rathke <erack@sun.com>]

Hi David,

On Thursday, 2007-02-08 21:55:09 -0500, David A. Wheeler wrote:

> DATESTRING

Where did that originate? Not found in OOo, Gnumeric, Kspread,
Ecma/Excel, let's drop it.

> NETWORKDAYS (note: internationalization issues)
> WORKDAY (note: internationalization issues)

For interoperability let's assume Excel semantics.

> HYPERLINK (note: weird semantics, we may not want it in the spec)

Quite weird, but supported by OOo and Excel. See
http://specs.openoffice.org/calc/compatibility/HyperLinkFunction.sxw

> RTD

Excel specific RealTimeData function that calls a Component Object Model
(COM) Automation server implemented as an ActiveX DLL or executable.
Highly unportable I guess.. I suggest to drop it.

> CELL

Hmm.. probably needed for interoperability.

> CURRENT (tricky semantics)

Actually not that tricky, it simply pushes the current calculation value
on the stack and makes it available as an argument to the next
operation, for example in
=A1+B2+IF(CURRENT()>3;STYLE("red");STYLE("green")) used to compare the
result of A1+B2 and conditionally apply a cell style.

Which adds STYLE to this list. I have both on my TODO agenda. They may
not sound that useful at first, but STYLE sometimes is used to have more
fine grained control than a fixed number of Conditional Formats allow.
Additionally the STYLE function has a timeout feature to switch to
another style after a while. Useful with real time Add-Ins.

> GETPIVOTDATA

I think we (OOo Calc team) can provide some definition for that,
hopefully by end of the month.

> LOOKUP

Oh, still undefined? I thought that was done with [HV]LOOKUP.

> FREQUENCY
> GROWTH

Statistical functions, needed. Any volunteer?

> HYPGEOMVERT
> ASC

As written earlier (moment.. where was it..)

| Date: Fri, 03 Nov 2006 17:40:37 +0100
| Subject: [office-formula] DBCS function and others in section 2.1.3 Large Group
| From: Eike Rathke <erack@sun.com>
| Message-id: <20061103164037.GG47406@sr1-eham02-01.Germany.Sun.COM>
| 
| | TODO: Currently the list above includes functions “hidden” in Excel that
| | perhaps should be removed: DBSC, HYPGEOMVERT, MNORMSINV, PHONETIC,
| | USDOLLAR.
| 
| We got a request to support the DBCS (note that it's not DBSC, which
| seems to be a typo in our document) function, which in Japanese Excel
| versions is named JIS, and the complementary ASC function (despite that
| they aren't related to JIS <-> ASCII text encoding conversion, as the
| names may suggest). Both seem to be actively used in Japanese
| environments. But might also do different things in other
| localizations.. See also
| http://www.openoffice.org/issues/show_bug.cgi?id=70213
| 
| As for HYPGEOMVERT: this seems to be a typo/copy&paste error, the
| function is called HYPGEOMDIST, hypergeometric distribution, and is not
| hidden. HYPGEOMVERT is the German localized function name (Verteilung ==
| distribution).

So I propose to define ASC and JIS (somehow in English Excel versions
imported as DBCS, but in Ecma/Excel it is JIS again) for the Japanese
context.

Drop HYPGEOMVERT. I'll remove it in my copy this week.

> BAHTTEXT (should probably remove)

That's a strange one and converts numbers into Thai numerals,
originating from a Thai localized Excel version. It seems Thai
spreadsheet users can't do without, so it was also implemented in OOo.
It even made it into Ecma/Excel.

We have a spec for it, so defining the function shouldn't be a problem.
See http://specs.openoffice.org/calc/compatibility/bahttext.odt

> If there are any particular functions you'd like to take on, or you're willing to take on 2 that i pick, let me know. First come - first serve.

To summarize: I'll take GETPIVOTDATA, CURRENT, STYLE, ASC, JIS

If no one else feels comfortable with HYPERLINK and BAHTTEXT, I'll do
also those.

I'll work through other TODOs though before.

> My sincere thanks to Andreas J. Guelzow, who found some problems in ZTEST and has ALREADY sent me a corrected version of that function definition.  That is really, really great - and fast turnaround too!!

Good catch indeed, and many thanks.

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.