OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Unit system tracking

Leonard Mada proposed unit system tracking, here:
> A major disadvantage of every spreadsheet application is the lack of  coding capabilities to explicitly state the type of the values. This *major design flaw* of spreadsheets makes them very error prone  when intermingling  various data formats.
> I really miss something like the new FORTRESS (see http://fortress.sunsource.net/).
>In my primary work, I am overseeing 100+ employees working extensively  with worksheets. Unfortunately, there were often  situations where 2 currencies  were used, providing a perfect milieu for conversion errors, e.g. 1,000,000 RON equals approx. 300,000 Euro, BUT adding 1,000,000 or 300,000 makes a great difference (of 700,000 RON or Euro). The globalisation trend will only  foster  this development.
>The killer feature that  will differentiate spreadsheets from the  '70-'80s from modern concepts would allow the user to specify the type of the data, e.g.:
> A1: 30 m
> A2: 125 cm
> A3: 86 inch
> A4:   = SUM(A1:A3) => implicit conversions are done
>2nd example:
>A1: 1.2 M Euro
>A2: 0.67 M $
>A3: =SUM(A1:A2) => implicit conversion using a pre-specified conversion rule
>As this feature will differentiate the flawed concepts of the past from the modern  views, I hope that it gets implemented in the ODF specification.

Thanks for your helpful comments!

Unit system conversions are indeed very useful.   We _do_ provide unit system conversions already, though not automatic in the way you'd like.  The current specification includes a function, CONVERT, that does unit system conversions for many (constant) relationships.  Note that the CONVERT specification goes WAY, WAY beyond what Excel does - it can handle areas (like acres), velocities (like mph and m/s), lightyears, and many other useful units.  So we _already_ do unit system conversions, but don't require applications to track units with every value.  It doesn't do currency conversions, as well.  Let's call what you'd like to see "automatic unit system tracking and conversion".

Automatic unit system tracking and conversions are indeed a useful capability, and there are a very few calculation systems that do automatic unit system tracking and conversion.  One of them is the Google calculator; if you type into the Google search bar "3cm + 1inch" it will correctly convert and give you the answer.

I looked into doing this briefly a while back, because it has its advantages.  Unfortunately, really doing this correctly for ALL cases is actually very, very complicated. Truly handling monetary system conversions is an incredibly complicated issue; you need to provide control over this.  And sometimes you do NOT want to follow the rules (some engineering 'rules of thumb' actually VIOLATE unit systems).    Handling unit systems doesn't come for free; it generally causes a major performance loss (slowing calculation WAY down) which may or may not be okay depending on your circumstance - many users of BIG spreadsheets would be very UNHAPPY with this as a requirement.  And it greatly complicates implementation, too, which creates a big opportunity cost.

What's more, there are a HUGE number of "little details" that must be addressed, e.g., you need a standard way to associate various values with unit systems, standard ways to represent them, descriptions with each function on how to deal with them, etc.  You'd need to able to say "I want this cell in furlongs/fortnight" without actually entering the number, for example.  I'd guess it'd take a LOT of effort to spec all that, plus implementation work to make sure that the specification was actually correct.  The history of past systems that tried to do this is not encouraging. I remember a number of Ada95 automatic unit systems that came unglued when people tried to use them seriously.  The _idea_ is obvious enough, but really getting the details right is difficult.. it's amazing how much humans do "automatically" but is hard to get down in a real spec.  And if they're done wrong it's WORSE than useless, because you'll pay the performance and implementation costs, and then have to work around it.  And users who do NOT want it would still pay the price, which is a problem too.  There's no point in spec'ing this unless it's CORRECT.

Current spreadsheet applications do NOT perform automatic unit system tracking, due to these problems.  There's a need for an interchange standard for current apps that do NOT perform this automated tracking, so MANDATING such support on EVERY spreadsheet application would (in my opinion) be inappropriate.

That said, OpenDocument is carefully designed to allow OTHER formula languages as well.  It would be quite reasonable to define another formula language that was "this one, plus automatic unit system tracking".  Of course, you'd then need to do all the work to define what the tracking/conversion actually added (big deal!).

So I think we should define a language without automatic unit system tracking and conversion, and then, if enough people are interested in creating a specification for the automatic version, let them work on it as a follow-on project!  But it would require a committed group of people to write the details of the specification AND to actually implement that spec.  There's no point in writing a spec that people will not implement.  If there are people interested in such follow-on work to support automatic unit system tracking and conversion, I'd like to know; you'd need to promise significant time to get all the details correct, and in practice you should arrange for at least one implementation before it can be standardized (else you risk standardizing something unimplementable).

--- David A. Wheeler

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]