Re: Stat Requirement (was Excel2000)

[David Firth]

In your followup post, I see that I misunderstood your reference to currency.
Let's not confuse the intended use with the data type as I did. Excel doesn't
list data types the way a programmer would. It is intended for the layperson to
use. Thus, currency describes one use of a scaled integer type that has a
fixed radix point with 4 digits to the right of the radix. I suppose it isn't
much use for other applications. The integer nature of the type and the
simplicity of operators in financial calculations makes it a good type for
finance. Integer math is fast. The programmer does not have to be that strong
in numeric methods.

The single and double reals are IEEE 754 short and long floating point reals.
Good from a coder's POV, since the internal FPU of any CPU from the 486DX
onwards will handle calculations in hardware automatically. Very quick
performance for the user and easy for the programmer. Most compilers support
these types. As you pointed out, range can be a problem with the short real.
Rounding error is a problem in them due to their floating point nature. These
types are in some ways artifacts of the way things were. Backwards
compatibility and the inertia of this is how we've always done it may be
factors, too. The best technical solution does not always win.

The long integer type is OK if you are willing to do your own scaling. Embedded
folks have done this for ages because an FPU was a rarity in most work. Some,
like Forth devotees, take reliance on integer math to an extreme, having fought
over whether to include FP in the Forth ANSI spec at all.

If the data types don't fit your application, I suppose that you have a valid
complaint. Excel has a pedestrian set of data types meant for general purpose
work. For work with minimum roundoff issues on large or small numbers, it would
have a bcd library capable of lots of precision or a string-based library
though I consider string-based math to be a bit of a kludge. The data storage
requirements would go way up, but today's machines have the resources to go
this route.

One problem with a mix of data types is what to do in mixed mode operations.
Precise operands can be compromised by an imprecise operand to yield an
imprecise result.

If folks do not choose the data type properly, their results may be less
precise than they otherwise could have been.


--
David Firth

Still Thinking Different: Apple Powerbook 3400  Newton 2100




=
Instructions for joining and leaving this list, remarks about the
problem of INAPPROPRIATE MESSAGES, and archives are available at
  http://jse.stat.ncsu.edu/
=

RE: Stat Requirement (was Excel2000)

[David Heiser]

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of David Firth
Sent: Sunday, January 06, 2002 1:22 PM
To: [EMAIL PROTECTED]
Subject: Re: Stat Requirement (was Excel2000)

Very good points. Appreciated.

DAHeiser


=
Instructions for joining and leaving this list, remarks about the
problem of INAPPROPRIATE MESSAGES, and archives are available at
  http://jse.stat.ncsu.edu/
=

RE: Stat Requirement (was Excel2000)

[David Heiser]

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of David Firth
Sent: Saturday, January 05, 2002 3:28 PM
To: [EMAIL PROTECTED]

Subject: Stat Requirement (was Excel2000)
thank you for your response

Thanks for the wealth of Excel trivia. Use the right tool for the job, I
say.
Excel might not be it.

I do have to take a little offense to the accuracy remark regarding business
calcs -- I learned early on that bcd reals or integer-based math libs were
the only appropriate mechanisms for business calcs. I prefer not to use
regular
real/float types if I have alternatives. But I'm a measurement/data acq man.
We can be a bit, well, anal about accuracy.
---
What I had in mind was that the variant form of data input in each cell (in
EXCEL, in VB and in VBA), accepts the following:
0   Empty   No data/entry (once held something but now is blank).
1   NullNo value. Unknown data.
2   Integer Whole number -32768 to +32767, 2 bytes.
3   LongWhole number (integer) -2,147,483,648 to +2,147,483,647, 4 
bytes.
4   Single  Floating point decimal number, aprox 7 decimal digits, 4 bytes.
5   Double  Floating point decimal number, aprox 15 decimal digits, 8
bytes. -1.79769313486231E308 to
-4.94065645841247E-324 and +4.94065645841247E-324 to
+1.79769313486232E308
6   CurrencyDecimal number with 4 decimal places, 8 bytes, 19 digits max. 
Use
it to minimize rounding errors.
-922,337,203,685,477.5808 to +922,337,203,685,477.5807
7   Date/time   A number, the left integer portion representing days and the
right decimal portion representing time as afraction of a day, 8 
bytes
There are a number of functions that will extract calendar and time
information from it.
8   String  Text. 10 bytes + string length, up to 2 billion characters.
However EXCEL limits cell contents to a maximum of 256 
characters.
9   OLE Object  4 bytes.
10  Error   Code number returned if an error in a computation occurred, 2
bytes.
11  Boolean True or false, 2 bytes. Integer, 0 is false, -1 is true.
12  Variant An array of variants, 16 bytes numbers, 22 bytes + string length
13  Non-Ole Object
14  Decimal 14 bytes. +/-79,228,162,514,264,337,593,543,950,335 with no
decimal point.
+/-7.9228162514264337593543950335 with 28 places to the right 
of the
decimal.
Smallest non-zero number is +/-0.(27 0's)1
17  Byte1 byte. 0 to 255.
8192Array   An ordered table of values

The number in the left column comes from the VarType() function. EXCEL uses
a separate formatting code to indicate how the number appears in the cell
(i.e. number of decimal points and % conversion). If I do calculations with
currency, I have up to 19 accurate digits, whereas with double, only 15. If
I do integer arithmetic with long integers, I only have up to 10 digits.
When you format a cell, EXCEL allows only 0, 1, 5, 6, 7, 8 and 11. Macros
can use all the other types. I have no experience with using decimal
numbers.
DAHeiser

-

A CS program more aligned with the needs of science, business, or econ might
be found, but CS is a general thing. The idea is that with the help of
content
experts or reference books the capable CS grad could do good work. If the
programmers involved slapped out some code and then went roller skating
(apologies to Dilbert) then they didn't do a good job. My 11 years in the
software side of embedded systems has contained too many recommendations to
peers about spending time to understand the customer's needs.
--
Bravo. Applause. (We will overlook the fact that the customer usually
doesn't know his needs until 2 weeks before product delivery date. This is
why software development is an involved time consuming interactive process.)
DAHeiser
--
IMHO it is more
of a process issue than a knowledge base problem. I took stats as an
elective
when I was an electronics engr tech undergrad because I thought it would be
handy.
---
Great, Good. Applause.
DAHeiser
--

 From what I have observed, many business type have a very limited math
background, and even learning simple business stat is a major problem. For
example try getting them to understand the difference between using z and
t tests,

Yes, but the idea again is to be a generalist who makes use of content
experts. I am now in an MBA program and have