LinkBack URL
About LinkBacks
Hi
I am looking to rank the data in the far right column but need to;
- Ignore zeros
- Has positive and Negative values which I want to rank together
- Data contains duplicates so needs to give unique rank for these
Sample attached....
Martin
Hi
I am looking to rank the data in the far right column but need to;
- Ignore zeros
- Has positive and Negative values which I want to rank together
- Data contains duplicates so needs to give unique rank for these
Sample attached....
Martin
Last edited by Martin85; 03-28-2022 at 04:50 PM.
Try:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi Martin and welcome to the forum,
Try this in I3 and pull down:
Formula:Please Login or Register to view this content.
If you want it to start at 1 instead of zero then try this:
Formula:Please Login or Register to view this content.
Countifs and no zero.xlsx
Last edited by MarvinP; 03-28-2022 at 04:24 PM.
One test is worth a thousand opinions.
Thanks, but not quite what I was looking for this sorts them but I want to pull the ranked data in to another table including product names so was looking for a way to do this within a rank formula or something similar.
Currently using the following but it ignores the zeros and any negatives end up at the far end of the rank of over 600 lines.
IF($M143=0,"",IF(M143=0,"",RANK(M143,M$5:M$624,0)+COUNTIF(M$5:M143,M143)-1))
Thanks for the welcome and the formula, looks like that works, the formula that you have given ranks from smallest to biggest can that be flipped around within the count if?
Apologies MarvinP this works for the positives and negatives but doesn't treat duplicates as unique references, apologies I left this out of my original post, now amended.
What do you mean by rank "together"? Do you mean rank ignoring the sign?
e.g. do you want to rank 2,1,-3,-4 or -4,-3,2,1?
It sounds like a slight tweak to TMS's solution is what you want:
=LET(a,UNIQUE(SORT(FILTER(B:H,(H:H<>0),(H:H<>"")),7,-1)),IF(a=0,"",a))
Though I'm not entirely sure how you want to define duplicates (eg whole row unique, or just product). It would be helpful if you put in the results you are after (not with a formula) for some example situations.
Hey Martin,
After working on this for a while, I realized I need a better example with zeros and duplicates and what you are pulling from the other table and products, etc. I think we can get a great answer if we see a bigger picture of the problem.
Hi All,
Thank you for all help.
I have attached an extended sample with duplicates and zeros in the data with both the countif result and the result I want hardcoded to one side.
With reference to the duplicates I wanted to be able to rank them uniquely, with all data going from highest to lowest so that I end up with a rank for all items that are not zero. These non zero items are then looked up on another table which pulls through the items for each catagory giving product name, qty and price so whatever solution I apply I need to be able to keep the data together so that the name etc can also be retreived.
Hope this makes sense.
Last edited by Martin85; 03-29-2022 at 04:07 AM.
I3:
Drag down
Please Login or Register to view this content.
Quang PT
Thank you, can this be done without the absolutes, realised in the example I gave you it ranked the negatives the wrong way around so -12 should be below -10? Apologies for the confusion
Maybe try to remove abs?
Please Login or Register to view this content.
Then skips the ranks for the zeros so end up with 1,2,3,4,8,9
Could you put my formula in file and manual type expected ranking then upload again?
Maybe this:Formula:Please Login or Register to view this content.
Think I have found the solution using a combination
IF(H3=0,"",COUNTIFS($H$3:$H$11,">"&H3,$H$3:$H$11,"<>0")+COUNTIF(H$3:H3,H3))
Thank you all for your help
Or, the COUNTIF modified.
Formula:Please Login or Register to view this content.
Ah yes, I could have cancelled the +1 and -1
Formula:Please Login or Register to view this content.
if there are same values in multiple rows, this formula gives same rank to all. Is it possible to give separate ranks to same values? For example if my data is 6,5,4,4,0,-2,-4,-4 then in descending order, my rank should be 1,2,3,4,,5,6,7 i.e. not duplicating rank while skipping rank for 0.
vvisheshvv
Hi.
It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. Why is it a rule?
1. It helps prevent massive confusion arising - which will happen if it is not clear which question is being answered.
2. The forum provides a public database for questions and answers. One thread = one question. Your problem will almost certainly be somewhat different.
So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).
A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks