Re: [office-formula] Semantics

[Dan Bricklin <danb@bricklin.com>]

Auto-conversion and being typeless I think is a real problem. 
"Everything gets converted as expected" -- what is "expected"? What 
about when it's ambiguous and you want to specify which? What about 
audio values in the future? Do we need to recognize them? What if we can?

I can see why you'd want COUNT vs. COUNTA -- define a range bigger 
than the values that includes the header/title row of a list of 
numbers so you can insert into the list including above the first 
without needing to redefine the list. You don't want the heading 
(which could be "Q4'2005" or "2006") included in the count used for 
an average. (Inserting off the end of a list is a common error -- 
Lotus was sued over it once even -- so creating a formula that 
protects from the error is a good thing.)

-DanB

At 05:24 AM Tuesday 2/28/2006, Tomas Mecir wrote:
>On 2/28/06, David A. Wheeler <dwheeler@dwheeler.com> wrote:
> > Generally spreadsheets agree on nearly all semantics anyway.
> > If you SUM a range, EVERYBODY skips the text values in the range.
>
> >    + KSpread 1.5-beta: Text and reference-to-text is taken, and conversion
> >       attempted; if success, returns converted number, else returns 0.
>
>
>These two together become tricky, if they're to be used differently
>... If error is returned instead of 0 for failed attempts, THEN we get
>a problem with SUM - if it's to behave the same, it will no longer
>ignore text ... Also, because SUM will ignore text references, it
>already does have the problems with 1 being typed as L ...
>And the users probably use SUM more than +, so the problem with
>typewriter-trained users is already present. If it's even important -
>I don't think typewriters are still used much :)
>
>Hence the problem with handling text values where numbers are expected
>would be, what kind of inconsistency should there be ? Because it
>seems like there always will be SOME inconsistency somewhere, due to
>the way existing spreadsheets work ...
>
>How I personally would like these things to be, at highest levels:
>
>First of all, my fundamental principle: as far as the user is
>concerned, there are no datatypes. Everything automatically gets
>converted to what is expected.
>
>- Numbers are numbers, treated as numbers. No problem here.
>- Passing a number to a string function converts the number to a
>string, using locale settings.
>- Passing a string where number is expected, converts the string to
>some number. 0 if failure. Yes, that's how I did it in KSpread (so I
>may be biased here), and yes, we silently ignore errors, but at least
>we're consistent.
>- SUM is doing the same, and functions like AVG basically just use SUM.
>- COUNT includes text and boolean and whatnot. It does not include 
>empty values.
>- basically, SUM/COUNT/... become what SUMA/COUNTA/... are now.
>- remove SUMA, COUNTA, AVGA, ***A - or make'em just aliases. I realize
>this may create problems, but it follows my fundamental principle of
>no datatypes. Anything else may be easier for the geek population, but
>confusing for anyone else. Imagine a Random User (TM), who has a
>column full of text data and tries to put a count at the bottom;
>=COUNT(range) is the obvious choice, right ? Only that it won't work.
>Bad, in my opinion. All this ***A business is only in because
>spreadsheets didn't auto-convert values as needed, thus a work-around
>was necessary.
>
>We, however, shouldn't need work-arounds.
>
>/ Tomas