Ranking By Data In Two Columns?

Apr 4, 2014

I am trying to rank via data in two columns to eliminate any tied rankings.

I need to use the data in columns "AW" & "AX" to compile the rankings.

The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.

For example:

AW AX RANK

13 89 7
13 63 4
14 89 2
14 81 1
13 90 8
13 66 5
13 58 3
13 87 6

I have attached the spreadsheet that I am using.

View 7 Replies


ADVERTISEMENT

Ranking Data With Multiple Columns?

Feb 14, 2014

I have a spreadsheet with values as per below

I can rank the data based on descending values in Columns B & C using the formula in cell B2

=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))

What I trying to do is also rank the data based on the values in column D as an additional tie-breaker

RankScore 1Score 2Score 3
1412315
2402420
2402422
4402030
5392015

View 3 Replies View Related

Ranking Two Columns Plus A Third (stipulation)

Jan 20, 2014

Running a NFL playoff pool and just actually used a previous post to finally correct my Rank formula which was great.

Basically I want to rank based on the POINTS column, and then based on the WINS column, and then based on two other things. The final tiebreakers are as follows.

1. Most points

2. Most wins

3. Picking the Super Bowl game correctly

4. Closest to the total points of the Super Bowl game (everyone has to pick what they think the total score of the game will be).

This is the formula used to Rank Points and Wins:

=RANK(M8,$M$8:$M$190)+SUMPRODUCT(--($M$8:$M$190=$M8),--(N8<$N$8:$N$190))

Can I add IF Statements to RANK formulas?

View 1 Replies View Related

Ranking With Multiple Columns

Apr 26, 2014

I have a project that I am unable to figure out the best way how to accomplish. I have five players on our basketball that are having a contest. Each player took 25 shots. I need to rank each player's shots individually. For instance, Andrew made 24 and 23 (4th and 1st attempts respectively) so he should be 1st and 2nd while Curtis would get 3rd because he was next with 22 made shots on his last attempt.

I would like to rank the numbers multiple columns (columns B thru F) in column G (not shown but understood).

If this is not possible, is there a way to list ALL the numbers in column G using a formula (the number of students will continue to grow so I am unable to simply enter =B2 in cell G2 and restart with =C2 in cell G7 (after five entries) because the number of students will grow.

A
B
C
D
E
F

[Code]...

View 3 Replies View Related

Ranking Based On Two Columns?

Jun 18, 2014

i would like to rank one table based on two columns, for example world cup group table to be ranked based on points but if two teams have the same no of points, then use goals scored column to break tie between them.

View 9 Replies View Related

Ranking Based On Multiple Columns?

Feb 14, 2014

I have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this

=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4

View 2 Replies View Related

Ranking Multiple Columns And Second Place

Mar 25, 2007

******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21=
QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price):

=IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"")

It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc)

I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).

View 9 Replies View Related

Ranking Multiple Columns By 1000th Inch

Jul 5, 2006

I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)

NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923

copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.

I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.

View 11 Replies View Related

Calculate Ranking Based On Results Of 2 Columns

Nov 4, 2008

I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)

In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.

View 9 Replies View Related

Ranking Values And Ranking Duplicates The Same Rank

Mar 30, 2014

Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

View 2 Replies View Related

Ranking Without Zero And Ranking On Multiple Factors?

Aug 12, 2014

I am facing some problem with ranking formulae. Attached is a file illustrating them.

Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.

Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.

This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.

Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.

View 1 Replies View Related

Automatically Sort Data And Ranking Based On Sorted Data?

May 22, 2012

In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.

View 6 Replies View Related

Ranking Within Subset Of Data?

Jun 24, 2014

I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.

View 2 Replies View Related

Ranking Data In Table?

Aug 19, 2014

here is an example of what my data looks like:

Book1.xls

the numerical data under the colors is the cost to make an item that color. i need a way to say that i have "n" of each color available to make and then a way to tell me which items to make which color for the lowest cost.

View 5 Replies View Related

Formula: Ranking The Data

Sep 30, 2008

Which formula do you recommend I can use to rank the below fields in yellow. I need to find the following:

1 - Which sales advisor (their name) has got the highest MPPI conversion and place it in cell K32.

2 - Which sales advisor (their name) has got the highest HC conversion and place it in cell K34.

3 - Which sales advisor (their name) has got the highest Highest Apps conversion and place it in cell K36.

View 5 Replies View Related

Ranking Without Distorting Data Order

Aug 20, 2014

Ranking in excel.

Column A - List of students Names
Column B - Grades of Students
Column C - Rank

The problem is, when I rank the students (order highest to lowest) the data in A and/or in B also moves.

What I want to accomplish is that the data input in columns A and B stay as is and Column C shall indicate what is the ranking of that student according to the number of data entry. i.e. 1st, 2nd, 3rd or 1,2,3 respectively.

View 9 Replies View Related

Ranking Portions Of Data With Same Number

Mar 18, 2013

I have 4,300 cells populated with data. I have sorted the data and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data (43 cells) will be ranked #2 etc. How could I automate this process? I was trying to figure something out with the "Rank" command but that doesn't seem to work in this situation.

View 3 Replies View Related

Group Data For Ranking & Sorting

Mar 19, 2009

I have a spreadsheet with a list of ID's in column A. In Column B I have a list of ratings. Each ID has more than one rating. For example there may be 8 rows with ID 1, 12 rows with ID 2, 6 rows with ID 3 etc......

I want to rank the ratings in Column B with highest being 1 but only when the ID's are the same. For example there would be 8 ratings ranked for ID 1, 12 for ID 2 etc.....

The problem is I am running a monte carlo simulation on the rating of around 10,000 simulations. Every simulation the rank for each row will of course change because the rating has changed. The spreadsheet has around 70,000 rows and over 3000 ID's.

How can I do this without either the computer becoming a crawl or freezing up and it not taking 30 minutes for each step of the simulation?

View 8 Replies View Related

VBA To Perform Weighted Ranking From Array Of Data

Oct 12, 2011

I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below

John / Products Owned 1 2 3 4
Shoes Socks Polish Brush Lace
Trousers Shoes Socks Belt Polish
Wallet Belt Trousers Shoes Socks

What I'm trying to achieve is this:

for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:

For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.

I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.

Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?

View 2 Replies View Related

Pull The Data From The First Column Based On The Ranking Of The Second Column

Sep 15, 2005

Item Quantity
A 2
S 7
D 3
F 6

I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc

The list I will pull this from is variable in length but in the
hundreds.

View 9 Replies View Related

Search Columns Of Worksheets / If The Columns Contain Data Then Form A List On Another Worksheet

Apr 17, 2013

I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).

What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.

View 3 Replies View Related

VBA Ranking

Jan 7, 2009

I have a sheet name "GP", and the range "A1:A5".

I need your kind help with a code that if the cells in the range become 10, then the cell across in column B will rank the order of the cell that scored 10.

ie: if A2=10 then B2=1st, A4=10 then B4 = 2nd, A5=10 then B5=3rd, A1=10 then B1=4th, A3=10 then B3=5th.

The ranking is placed according to the which cell results to 10 first.

View 9 Replies View Related

Ranking Without Gap

May 4, 2006

i need to rank database and get an output of 1, 2, 3, 4, and ect. even if the first 2 had a tie of 1; 1;. So if i have 500 records and the first 3 are tie at 1st place than the next rank should NOT be 4 but instead should be 2 and so on. Is it possible?

# Rank
65 - 1
65 - 1
65 - 1
60 - 2
58 - 3
58 - 3
50 - 4

View 6 Replies View Related

Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match

Feb 20, 2009

I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View 7 Replies View Related

Splitting Address Data From 2 Different Columns Into City And State Columns?

Dec 5, 2012

I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).

View 4 Replies View Related

Enter And Highlight Data In Columns Based On Other Columns

Jul 10, 2009

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column

"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................

View 2 Replies View Related

Move Data From 2 Columns Into Multiple Columns At Each Change

Feb 2, 2008

I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............

View 2 Replies View Related

Ranking With Two Criteria

May 6, 2014

Formulas for ranking by column "PTS" and then by "DIF" if the PTS are equal for two or more teams...

View 5 Replies View Related

RANKing Words?

Feb 9, 2009

The Excel function RANK allows one to automatically rank a number within a given range of numbers. Can anyone think of a way to do the same thing with words?

For example, imagine a list of 20 words in a column, with space to add ten more words (complete range B2 to B31, which include the last ten blank cells). If these were numbers, then in the previous column one could simply put in the RANK formula covering the full range, which would then auto update as you added more numbers to the spare ten cells of the range. Assuming that all the values being ranked are unique, and you would therefore get a unique rank number, to order them in rank order, one would simply have the list of possible rank values a column or two removed and VLOOKUP the ranks and their appropriate values.

But what if these numbers were WORDS? The first 20 words are FIXED and will not change, the next ten cells of the range could have any other words typed into them. There is no RANK function for words so how would you suggest that ranking by words is achieved, that does not require any intervention, and as new words were added to the spare ten cells in the range, then the rank would automatically update? This list of ranked words would then be VLOOKUP’d in the same fashion as above with the final list being used to fuel a drop down box, so the user would have an alphabetical listing of all the potential words in the range.

View 7 Replies View Related

Ranking To Ignore 0

Apr 27, 2009

I need the folwing formula to ignore any 0 in column Q, and rank only values of 1 or greater.

=RANK(Q6,$Q$6:$Q$20,1)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved