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.)

 

[Guest]

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
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ae4a0544a0e6d1c
http://groups.google.com/group/microsoft.public.excel.misc/msg/be02c9c113154b5e

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]

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.)

 

[Guest]

You're welcome, glad it helped.

Jerry