+ Reply to Thread
Results 1 to 12 of 12

Criteria based ranking

  1. #1
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Criteria based ranking

    Dear Members,

    Refer the attached sheet.

    I want to Rank ITEM in col. A. Rank 1 is to be given to any Item marked with "XXX" or "YYY" in col. E. The rest of ranking is to be given to only Urgent items (Col. C) with their date (Col. D) is greater than or equal to I2 cell.

    with the help of the excel forum i have derived an formula (Col. F) however i am not able to include a condition so that Col. E marked item should be ranked 1 then the rest.

    Kindly help me to create a formula so that i could get my ranking as desired.(Col. G)

    Thanks in advance
    Attached Files Attached Files
    Last edited by andrei461; 10-03-2020 at 10:37 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,204

    Re: Criteria based ranking

    Please try at F4

    =IF(E4="",IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4),B$4:B$23),B$4:B$23)>0))+2,""),1)
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,488

    Re: Criteria based ranking

    ARRAY formula in F4 then copy down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Re: Criteria based ranking

    Quote Originally Posted by Bo_Ry View Post
    Please try at F4

    =IF(E4="",IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4),B$4:B$23),B$4:B$23)>0))+2,""),1)
    i also thught of using IF for E4, but the issue with the formula is that though its giving rank 1 as per the Col. E entry but the ranks that is being computed in Col. F are not in series.
    Check the sheet that i attached.
    in col. F, Rank 2 is missing.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Re: Criteria based ranking

    Quote Originally Posted by kvsrinivasamurthy View Post
    ARRAY formula in F4 then copy down.
    Please Login or Register  to view this content.
    Thanks for your response.
    Your formula though working, is not able to maintain the series of the ranking. Check the sheet, u will get what iam trying to say.
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,204

    Re: Criteria based ranking

    How about

    =IF(E4="",IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4)*(E$4:E$23=""),B$4:B$23),B$4:B$23)>0))+2,""),1)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Re: Criteria based ranking

    Quote Originally Posted by Bo_Ry View Post
    How about

    =IF(E4="",IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4)*(E$4:E$23=""),B$4:B$23),B$4:B$23)>0))+2,""),1)
    U have put (E$4:E$23="") as a criteria for ranking. Thats smart. I didnt thought about that.
    But the problem with this would be it would give rank 1 to any entry in col. E, what if i only want to give rank 1 to "XXX" & "YYY", any other entry means nothing.

    Check the sheet for what i meant to say.
    Attached Files Attached Files

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,204

    Re: Criteria based ranking

    How about

    =IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,""),1)

  9. #9
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Re: Criteria based ranking

    Quote Originally Posted by Bo_Ry View Post
    How about

    =IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,""),1)
    Wow.. thats awesome it worked great. I am trying different scenarios to test your formula.

    I modified your formula to rank 1 "XXX" & "YYY" and rest is to be ranked based on decreasing Price (in continuous).

    =IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),IF(SUM(N(FREQUENCY(IF((B$4:B$23>B4)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,""),1)

    However i am not getting it. What is wrong with my edit of the formula??
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,488

    Re: Criteria based ranking

    Try this. It is working.
    Normal formula in F4 and copied down.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,204

    Re: Criteria based ranking

    Please try

    =IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),SUM(N(FREQUENCY(IF((B$4:B$23>B4)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,1)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-04-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    25

    Re: Criteria based ranking

    Thanks Bo_Ry. I found out wat i did wrong. thanks for your help.

    @kvsrinivasamurthy your formula also work as the way i intended, thank u.

    Thank u all for your responses. Have a great day ahead.

+ 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. Ranking based on subtotal sum and ignoring rows based on criteria
    By Crysaline in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2018, 07:23 AM
  2. [SOLVED] Ranking based on criteria
    By Villalobos in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2018, 10:38 AM
  3. [SOLVED] Create a table based on top ranking criteria, based on multiple fields.
    By stevensimon10482 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2017, 08:49 PM
  4. Ranking Question - Ranking based criteria but having forumla value issues
    By harp1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2016, 07:03 AM
  5. [SOLVED] Ranking based on criteria
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-17-2014, 04:22 AM
  6. Ranking Based on Three Criteria
    By annie82 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-28-2013, 01:21 AM
  7. Ranking based on certain criteria?
    By Ahmad Adha Ali in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2005, 04:51 AM

Tags for this Thread

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