Bug from interaction of Average and Rank commands -- bottom of tied group falls out?

[Shannon Jacobs]

This is a problem with Excel 2002 (SP3). I'm doing a table of Likert-style
evaluations on various metrics, and I want to track the resulting overall
ranks at the bottom, using the Rank command. It's supposed to resolve ties
by reporting all of them at the same rank. I then added a running average
for each of the metrics so I could easily see if the ranking I was going to
assign to that metric was higher or lower than the average up to the current
point. As expected, the total scores for the unevaluated columns became odd
values, but they all look equal, and even report equality when tested.

As NOT expected, the Rank function reports that some of them are no longer
tied, even though the values appear to be equal and test as equal.

With some more experimentation, I believe that I understand the cause of the
problem. It's some kind of pretty subtle rounding error. The Average of the
previous values returns a value. If that value was truly the average of the
previous values, then including it as another value to be averaged in should
have no effect on the running average. However, the average value is rounded
in some way, and even though the paired values (for the sums) pass the
equality test, the resulting values are no longer equal when the Rank
function considers them.

Can anyone confirm the nature of the problem, or perhaps even point at a
relevant article. (As usual, searching the Microsoft 'support' webpages was
useless.)

[Jerry W. Lewis]

Most terminating decimal fractions are non-terminating binary numbers that
can only be approximated. Therefore most input numbers are approximations to
begin with. Calculations involving approximations will naturally produce
results that are only approximations ... This a natural and unavaoidable
consequence of finite precision math an binary representation of numbers; it
is not a bug and it is not unique to Excel.

As a direct result, there are couple of recent threads noted that
AVERAGE(x,x,x) may not exactly equal x
http://groups.google.com/group/micro...4a0544a0e6d1cc
http://groups.google.com/group/micro...02c9c113154b5e

For your application, you might be able to get your desired result by
rounding all your averages to some appropriate precision.

That they "report equality" but rank differently is a known issue, where MS
has muddied the water by sometimes reporting that things are equal when they
are not really. The only reliable test for exact equality is =(a-b)=0, where
the parentheses are required to keep MS from glossing over small differences.

Jerry

"Shannon Jacobs" wrote:

> This is a problem with Excel 2002 (SP3). I'm doing a table of Likert-style
> evaluations on various metrics, and I want to track the resulting overall
> ranks at the bottom, using the Rank command. It's supposed to resolve ties
> by reporting all of them at the same rank. I then added a running average
> for each of the metrics so I could easily see if the ranking I was going to
> assign to that metric was higher or lower than the average up to the current
> point. As expected, the total scores for the unevaluated columns became odd
> values, but they all look equal, and even report equality when tested.
>
> As NOT expected, the Rank function reports that some of them are no longer
> tied, even though the values appear to be equal and test as equal.
>
> With some more experimentation, I believe that I understand the cause of the
> problem. It's some kind of pretty subtle rounding error. The Average of the
> previous values returns a value. If that value was truly the average of the
> previous values, then including it as another value to be averaged in should
> have no effect on the running average. However, the average value is rounded
> in some way, and even though the paired values (for the sums) pass the
> equality test, the resulting values are no longer equal when the Rank
> function considers them.
>
> Can anyone confirm the nature of the problem, or perhaps even point at a
> relevant article. (As usual, searching the Microsoft 'support' webpages was
> useless.)
>
>

[Shannon Jacobs]

Thanks for your highly relevant reply and the more reliable equivalence
test. I have tested your formula, and it indeed returns an apparently more
reliable result than simple equality. (Well, at least it's more reliable in
terms of how the Rank command sees things.)

Jerry W. Lewis wrote:
> Most terminating decimal fractions are non-terminating binary numbers
> that can only be approximated. Therefore most input numbers are
> approximations to begin with. Calculations involving approximations
> will naturally produce results that are only approximations ... This
> a natural and unavoidable consequence of finite precision math an
> binary representation of numbers; it is not a bug and it is not
> unique to Excel.
>
> As a direct result, there are couple of recent threads noted that
> AVERAGE(x,x,x) may not exactly equal x
> http://groups.google.com/group/micro...4a0544a0e6d1cc
> http://groups.google.com/group/micro...02c9c113154b5e
>
> For your application, you might be able to get your desired result by
> rounding all your averages to some appropriate precision.
>
> That they "report equality" but rank differently is a known issue,
> where MS has muddied the water by sometimes reporting that things are
> equal when they are not really. The only reliable test for exact
> equality is =(a-b)=0, where the parentheses are required to keep MS
> from glossing over small differences.
>
> Jerry
>
> "Shannon Jacobs" wrote:
>
>> This is a problem with Excel 2002 (SP3). I'm doing a table of
>> Likert-style evaluations on various metrics, and I want to track the
>> resulting overall ranks at the bottom, using the Rank command. It's
>> supposed to resolve ties by reporting all of them at the same rank.
>> I then added a running average for each of the metrics so I could
>> easily see if the ranking I was going to assign to that metric was
>> higher or lower than the average up to the current point. As
>> expected, the total scores for the unevaluated columns became odd
>> values, but they all look equal, and even report equality when
>> tested.
>>
>> As NOT expected, the Rank function reports that some of them are no
>> longer tied, even though the values appear to be equal and test as
>> equal.
>>
>> With some more experimentation, I believe that I understand the
>> cause of the problem. It's some kind of pretty subtle rounding
>> error. The Average of the previous values returns a value. If that
>> value was truly the average of the previous values, then including
>> it as another value to be averaged in should have no effect on the
>> running average. However, the average value is rounded in some way,
>> and even though the paired values (for the sums) pass the equality
>> test, the resulting values are no longer equal when the Rank
>> function considers them.
>>
>> Can anyone confirm the nature of the problem, or perhaps even point
>> at a relevant article. (As usual, searching the Microsoft 'support'
>> webpages was useless.)

[Jerry W. Lewis]

You're welcome, glad it helped.

Jerry

"Shannon Jacobs" wrote:

> Thanks for your highly relevant reply and the more reliable equivalence
> test. I have tested your formula, and it indeed returns an apparently more
> reliable result than simple equality. (Well, at least it's more reliable in
> terms of how the Rank command sees things.)