+ Reply to Thread
Results 1 to 4 of 4

Dynamic Tie Breaker Not Workign

  1. #1
    Registered User
    Join Date
    10-02-2013
    Location
    America, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Dynamic Tie Breaker Not Workign

    Hello!

    I use this forum a ton, but rarely post. I'm trying to figure this out. I am using the rank + countif formula to do a tie breaker and then vlookup to rank. But for some reason I can't figure out, one of my data sets it has an error. I have narrowed it down to this: This is the rank without a countif

    Rank Number
    9 8.55
    8 9.40
    7 10.15
    6 10.70
    5 10.80
    3 10.80
    3 10.80

    2 11.60
    1 13.35

    Notice the 10.8 is ranked 5 then 3, 3. If I set up a grid referencing the three 10.8's and using =Cell=Cell, I get this: (L6, L7, L8 are the cell references for the 10.8's)

    L6 L7 L8
    L6 TRUE TRUE TRUE
    L7 TRUE TRUE TRUE
    L8 TRUE TRUE TRUE

    Any idea why rank would rank them different despite them being the same? The formula for rank I drug down is this:

    =RANK(L6,$L$2:$L$10,0)
    =RANK(L7,$L$2:$L$10,0)
    =RANK(L8,$L$2:$L$10,0)

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dynamic Tie Breaker Not Workign

    When I put those same values into a range L2:L10, I get 3 for all of their ranks. So there is an unseen difference in your data. Copy L7 and paste it into L6.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-02-2013
    Location
    America, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dynamic Tie Breaker Not Workign

    Hey thanks for the reply!

    I found the answer when I improved my google search of this forum.

    Quote Originally Posted by Jerry W. Lewis View Post
    Charles Blaquière wrote:

    > Ron Rosenfeld wrote:
    >
    >
    >>I cannot reproduce your problem with your posted data. I suspect the
    >>two scores are NOT exactly the same on your worksheet.
    >>
    >>1. How are the scores calculated? If these are calculated rather
    >>than simple entries of integers, it is likely that the two 19 point
    >>scores are NOT exactly the same.
    >>
    >>2. What do you get if you do an equality between the two cells (e.g.
    >>in some cell enter the formula =S19=S20 assuming S19 and S20 are the
    >>two cells where you have these 19 point scores). If you get FALSE,
    >>then they are not the same and the RANK function would rank them
    >>differently.
    >>

    >
    > Yes, the two 19-point scores are in S19 and S20. Entering =S19=S20 in a cell
    > returns TRUE. <chuckle> What now?



    =S19=S20 proves nothing. Try =(S19-S20) instead.

    Excel (and almost all other software) follows the IEEE standard for
    double precision storage of floating point numbers. Most decimal
    fractions cannot be represented exactly with a terminating binary
    fraction (just as 1/3 cannot be represented exactly with a terminating
    decimal fraction). The net result is that calculated numbers that you
    would expect to be the same may not be exactly the same. Excel tries to
    help by including a fuzz factor in some operations, thus if A1 contains
    0.3-0.2 and A2 contains 0.1, the cell contents will look the same, even
    if formatted to 15 decimal places. Because of Excel's fuzz factor,
    =A1-A2 will return 0 and =A1=A2 will return TRUE. But =(A1-A2) will
    show the very small difference that correctly occurs between the binary
    values, and RANK() will distinguish them.

    ROUND() should solve the problem, as has already been suggested.

    Jerry

    http://www.excelforum.com/excel-form...ent-ranks.html

    I made the calculation surrounded by a round function and it fixed the problem.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dynamic Tie Breaker Not Workign

    Excellent.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rankif with tie breaker
    By huy_le in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-13-2014, 06:22 AM
  2. Dynamic sort not working - Tie Breaker
    By butler1012 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-04-2014, 01:50 PM
  3. [SOLVED] Multiple If statement not workign
    By cartica in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2014, 09:26 AM
  4. Rank Tie Breaker
    By Obermeyer B in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-02-2013, 10:54 AM
  5. Tie Breaker
    By allnet000 in forum Excel General
    Replies: 6
    Last Post: 02-11-2010, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1