Re: QP9 - @MODE

NigelH wrote:
> 
> I have generated several columns of numbers, and I was hoping to use 
> @MODE to find the mode of each column.
> 
> Column A has 114 values. The first (A1) is approx -0.074. The second 
> (A2) is approx 0.205. @MODE for the column returns 0.205...
> 
> If I do @COUNTIF on the column to count how many values equal A1, I get 
> 41. If I do @COUNTIF on the column to count how many values equal A2, I 
> get 26.
> 
> If I copy column A into Excel 2003 and repeat the tests, I get the same 
> answers - there are 41 occurrences of -0.074... and 26 of 0.205... but 
> 0.205... is the mode.
> 
> Does anyone know what the problem is, and how I can circumvent it?

Clearly something is out of kilter.

- Are the values fixed, or formulas?

- If you apply the QuickFilter (AutoFilter), does the output match the @COUNTIF

results?

- How are the results affected if you transform the values first?

Cheers,
Uli

I have generated several columns of numbers, and I was hoping to use 
@MODE to find the mode of each column.

Column A has 114 values. The first (A1) is approx -0.074. The second 
(A2) is approx 0.205. @MODE for the column returns 0.205...

If I do @COUNTIF on the column to count how many values equal A1, I get 
41. If I do @COUNTIF on the column to count how many values equal A2, I 
get 26.

If I copy column A into Excel 2003 and repeat the tests, I get the same 
answers - there are 41 occurrences of -0.074... and 26 of 0.205... but 
0.205... is the mode.

Does anyone know what the problem is, and how I can circumvent it? I'm 
working with a large number of columns spread over several worksheets.

Thanks,

NigelH

Uli wrote:

> 
> NigelH wrote:
> 
>>
>> I have generated several columns of numbers, and I was hoping to use 
>> @MODE to find the mode of each column.
>>
>> Column A has 114 values. The first (A1) is approx -0.074. The second 
>> (A2) is approx 0.205. @MODE for the column returns 0.205...
>>
>> If I do @COUNTIF on the column to count how many values equal A1, I 
>> get 41. If I do @COUNTIF on the column to count how many values equal 
>> A2, I get 26.
>>
>> If I copy column A into Excel 2003 and repeat the tests, I get the 
>> same answers - there are 41 occurrences of -0.074... and 26 of 
>> 0.205... but 0.205... is the mode.
>>
>> Does anyone know what the problem is, and how I can circumvent it?
> 
> 
> Clearly something is out of kilter.
> 
> - Are the values fixed, or formulas?
> 
> - If you apply the QuickFilter (AutoFilter), does the output match the 
> @COUNTIF results?
> 
> - How are the results affected if you transform the values first?
> 
> Cheers,
> Uli

Thanks Uli,

I've been doing some more experimenting. If I copy the column into a 
text editor then back to either QP9 or Excel, I then get the 'right' 
answer. The problem must lie in the 'extra' decimal places that the 
spreadsheets don't make visible, and differences in the way equality is 
evaluated between the mode and countif functions in both QP9 and Excel. 
I changed the @COUNTIF to explicitly include the value from A1 (using 
cut and paste) rather than referring to the cell and it returned the 
value 23 rather than 41.

The values in the column are initially calculated by formulae, then 
copied by value into their final resting places.

Using QuickFilter, the results match @COUNTIF for the cases I have 
looked at in detail, but I notice that a couple of the other values have 
duplicates which differ in the 15th decimal place.

I have adopted a circumvention of rounding everything to 14 decimal 
places, and @MODE seems to be giving me the 'right' answers now.

NigelH

NigelH wrote:
> I've been doing some more experimenting.
So have I ... more below.

> If I copy the column into a 
> text editor then back to either QP9 or Excel, I then get the 'right' 
> answer. The problem must lie in the 'extra' decimal places that the 
> spreadsheets don't make visible, and differences in the way equality is 
> evaluated between the mode and countif functions in both QP9 and Excel.

Nigel, you hit it right on the head.  Apparently, both QP and Excel recognize 
significant digits IN EXCESS of what any of their display formats are either 
able, or "willing", to display, with sometimes startling consequences,
as in 
your case.

> I changed the @COUNTIF to explicitly include the value from A1 (using 
> cut and paste) rather than referring to the cell and it returned the 
> value 23 rather than 41.

Quite interesting.  This would imply that the internal procedure that passes a 
criterion value from an indicated cell to @COUNTIF uses a lower level of 
precision than the procedure which passes a constant to the function, i.e., an 
internal inconsistency within the function itself -- which is all the more 
remarkable if you consider that this inconsistent treatment of numerical values

is equally true for QP and Excel.

> The values in the column are initially calculated by formulae, then 
> copied by value into their final resting places.
> 
> Using QuickFilter, the results match @COUNTIF for the cases I have 
> looked at in detail, but I notice that a couple of the other values have 
> duplicates which differ in the 15th decimal place.

