Hi Guys
I am looking for Rank if formula which break the ties and ignore zeros.Below is the output required
Col A Col B Col C
Model Values Rank Output
Model 1 1 4
Model 1 2 3
Model 1 3 2
Model 1 4 1
Model 1 4 1
Model 2 0 0
Model 2 0 0
Model 2 1 3
Model 2 2 2
Model 2 3 1
Request for help, and how to reverse order of the rank?
This is a little confusing. You ask for a tie break, yet you have two model 1s with the same value (4) and the same rank order (1). So, are these to be shown as being of equal rank, or the tied result broken?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn
That two 4'ws was intentional.thats where i had got stuck.
Like in above case i want equal rank in 4 (i.e both 1 rank) and for 3 i need rank 2 and not rank 3.
How to include if formula for dynamic range in below formula
=SUM(1/COUNTIF(B$1:B$100,B$1:B$100)*(B$1:B$100<B1))+1 Ctrl+Shift+Enter
You get better help on your question if you add a small excel file, without confidential information.
Please also add manualy the expected result in your file.
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Attached is the sample workbook..
Hi there. Try this:
=IF(B2=0,"",SUMPRODUCT((B2 < B$2:B$11)*($B$2:$B$11>0)*($A$2:$A$11=A2)/COUNTIFS($A$2:$A$11,$A$2:$A$11,B$2:B$11,B$2:B$11))+1)
I think you made a mistake in your example sheet. There was no tied value for 1st place, model 1.
Last edited by Glenn Kennedy; 11-16-2015 at 09:32 AM.
Yea sorry for the mistake..
Formula works fine..
Thanks a lott
Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
or you could sort the data.
you can unsort the data by sort on original
See the attached file.
This will rank with a decimal value so that there are no ties.
Formula:
Please Login or Register to view this content.
This can be sorted in order to give the following result:
Model Values Rank Output Model1 Model1 Model1 Model1 Model1 Model2 Model2 Model2 Model2 Model2
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks