How to Rank the Value with Respective to Name and Place
Hi All
Pl find the example below, I Would like to rank my cells with respective to Place and Name . Kindly help me
A B C
PLACE Name Marks
Chennai Style A 100
Bangalore Style A 70
Mumbai Style A 50
Delhi Style A 40
Bangalore Style B 90
Mumbai Style B 50
Delhi Style B 75
Chennai Style C 100
Bangalore Style C 20
Delhi Style C 30
Suppose your data in "A" "B" "C"
In "D2"Formula:
Please Login or Register to view this content.
copy down.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
zz.PNG
The Above Formula is not Working out . Pl find the reference image of the format which i require
I need the ranking for each style to different Places .
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I have check in your data no duplicate city & name hence showing 1
If you change to one city & one name you can get result.
See attachment. (Two sheets data)
Disregard. Misinterpreted post #3.
Last edited by FlameRetired; 07-05-2017 at 12:17 AM.
Dave
Hope this works, put in cell D2 and copied down :
=SUMPRODUCT(($A$2:$A$11=A2)*(C2<$C$2:$C$11))+SUMPRODUCT(($B$2:$B$11=B2)*(C2<$C$2:$C$11))+1
Is this what you mean?
In D2 and filled down.Formula:Please Login or Register to view this content.
Row\Col PLACE Name Marks Rank Chennai A Bangalore A Mumbai A Delhi A Bangalore B Mumbai B Delhi B Chennai C Bangalore C Delhi C
Or do you mean this?
Formula:Please Login or Register to view this content.
Row\Col PLACE Name Marks Rank Chennai A Chennai Bangalore A Bangalore Mumbai A Mumbai Delhi A Delhi Bangalore B Bangalore Mumbai B Delhi Delhi B Mumbai Chennai C Chennai Bangalore C Delhi Delhi C Bangalore
Hi Flame Retired
Thank you so Much
This what i expected.
Kindly explain the formula, how it works
Which one?
This One
Formula:
=RANK($C2,INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,0)):INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,1)),0)
I'll do my best.
The heart of this formula is in INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,0)):INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,1)).
The two MATCH function calls return the first occurrence of the Name in it's current row (match type: exact) and the last occurrence of that same Name (match type: approximate). Joined together by ":" and passing those values to the first argument of each INDEX they define a sub range within Marks that is limited to the respective sub ranges of Names.
In the formula bar if you select INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,0)):INDEX($C$2:$C$11,MATCH($B2,$B$2:$B$11,1)) and press the F9 function key you will see that sub set of Marks values. Do that in each row corresponding to A. You will see {100;70;50;40}. In each row of B you will see {90;50;75} and in each row of C {100;20;30}.
From there RANK ranks each value in each row of Marks against it's individual group and returns its rank number relative to that group (largest to smallest).
Did this help?
Yes it is
thank You so much
You are welcome. Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks