Archive Magazine 1995/ARCHIVE95.iso/discs/pipeline/8_02/CrossCheck/ReadMe

Text File | 1994-07-28 | 13KB | 242 lines

%OP%VS4.13 (28-Apr-92), Gerald L Fitton, R4000 5966 9904 9938
%OP%DP0
%OP%IRY
%OP%PL0
%OP%HM0
%OP%FM0
%OP%BM0
%OP%LM4
%OP%PT1
%OP%PDPipeLine
%OP%WC2,1238,44,1748,0,0,0,0
%CO:A,72,72%
%C%Cross Checks
%C%by Gerald L Fitton
Keywords:
Check Error Fitton


Introduction

Although my 'day job' is teaching almost anything related to
Mathematics (from Statistics, Fourier Analysis, Stress Analysis of
Structures and even Economics) at the local College of Further
Education my knowledge of what goes on (Mathematically speaking) in
Primary and Secondary Schools is limited to tales told me by my 16 year
old students.  Perhaps a PipeLine article is not the place to enter
into a discussion as to whether the standards of pre Further Education
Numeracy have risen or fallen; I don't intend to say anything on that
subject at present (but I'm more than willing to listen to your
comments).  However, I do feel able to say, without fear of being
corrected, that that the Mathematics taught at Primary and Secondary
Schools nowadays is definitely different from that which I was taught.

