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

[robert_weir@us.ibm.com]

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

-Rob

"David A. Wheeler" <dwheeler@dwheeler.com>
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
>