Charles,
Change the formulas in the total cells (upon which the ranking is based) to
something like:
=ROUND(Oldformula,0)
HTH,
Bernie
MS Excel MVP
"Charles Blaquière" <[email protected]> wrote in message
news:%[email protected]...
> I have a scoring spreadsheet where column A is the player ranking, B is
the
> name, and S the point total. I have just noticed that, for a pair of
players
> with the exact same score, Excel is assigning different ranks. Here's an
> excerpt with just those columns:
>
> Rank Player Total
> 1 JohnF 87
> 2 Adam 79
> 3 James 77
> 3 MarkSu 77
> 3 Nick 77
> 6 Tom 76
> 6 CharlesB 76
> 8 Jennifer 68
> 9 Maurice 55
> 10 Jason 50
> 11 Kim 48
> 12 JohnW 43
> 13 JimW 36
> 14 Andrew 34
> 15 Casey 31
> 16 Paul 22
> 17 Tim 20
> 19 Ian 19
> 18 JoeN 19
> 20 Eli 18
> 20 Hal 18
> 22 Jeremy 13
> 23 Nancy 12
> 24 Anthony 9
> 24 Geoff 9
> 26 Bob 8
> 27 Steph 6
> 28 LizD 4
> [Omitted remaining rows up to 52]
>
> As you can see, Excel correctly ranks players tied for 3rd, 6th, 20th, and
> 24th. But Ian and JoeN, both with 19 points, get ranked differently.
>
> - This is not a "sliding rank range" problem caused by improper formula
> definition; The formulae for the first two data rows, for example, are:
>
> =IF(S2=0,"",RANK(S2,S$2:S$52))
> =IF(S3=0,"",RANK(S3,S$2:S$52))
>
> - I highlighted both 19-point scores in their cells' formula bar and
pressed
> F9 to ensure they didn't vary by some small amount. In both cases, Excel
> displayed 19 as the calculated value.
>
> - I don't know if this is relevant, but there is another problem on the
> sheet, where equal percentage values are shown by Excel to vary in their
> least significant digit (0.583333333333333 vs. 0.583333333333334).
> Highlighting & calculating parts of the formulae shows the first cell to
be
> computing 49/84 and the second 35/60; that's equal to 7/12 multiplied by
the
> same number (5 or 7) in both the numerator and denominator, which gives
the
> same result. (0.583...3). Perhaps Excel is starting to act screwy on me?
>
> Any hints? Thanks.
>
>
Bookmarks