1. W

    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...
  2. T

    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...
  3. A

    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...
  4. W

    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...
  5. T

    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...
  6. Z

    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...
  7. J

    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...
  8. S

    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...
  9. A

    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...
  10. R

    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...
  11. 0

    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...
  12. T

    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...
  13. M

    PERCENTRANK by group excluding another group

    I am trying to calculate the percentrank of each annual salary by salary grade level but need to exclude for instance departments that start with "IT ". For those who are in the IT department I would like to see blank and make sure their number is not part of the percentrank calculation. So...
  14. B

    Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula

    Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula 1. In a single formula, I am trying to get the Rank (Spearman) correlation between two arrays. One of the arrays contains non-adjacent values. I can get the Pearson correlation with this formula...
  15. A

    Conditional Rank without duplicates

    Hi Excel friends, Looking to solve this problem without using an array formula: name value Conditional Rank (formula required to solve for this column) a 2 1 a 2 1 a 7 3 a 5 2 a 5 2 a 15 4 b 15 3 b 15 3 b 17 4 b 2 1 b 11 2 Basically I want a formula to give me...
  16. DRSteele

    Ranking with Tiebreakers

    Using tiebreakers is frequently required in sports leagues and sales data to come up with winners and losers. We can use a clever trick to rank with tiebreakers, a process otherwise made difficult by the many conditions. Here we have sales data for automobile salesmen, and we want to grant...
  17. R

    Single Ranking with Two Data Sets in One Column

    I'm trying to rank scored participants from highest score to lowest with one added criteria. Those who fouled, designated by an asterisk before the score, are still given a score but ranked lower than all the ones who did not foul.
  18. W

    RANK gives different rank for same value

    Hi guys, I'm getting totally crazy over the RANK-function. In this sheet, B2 = RANK(A2,$A$2:$A$3) and B3 = RANK(A3,$A$2:$A$3), and as you can see, it gives me two different ranks for the same value. These are constants (not formulas) and prove that the value is the same, formula in C2 =...
  19. M

    Ranking using VBA

    I'm trying to rank a list of values using VBA rather than a formula as the numbers will be continually changing. I'm basically trying to run the code at a set point to rank the lowest 3 numbers on the sheet and leave them set rather than recalculating with a formula. Thanks
  20. B

    Rank and Count Formula

    I'm trying to create a formula that will rank and count responses to survey data that will summarize the top 5 in the format below. The data will be structured similar to the table below with new responses being added to the bottom of the table continually. For this example, the data is in...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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