rank

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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( _...

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

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

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