+ Reply to Thread
Results 1 to 6 of 6

Rank using countifs and avoiding duplicates

  1. 11-18-2014, 03:33 PM #1
    mashley
    mashley is offline
    Registered User
    Join Date
    06-18-2007
    MS-Off Ver
    2007
    Posts
    77

    Rank using countifs and avoiding duplicates

    trying to adjust this formula "IF(AE17=1,COUNTIFS($AE$14:$AE$171,1,$AD$14:$AD$171,">"&AD17)+1,"")" so that i can avoid duplicate ranks, like 81 in the attached when there are ties. Using this formula to do the rank as i am only ranking certain members of the list. This formula is in column A of Sheet1 on the attached. A simplified end result of what in need final product to be is shown on Sheet2. The data is an active OLAP pivot table, so i can not alter its output. i have tried using various formulas in a helper column, but have not been successful. the desire for a tie would be to put them ties in order of rows from lowest to highest.
    Thanks for any assistance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-19-2014, 02:07 AM #2
    pangam_s@yahoo.com
    pangam_s@yahoo.com is offline
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: Rank using countifs and avoiding duplicates

    Hello,

    Please find attached solution for your problem that is in descending order.

    Regards,
    Suhas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 11-19-2014, 09:18 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Rank using countifs and avoiding duplicates

    Sorry,Wrong posting.
    Last edited by kvsrinivasamurthy; 11-19-2014 at 09:24 AM.
    Reply With QuoteRegister To Reply
  4. 11-19-2014, 10:43 AM #4
    mashley
    mashley is offline
    Registered User
    Join Date
    06-18-2007
    MS-Off Ver
    2007
    Posts
    77

    Re: Rank using countifs and avoiding duplicates

    pangam, your solution is sorted smallest to largest, i am looking for largest to smallest. is a change possible for this?

    kvsrinivasamurthy, the workbook you provided (reattached) works with one exception, excluding abs 100%. there are legitimate times where 100% or -100% would be accepted, that was the reason for the capture column i used.

    thanks both for the time.
    Attached Files Attached Files
    Last edited by mashley; 11-19-2014 at 11:41 AM.
    Reply With QuoteRegister To Reply
  5. 11-19-2014, 11:47 PM #5
    pangam_s@yahoo.com
    pangam_s@yahoo.com is offline
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: Rank using countifs and avoiding duplicates

    Formulas length increased but perfect solution.

    Regards,
    Suhas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 11-20-2014, 01:10 PM #6
    mashley
    mashley is offline
    Registered User
    Join Date
    06-18-2007
    MS-Off Ver
    2007
    Posts
    77

    Re: Rank using countifs and avoiding duplicates

    Good to go. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using Countif and avoiding duplicates
    By dsklein85 in forum Excel General
    Replies: 2
    Last Post: 05-15-2012, 04:19 PM
  2. Weighted rank- if duplicates rank the average
    By vlady in forum Excel General
    Replies: 3
    Last Post: 02-28-2012, 09:17 PM
  3. Excel Rank Duplicates then preferred rank
    By Economic in forum Excel General
    Replies: 2
    Last Post: 04-05-2009, 07:45 PM
  4. AutoFilter - Avoiding Duplicates
    By spud2486 in forum Excel General
    Replies: 1
    Last Post: 10-17-2008, 02:53 PM
  5. [SOLVED] Avoiding Duplicates
    By Mald in forum Excel General
    Replies: 4
    Last Post: 08-01-2006, 10:40 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1