+ Reply to Thread
Results 1 to 4 of 4

Rank Formula not working in Excel 2007

  1. #1
    Registered User
    Join Date
    05-05-2009
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    10

    Rank Formula not working in Excel 2007

    I have a list of 22 customers and their associated ranks in the adjacent column.

    For some reason the rank for the very first customer is not working ( # N/A). The formula I am using is: =RANK(N6,$N$7:$N$28,0)+COUNTIF($N$7:N29,N6)-1

    As you can see the formula (or it seems) to be working fine on the other lines.
    *Col N is based on =SUM(B6:M6) (copied all the way down)
    *Col O is based on =RANK(N6,$N$7:$N$28,0)+COUNTIF($N$7:N29,N6)-1

    One thing to make mention, I have copied this entire spreadsheet from a previous year to get the basic format setup.

    Please see attached.

    Thanks for the help.
    Jason
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Rank Formula not working in Excel 2007

    your formula in O4 Should be
    =RANK(N4,$N$4:$N$25,0)+COUNTIF($N$4:N25,N4)-1
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Rank Formula not working in Excel 2007

    In the attached sheet it seems that the data to rank is in N4:N25 in which case you probably need this formula in O4 copied down

    =RANK(N4,$N$4:$N$25,0)+COUNTIF($N$4:N4,N4)-1
    Audere est facere

  4. #4
    Registered User
    Join Date
    05-05-2009
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    10

    Re: Rank Formula not working in Excel 2007

    Got it..Thank you both very much!

    JC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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