RE: [office-formula] serial number? Don't we mean serial date?

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

My apologies Rob, I wasn't clear enough.

I meant the serial day 0, not calendar year 0 or even calendary year 1588 or
whatever.  The serial day 0 could be specified to be 1899-12-31 in mapping
to the ISO 8601 Gregorian Calendar with sequential mapping to each
successive date, for example. 

My observation is that some mappings apparently start with serial day 1 at
1900-01-01 and evidently don't provide mapping of serial days < 1, so both 0
and negative serial day numbers have no corresponding calendar date.  When I
said I preferred assigning an origin date to serial day 0 it was just
because that is the conventional origin for non-negative ordinal numbers.

I understand the concern that the available precision limits the range of
serial-day numbers for accurate date-time values having certain precision
(although for calendar dates without times, the limit could be for which the
serial day numbers include exact-integer representations at steps of 1).

We need to say something about the relationship and the ranges for which
accurate intervals of days and of elapsed times to a particular precision
are maintained.  

Note that this has nothing to do with the calendar mapping, it has to do
with the accurate span from 0 that is representable with the serial day
Number value.  If is tempting to allow a comparable range of negative serial
days but there is actually no gain because the span from least to greatest
acceptable day number has to be an accurate day- or time-lapse value.

Thanks for raising this concern about assuring that at least some range of
conventional units (i.e., expressible in ISO 8601 time values and Gregorian
date and date-time values) be accurately expressible.  It clarified for me
that (1) the precision of serial-day values is a limiting concern
independent of the origin date and calendar mapping in effect and (2) we
want to ensure that some reasonable range of contemporary dates and
date-times are accurately mappable.

 - Dennis

SIDELIGHT: 

 1. I hadn't realized that serial day numbers also illustrate rather nicely
all of the interesting (and dangerous) characteristics of floating-point
representations when we consider that less-than day intervals may be
representable with great precision (there are 86,400 seconds, 86,400,000
milliseconds, 86,400,000,000 microseconds, and 86,400,000,000,000
(0.864E+14) nanoseconds in a serial day.  That is a tolerable range in some
implementations, and we can do better by working in a less-than one-day
range (e.g., we can also break a single minute into picoseconds with 15
decimal-equivalent digits of precision), a common happy situation where
finer resolution is meaningful.

 2. This observation is important because periodic functions and others are
approximatable much better when the operands are confined to narrowed
ranges.  This applies to the trigonometric functions where principle angles
and even fractions of principle angles provide the best preservation of
precision.  SINE(x) is not terrific for large values of x and there is not
much to be done about it.  For extremely large magnitudes of x, SINE(x) is
ridiculous of course.   

 3. These factors need to be taken into account when saying anything about
implementation quality and also about applicable transformations, whether in
conversion functions or in working between logarithm and exponentiation
bases.

 4. Continuing with a simple example, one would expect that SINE(x) is done
somewhat better for intervals -PI()/2 < x < +PI()/2 and perhaps even better
for smaller intervals centered on 0 (since there is some point where the
magnitude of x is so small that the best approximation to SINE(x) is x
itself).  

 5. I think considerations such as these matter when we choose to specify
the expected quality of function results in terms of what the available
Number representation provides and what range of operands are allowed versus
what range of operands are expected to fit the sweet spot for result
quality.  

 6. I think the bounds on financial-function quality (and the sweet spots)
are even more important to quantify than the limits of serial day
reliability, although they are not unrelated, e.g., whenever YEARFRAC comes
into the picture.  It will be an accomplishment to find understandable
implementation-neutral characterizations that can be applied in the
specification of OpenFormula functions that have such result-approximation
sensitivities.


-----Original Message-----
From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com] 
Sent: Friday, January 29, 2010 07:18
To: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] serial number? Don't we mean serial date?

"Dennis E. Hamilton" <dennis.hamilton@acm.org> wrote on 01/28/2010 
06:49:57 PM:

> 
> RE: [office-formula] serial number? Don't we mean serial date?
> 
> Yes, I have been arguing for two ambient properites (nice term), 
> 

I took the term from how Microsoft describes OLE/ActiveX controls.  There, 
an ambient property is a exposed by the control container (the thing you 
are embedding a control in) and defines things like UI locale, background 
color, default font, etc., -- the things you need to know to make a 
control blend in well in a given container.

There is an analogous case here, where we have a container, abstractly at 
least, where the formula evaluator is embedded. The context provided by 
the container will include things like locale, date origin, etc.  We 
should probably start tracking a list of these items. What are all the 
things a formula evaluator needs to know about other than the text of the 
formula itself?  What are the parameters of our abstract machine?

-Rob


---------------------------------------------------------------------
To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  Follow this link to all your TCs in OASIS at:
https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php