Replace values with a value indicating ranking, based on the values, within each group

Try this in E21 dragged down:

=1+SUMPRODUCT(($A$4:$A$15=A4)*($E$4:$E$15>E4))&"/"&COUNTIF($A$4:$A$15,A4)

Caveat: this ranks in the traditional way, i.e. if there is a tie for first place, the next ranking will be 3, not 2. Also it will not ignore blank cells. Maybe you or somebody else will be able to refine it suitably.

I cannot help with the modal formula as I am afraid I don't understand the maths behind it.

By the way, I have visited Lebanon (and Beirut) a few times - it's a beautiful country. My daughter is half Lebanese.

A modification of AliGW's solution. This is closer to your requirement (Note that there was a mistake in the expected answer in E37-E30: it should have been /3 and not /2).

In E21, copied across and down:

=IFERROR(IF(E4="",NA(),1+SUMPRODUCT(($A$4:$A$15=$A4)*(E$4:E$15>E4))&"/"&COUNTIFS($A$4:$A$15,$A4,E$4:E$15,">"&0)),"")

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

APT. Also... thanks for the Rep. can you try to explain what you meant by the second part of your query. I didn't really follow what you wanted....

AMAliGW and Glenn Kennedy,

Thanks for your help. I ended up using Glenn Kennedy's solution and it worked out great.

In regard to the second portion of my question, I want a formula that calculates "Modal Rank within Group". The resulting number would give a notion of what the most common ranking, for that give item, is across all shops. For example if the most common ranking of an item, ignoring the denominator (i.e. no. of items within each group), is "2" then I'm calling this number the modal ranking within the item, across shops. This would be the case in this set for example: {1/2, 2/3, 4/5, 2/4, 2/5)}

The difficult here is that while in ShopA item 3 (from group 222) may be ranked as 2/3, in ShopD it is ranked 2/4, while the other rankings for this item are: 5/5 in ShopB and 4/5 ShopC. Thus, it's not entirely clear to me that saying the 'modal ranking' is 2, since I'm ignoring the denominator. This is a more of an issue I will have to figure out, but any suggestions are welcome! (perhaps some sort of average? Note: non-math person, here!)

Any suggestions to generate the modal ranking? I tried the following, which ignores the denominator (i.e. no. of items within each group): {=MODE(MATCH(E21:I21,E21:I21,0))}
..yet it fails in most cases.

Thanks again!

PS. AMAliGW, indeed, Lebanon is nice. Glad you liked it!

This looks worse than it is, because I had to strip out all the /n values... another array formula:

=MODE(IF(IF(ISNUMBER(--LEFT(E21:I21,SEARCH("/",E21:I21)-1)),--LEFT(E21:I21,SEARCH("/",E21:I21)-1),FALSE)>0,IF(ISNUMBER(--LEFT(E21:I21,SEARCH("/",E21:I21)-1)),--LEFT(E21:I21,SEARCH("/",E21:I21)-1),FALSE)))