Re: [office-formula] Conversions (BIN2DEC and friends) - how much to spec?

Are these functions assuming a particular
byte ordering?  Or are the machine-dependent?

-Rob

"David A. Wheeler" <>
wrote on 07/26/2006 10:33:29 PM:

> There are a number of base conversion functions of the form xxx2yyy

> whose standard semantics can charitably described as

> "created by the incompetent".  I'm being nice, believe
it or not;

> I suspect their creators are sorry for their design!

> 

> Yet these crazy semantics seem to implemented identically

> by many applications; I've already checked Excel, OpenOffice.org,

> and Gnumeric, and they all agree on them (which is the usual

> criteria for a standard, after all). I have every reason to

> believe that people have important-to-them spreadsheets that

> depend on these (crazy) semantics.

> 

> We have three choices that I can see:

> 1. Don't include them in the spec.  Clean, but then we don't

>    provide an upgrade path for those with spreadsheets that

>    currently use them.  I think we MUST provide an
upgrade path

>    for existing spreadsheet documents, so this sounds less
appealing.

>    The spec includes essentially all functions from

>    Excel and OpenOffice.org, so that anyone using those
programs

>    should have an easy upgrade path.

> 2. Include them, but leave out the "gross" parts as

>    "implementation-defined".  This has the
same basic

>    problem as #1... we're not helping people handle their

>    existing spreadsheet documents.

> 3. Include them, including the weird semantics that everyone

>    implements (and people probably depend on),

>    but also include "nicer" functions with

>    saner semantics.  In this case, BASE and DECIMAL
are far

>    saner and simpler, and are widely implemented

>    (e.g., BASE is implemented by OpenOffice.org 2 / Sun
StarOffice,

>    GNOME Gnumeric, KDE KSpread, and Corel Quattro Pro).

>    This gives people a transition plan.

> 

> As you can tell, I think we should do #3, though I think I need

> to hold my nose to write down this particular nonsense.

> I'm not even sure we can deprecate the xxx2yyy functions,

> because for some circumstances they make sense.

> We could deprecate some of their craziness later on, I suppose.

> 

> I'd like to hear comments on this.  Details below, so you

> can see what I mean.

> 

> ==== DETAILS ====

> 

> Let me give an example: BIN2DEC. BIN2DEC converts

> binary numbers to decimal numbers.  Here are examples:

> * BIN2DEC("101") is 5, and that's reasonable.

> * BIN2DEC(101) is 5 - it re-interprets the decimal presentation.

>   Weird, but okay.

> * BIN2DEC("1100000000") is -256.  What's that, you
say?

>   Well, the 10th bit is the sign bit, no doubt to support all

>   those people using 10-bit computers :-).

> 

> The hex functions, etc., all have sign bits, and in the

> weirdest, most unjustifiable places you can imagine.

> Hex allows 10 hex digits (40th bit is sign).. so it cannot

> handle 64-bit registers (never mind 64-bit computers).

> Do they actually allow negative numbers as input, or allow you to

> specify a register width? Nah.

> 

> Yet because these are the ONLY base-conversion functions that

> EVERYONE implements, people work around them, and I believe

> even depend on this odd nonsense.  How sad.

> 

> Thankfully, the widely-used BASE and DECIMAL provide a

> rational approach (they're both in OpenOffice.org; neither

> are in Excel).  BASE(X;base) converts number X to text

> representing the given base; DECIMAL(T;base) converts

> text T of given base into Number.  Far more sensible.

> 

> So I think we should include the xxx2yyy functions as a

> transition strategy, with their full nonsense (ugh!),

> as long as we also include BASE and DECIMAL

> as more rational/reasonably designed interfaces.

> 

> --- David A. Wheeler

>