Basically, that's what this method was supposed to shed light on.

> I have adopted a circumvention of rounding everything to 14 decimal 
> places, and @MODE seems to be giving me the 'right' answers now.

Your particular scenario caused me to stumble upon a strange phenomenon with 
regard to the recognized precision level of numerical values and the manner in 
which this is variously interpreted and used.  A simple example should help to 
illustrate the issue at hand:

In QP, enter in A1 the number 1234567890.12345 (15 digits), and in A2 the number

1234567890.123451 (16 digits).  Looking at the displayed values in the cell, as

well as the underlying values in the input bar, the values in A1 and A2 appear 
to be identical (QP arbitrarily drops the last digit from the displayed value in

A2 (both in the cell as well as the input bar).  A quick test using the simple 
formula +A1=A2 suggests that the two values are indeed equal - but wait, not so

fast:  there are other tests which indicate that the two values are NOT equal 
(@DELTA(A1,A2), @GESTEP(A1,A2), +A2>A1, +A2-A1).  What's going on? 
Apparently, 
in SOME of its calculations QP uses any additional invisible digits that extend

beyond the program's design limit for display but which are still part of the 
cell's internally stored value.  (BTW, this test equally applies to any numbers

comprised of 15 or more significant digits, including numbers between 0 and 1. 
As an additional, curious aside, certain fractions actually produce decimals 
that are displayed with MORE than 15 significant digits, in seeming violation of

QP's established display limit, e.g., +1/11 (16 digits), +1/110 (17 digits), and

+1/1001 (18 digits).  Another curiosity is that the formula +A1=A2 begins to 
agree with the not-equal verdict of @DELTA(A1,A2) if the trailing invisible 16th

digit in A2 is greater than 3, which I suspect is a consequence of a 
decimal/binary clash.)

Now, attempting to do this in Excel quickly reveals that Excel does NOT accept 
the input of more than 15 significant digits if entering a value MANUALLY.  Any

digits beyond the first 15 significant digits are simply dropped from the 
internally stored value and seem to have no affect on subsequent calculations. 
This, however, is not true for data COPIED to an Excel spreadsheet (which, I 
believe, is why you encountered the same results in Excel as in QP).  Thus, if 
you paste data from QP to Excel using either the Biff3 or Wk1 option, Excel DOES

store the underlying value with MORE than 15 significant digits (if the original

value in QP had more than 15), but just like QP, Excel does not display those 
extra digits.  What is particularly strange is that the extra digits do not even

appear in Excel's 30-decimal display format, even though they are apparently 
part of the cell's internally stored value, as can be ascertained by applying 
the DELTA and GESTEP tests.  Curiously, the tests =A2>A1 and =A2-A1 (which
give 
reliable results in QP) return results in Excel which suggest equality, in 
obvious contradiction to the apparently more precise DELTA and GESTEP functions,

which indicate inequality, and thus by extension, the presence of those extra 
invisible digits even in Excel.

Well, enough of this now, but it sure was an eye-opener ... in several respects.

  Thanks for bringing up the issue.

Cheers,
Uli

lemoto wrote:
> Uli:
>> it sure was an eye-opener
> 
> Highly instructive!-)}
> 
> I am not sure whether this throws any light or not:
> 
> I find, courtesy of Google:
> <<<
> The Pentium (along with every other major microprocessor) uses 
> the IEEE 754 standard to represent floating point numbers.  A 
> floating-point number stores a real number as sign*1.XXX*2^YYY, 
> i.e. one plus a fraction, raised to a power of two, along with 
> the sign. The XXX part is the mantissa and the YYY part is the 
> exponent. The IEEE standard defines a single-precision number to 
> have a 23-bit mantissa.  This provides 24-bit precision 
> (counting the leading one) and is equal to approximately 7 
> decimal digits.  A double precision number has a 52 bit 
> mantissa, giving 53-bit precision; equal to about 15 decimal 
> digits.
>  - which is, to within the width of a mouse's whisker, what QP 
> offers.

Intuitively, I recognize that this probably plays a (if not THE) key role in 
producing the computational vagaries recently discovered.  Nevertheless, I must

admit that I would have to delve into several weeks' worth of intensive research

and study before being able to constructively comment on the subject matter of 
the specific implications of a hardware-driven binary number system attempting 
to express numerical values within the "straight jacket" of a human,
and by 
extension, software-driven, decimal number system.

In the interest of keeping things shorter than I'm prone to do, let me just say

that the comment about QP's ability to operate within a mouse's whisker's width

of normal computational requirements was the only part I fully understood :-) 
The other stuff made me wish I was 20 or 30 years younger to investigate and 
sort out.

Just my way of saying, "I betcha there was light in there, if only I had
eyes to 
see."

Uli