rank

1. DAX TOPN function

Hi, I've been trying to use topn to get the Nth hightest, selling item, I've tried ; EVALUATE TOPN(4,VALUES( Table1[Item]) ,[totalUnits] ) and then thought to wrap this in a MINX to get the 4th top Item,, MINX( TOPN(4,VALUES( Table1[Item]) ,[totalU] ), Table1[Item] )...
2. Ranking based on Conditional Formatting colour scheme

This might be very trivial but I can't for the life of me work it out. 8-12 cells affected, using default conditional formatting rules 'Format all cells based on their values' - Format style '3-Color Scale' and midpoint Percentile '50'. Maximum and minimum are left as 'Highest value' and...
3. Slicer Problem with Rank Function

Hi Everyone, I am working on a practice file with help of a video explaining every step to create a dashboard file. Everything went fine and clearly understood until I came to the Rank function to get the top rank of the products. The function worked fine but when I click on any slicer to...
4. Rank sequentially with same rank for ties, breaks when sorting

I'm trying to come up with a function that ranks scores sequentially with ties receiving the same rank. I've gotten really close by using: =SUMPRODUCT((I5>=I\$5:I\$129)/COUNTIF(I\$5:I\$129,I\$5:I\$129)) In fact, this works perfectly when sorting smallest to largest: In a ranking of 125...
5. Excel Ranking Formula with Unique Records

I want top 3 Unique students ranking filtered from records below along with their details and all the other roll numbers excluding the ones that are once already mentioned once in the left Only that appeared on top 3 in the last column (with Comma separation) D1125A8:I20,J11:N20Cellcontains a...
6. Rank products by date

I have lost hope of me being able to work this one out, although sure easy for you all I am trying to rank each product individually by date in excel, so effectively each product in the list will have its own rank by date. For example Tomato Sauce rank 1, 2, 3, 4 etc. Black Bean CPU rank 1...
7. Rank based on two criteria without ties

Hello guys :giggle: Hope you have a nice day! I need to rank projects in descending order based on: 1. Their scores 2. If their scores are a tie, compare their YTD scores - the higher score the better rank 3. If their YTD scores are also a tie, just randomly assign their ranks *I want the ranks...
8. If Two cells match, output two values

Hello, I would like to look in a range to see if two ranks match, if they do I would like excel to output both team names into two separate cells. For example in my image I would like to get Team 1 and Team 2 as my output but separate cells. I believe I need an array formula? but I cannot...
9. Rank by group (3 columns)

Hi all, I have read a lot of the forums to related about rank by multiple criteria, but I still not work for me. I would like to rank by value sales (column F) based on Shop, Category and Period. I have already input the expected rank in last column, please kindly help me to solve this...
10. Rank a subset with duplicated values defined by another column

Hello, I'm trying to create a column with a rank of values based on a control column. I've posted this in StackOverflow but I feel this forum is more appropriate for the topic. My data: (A) (B) (C) Value Control Rank 100 1 8 200 1 6 200 1 7 300 0...
11. Need Help on Sumif using rank based on criteria (which changes)

Hi Team, This is my first post and apologies if I am overstepping any rules here. I have gone through similar posts, but didnt find a way forward. There are similar threads, but the issue here is a bit different. The problem Statement is as follows - I need to calculate the time taken before a...
12. Cumulative sum Value by rank and per group

Hi everyone, I have to put an evaluation to the certain data set (ex : image below), and I'm attempting to create a summary for the cumulative sum of value by rank per group. Column A is the number of data Column B is the Group of each data Column C is the value of each data Column D is the...
13. Random Sort People into Groups of 4

Hi All, I have been working on sorting members of a club into Random Names into Groups of 4 People. I have 2 sheets that I use for this. This is sheet 1 below for the purpose of this I have used animals instead of names. Following Formulas used below: Column E Copied down to E28 First Row 5...
14. How do I pick out top 5 and bottom five from a long list

Hello. I have, say, used RANK in column A to rank 100 products profitability. In column B I have used the formula next to each rank number: IF(AND(A1>=1,A1<=5),"Gain","") to pick out top 5 performers i.e. 1,2,3,4,5 from the ranked list (1 being the best). I now want to also pick out the top...
15. Duplicates in RANK

Hi there, So i have a bunch of stock price returns that i am ranking relative to one another. I am then trying to assign weights based on the highest 2 and lowest 2. These should sum to net 0. However, i occasionally have two stocks with 0% returns. This causes an issue whereby RANK obviously...
16. Ranking Without Duplicates and NOT Skipping Ranks

Hi there, Apologies if this is straight forward but I can't seem to arrive at a solution here. Trying to produce a rank for a column (column AA based on column Z in attached image) with unique ranks (no duplicates) and with no skipping rank numbers, currently using the below standard formula...
17. Query regarding RANK/COUNT based on occurrence

I have same data where I want to count how often a reference occurs, and then rank it by a specific stipulation. Below is my example. Column A Column B What I want column C to show ABC 00:00:00 1 ABC 00:04:00 2 BAC 00:00:00 1 BAC 00:03:50 3 BAC 00:02:03 2 So the formula should...
18. How to find top positions basis multiple criteria while handling ties

Hello All, I'm looking for excel formula for following set of data: I'm having Names in Col A, Scores in column B and quantity lifted in Col C. I need to find top 3 positions. First data needs to be scan through Col B and find highest score and if there is a tie, than Col C should be considered...
19. Formula Sorting and nested IF statements

I am working on a project for an event where contestants time is recorded real time and depending on all the other contestants scores (as they are entered) a "place" and a "score" are awarded. 1st, 2nd, 3rd, etc places are awarded points if they are members, based on the contestant with the...
20. Rank duplicate values with COUNTIFS

Hi experts, I have a data set looking at 3 types of fruit, their order dates, and order cost. I want to rank the line items by earliest date. However, since some orders have the same order date, I get duplicate ranks (see two rows of Apples ranked as #4 in the attached image). I am using a...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back