Hence, it is with some trepidation and in anticipation of being deluged
by "Things ain't what they used to be" mail (but I won't mind - honest)
that I mention the instruction I received at Market Drayton (Salop)
Junior School when I was eight years old.  I must remind those of you
who never knew the nature of the Education system before the 1944
Education Act that there was no 11+ but there was an Examination called
the "Scholarship".  Those who passed the "Scholarship" were entitled to
a "Special Place" at the local Grammar School.  The Grammar School was
a private body and pupils paid fees unless they had a "Special Place"
(in which case the school fees were means tested and paid, at least in
part, by the Local Education Authority).  We were poor so that the only
way I could get to the Grammar School would be by passing the
"Scholarship" and so gaining a "Special Place".  In those days going to
the Grammar School was considered to be a 'good thing' for middle or
working class children (such as me).  It was something I (and many
others) believed we had to to work toward whilst at the Junior School.
Teachers at the Junior School were pleased for and proud of pupils who
passed the "Scholarship".

At the age of eight my education was (almost) exclusively English and
Arithmetic - in preparation for the "Scholarship" which we would take
at the age of 9, 10 or 11 (according to ability).  In English we were
taught Grammar and Comprehension as well as Parts of Speech!  In
Arithmetic we were taught fractions, ratios and proportion and - wait
for it - the subject of this article - "Cross Check Arithmetic".


Error Checks

During the last six months I have received a wealth (?) of mail asking
about "Cross Checks".  This topic has come to the fore in recent
correspondence primarily because the spreadsheet part of Versioná1.07
of Fireworkz sometimes does its arithmetic incorrectly (or not at all).
This would not be so bad (and there are 'work arounds') except for the
fact that it does so without generating any error message.  Of course,
this is potentially disastrous for those using Fireworkz for their
accounts and I can understand why those who experience this phenomenon
write to me using emotive phraseology rather than objective comment!

Generally, if the Fireworkz spreadsheet is small then the risk of an
error is small and when one occurs it is fairly obvious; if the
spreadsheet (or set of linked documents) is large then such an
unreported error is more likely and much harder to spot.

I have had occasion to note that PipeDream sometimes fails to include
the most recent numerical entry into its recalculation; these occasions
are when I use <CtrláFGS> to Save a large sheet which has not finished
recalculating.  Generally PipeDream puts itself right at the next entry
so I usually don't bother worrying about it until I have completed all
my transactions.  My main check is to enter all transactions into a
separate simple (credit, debit, balance) file as I go and check that
the final balance agrees with the main (large) spreadsheet.  This is
not a 'Cross Check' in the 'when I was eight' sense of the phrase but
it is a useful way of ensuring that all entries have been made
correctly and have been 'calculated' by the main spreadsheet.

From my comments above the more perceptive of you will realise that,
although I have both, I still use PipeDream in preference to Fireworkz
for the main part of my serious spreadsheet work.  Unreported errors in
Fireworkz is only part of the reason - the relative speeds of PipeDream
and Fireworkz is another.  I wonder how you feel about Fireworkz when
you compare it with PipeDream?


Cross Checks

Load the file [ErrorCheck] from this directory and you will see that
the block B5E8 consists of sixteen entries, all of which are what, as
an eight year old, I would have called "decimals" but which I believe
are now called "decimal fractions".

The sums of the columns appear in row 10 and the sums of the rows
appear in column G.  If you then add the values in row 10, B10E10, it
should be (exactly) the same as the sum of column G, G5G8.

When I was eight years old this "Cross Check Arithmetic" is something I
had to practice for hours on end (without the aid of a calculator).
Incidentally I found that adding columns was much easier than adding
along a row - even though I had never heard of (to use modern
terminology) 'place notation'.  I took to doing what I now know as
'transposing' the array of numbers (so that the rows became columns and
the columns became rows) and adding only columns.  I found that the
time taken to rewrite the array in its transposed form was more than
compensated for by the time taken to correctly add up the rows.  I
suppose that, with a calculator, it doesn't make a lot of difference
whether you're adding down a column or along a row.  Such is progress!

Before I introduce you to the formulae in H10, I10 and J10 let me draw
your attention to a subtle point about the formulae in row 10 and
column G.  For example, have a look at B10 where you will find
sum(B4B9) and not sum(B5B8) as you might have expected.  What I have
done is to introduce two blank rows, row 4 and row 9 and include them
in the sum().  You might wonder why.  The reason is that, if you do it
my way, you can mark the rows 5 to 8 (containing the data) and use a
Sort operation (such as <CtrláBSO>) on the marked block without
'corrupting' the formula in B10.  If you used the formula sum(B5B8) in
B10 and then sorted the block in such a way that row 8 changed position
to become, say row 7 then the sum(B5B8) would be 'corrupted' to become
sum(B5B7), which you don't want to happen because the new row 8 would
be left out of the sum!  It is 'good practice' to leave blank lines
above, below, left and right of a block of data for this reason.

Those of you who create data bases of text with each row a record and
each column a field would do well to note this 'tip' of leaving a blank
line in any computation on the columns.

As an aside - I think it is good practice to include blank rows and
columns when defining a PipeDream Name to refer to a block of data if
there is any chance you're going to sort it.


The Erroneous Cross Check

The 'obvious' cross check formula is that in slot H10.  Surprisingly it
gives the message "Error"!  The reason is interesting and I have
referred to it in earlier articles.  Essentially, decimal fractions are
not (and can not) be stored accurately in binary (though they can be
stored accurately in binary coded decimal) since a number such as the
decimal 0.1 is a non terminating series of 1 and 0 in binary.  At this
stage I must acknowledge the many correspondents who have written to
me, many sending me a worked value for 1á≈á10 using binary long
division.  The infinite series of (binary) 1 and 0 has to be truncated
at some point.  This truncation generates the 'inaccuracy' with which
values such as 0.1 are stored in the computer.

Whether or not you follow the reasoning of the previous paragraph, the
essential point to grasp is that the sum of the values in row 10 is a
sum of approximations and that the sum of the values in column G (which
is also a sum of approximations) can easily be slightly different.  The
displayed value won't be different because the difference between the
two values is a very small number (about 0.0000000000000001) and the
display is shown only to two decimal places.  If your 'Cross Check' is
to compare the two approximations for an exact match then sometimes you
will be told that there is an "Error" when, in truth the calculation is
(near enough) "OK".


A 'Pragmatic' Cross Check

Now have a look in slot I10 and you will see the formula which has been
developed as a result of correspondence about error checks between
myself and Dennis Howard.  Thanks Dennis for the inspiration!

The difference between the two values (remember that this difference is
theoretically zero if the numbers are exactly the same) is divided by
twice their average and the absolute value of this ratio is compared
with a small number - in this case (1e-10) ie 0.0000000001.  I have
used the function abs() to get rid of any negative value for the
difference before comparing it with the small positive number (1e-10).
This method of dividing the difference by (twice) the average allows
you to check both large and small numbers without having to change the
value of (1e-10) to match the (average) size of the two numbers.


The Custom Function

I have written a simple custom function called "same_number" which you
will find in the file [c_Same].  This custom function accepts two
numbers as its parameters and checks whether they are the same to about
one part in 1000000000000000 (one part in a thousand million million).
As an example of the way in which this custom function may be used have
a look at slot [ErrorCheck]J10 and you will see that it returns the
value "OK".

I haven't tried this custom function out for enough examples to be sure
it is the best compromise between accuracy and common sense but you'll
find that if you change the (1e-16) in slot [c_Same]A11 to (1e-17) then
this custom function returns "Error" in [ErrorCheck]J10 showing that
my error function does have a value greater than 0.00000000000000001!

The 'experiment' of the previous paragraph shows that the value of
(1e-16) is right on the limit of producing false error messages with
this example.  I think I'd be happier with (1e-14) or (1e-10) for
general use - but I'd like to know what your experience is of using
such a function.  False error messages from a known cause is 'safer'
than false "OK" messages (or, as in Fireworkz, no message at all).
As I said, this is a case for further experimentation.  If you get
false error messages with (1e-16) then try a slightly larger value but
never more than (1e-10) and let me know what you eventually find is a
reasonable value.  I'll report back on a future disc.


Finally

My general advice to those of you with large spreadsheets (or many
linked spreadsheets) and who have both PipeDream and Fireworkz is to
stick to using PipeDream until we find out the extent to which the next
version of Fireworkz is 'better' than the current version.  I keep
saying that PipeDream is a 'mature' package with few 'bugs' that are,
in the main "obscure and known".  What I mean is that I am confident
that I can use PipeDream to get answers I can believe in whereas I've
had a few near disasters with my accounts using Fireworkz.

The problem with the 'exact' cross check of [ErrorCheck]H10 is not one
of PipeDream's making; it is inherent in the nature of binary
arithmetic.  The 'work around' of seeing if two numbers are 'near
enough' the same can be implemented by means of a custom function.  If
you have a lot of such cross checks in your spreadsheet then you'll
find that using the custom function gives you the usual benefits of
using custom functions.  The benefits of custom functions range from
smaller spreadsheets (which load and save more quickly) to consistency
and readability (consistency and readability make modifications to the
sheet easier, more rapid and more sure).

Please let me know if you feel you have something to say on this
subject and particularly if you develop the custom function further.
Definitely let me know how you would like me to develop my articles on
the general subject of spreadsheet design.