Criteria based ranking

[andrei461]

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

[Bo_Ry]

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)

[kvsrinivasamurthy]

ARRAY formula in F4 then copy down.

Please Login or Register  to view this content.

[andrei461]

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.

[andrei461]

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.

[Bo_Ry]

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)

[andrei461]

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.

[Bo_Ry]

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)

[andrei461]

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??

[kvsrinivasamurthy]

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

Please Login or Register  to view this content.

[Bo_Ry]

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)

[andrei461]

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.