1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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.
  11. 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 =...
  12. 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
  13. 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...
  14. J

    Modified Rank function?

    I have worked out a UDF which replicates Excel Rank() function. Now, I am trying to modify it such that instead of outputting 1 2 2 4 as the ranks for a set {2,5,5,8} it should output 1 3 3 4; and it should be able to handle any data as per the modified logic. Public Function Rnk( _...
  15. M

    Ranking with Index Match Functions

    'm looking for some guidance on creating this lookup in excel. i have a table similar to below: <tbody> A B C D E F G H 1 STATE SalesPerson Count Percentage RANK MA TX 2 MA AA 68 99.4% 1 CC DD 3 MA BB 393 97.5% 2 AA CC 4 MA CC 566 100.0% 3 BB EE 5 TX BB 319 0.0% 4 - BB 6 TX...
  16. E

    Excel VBA replace duplicates for large formula

    Hi All, I have some percentages for 3 weeks for 3 different Areas in Area 1) B1:V100 Area 2) AB1:AV100 Area 3) BB1:BV100 now what i want to do is get the 5 largest values for each day 5 largest for each day ie B1:B100 C1:C100 D1:D100 etc Now i know i can rank them and use a countifs...
  17. V

    Custom sort order

    I have a list of products that need to be sorted in a very specific manner - the final sort order should be as described in the column titled "Overall Rank". The problem I have is that I need to first sort the products by the column titled "Date". Then, for a subset of the products (namely E...
  18. B

    Ranking with Tiebreakers

    Need some help ranking, this is just a small sample. I found a formula on here to rank the teams based on columns D & F, but can't figure out how to use L as a tiebreaker instead of order in the spreadsheet. Obviously this is just a small sample of the sheet there are more columns so the...
  19. M

    RANK(SUMPRODUCT Unique Ranking With Tie Breaker Problem

    Hi there... I am having a bit of trouble with the formula below which is intended to provide a unique (no duplicates) ranking of Productivity scores (highest to lowest) using another metric as tie breaker as needed (lowest Score is best). The formula is used in cells AM7:AM31 and the tie...
  20. M

    Ranking Groups with Multiple Criteria

    <tbody> A B C D E GROUP TARGET ACTUAL % ATTAINMENT EXPECTED RANK A 9 17 188.9% 1 A 7 13 185.7% 2 A 6 11 183.3% 3 A 12 18 150% 4 A 6 9 150% 5 B 1 11 1100% 1 B 3 9 300% 2 B 4 8 200% 3 B 1 2 200% B 6 6 100% 4 C 7...

Watch MrExcel Video

This Week's Hot Topics

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