I'm having problem with ranking results (ascending) with similar values e.g. values 1, 2, 2 and 4 fails by use of Rank-formula (rank: 1, 2, #N/A and 4) as value 2 appears twice.
I've attached a simple example of the problem.
I'm trying to come up with a formula that assigns ranks to an ascending list of integer values where ties are resolved by assigning tied values the mean of the ranks they would have received if there were no ties, e.g., 117, 119, 119, 125, 128 becomes 1, 2.5, 2.5, 4, 5. (If the two 119s were not tied, they would have been assigned the ranks 2 and 3, yielding a mean of 2.5.) My list looks like this:
Column_A Column_B
1
1
1
2
3
4
4
5
...
I am trying to break ties in the Rank function.
Example worksheet attached.
I have an eight digit number (a digit can show up more than once) and determine the rank of the sum of each number. I had the ties broken by whatever digit showed up first.
In the following example, June 13th row showed 6 digits tied for second place and the tie was broken by the order in which the digit show up in the range.
On the June 14th row it showed digit 4 and 5 tied with 3 occurrence each (running total for the two days). But digit 4 came in twice that day, and reach a total 3 first before digit 5, but Rank2 shows that digit 5 was ranked first, which should of been digit 4.
I am using the RANK function to rank the data in several rows. There are some times ties in the result.
I want to break that tie by taking the greatest value in a different cell in that row
Greatest value wins the tie.
same situation if there are multiple ties (4 "RANK"'s of position 2) ...
I am a meteorologist and I keep track of my forecaster's monthly statistics. One area I track is percentage of warnings issued with desired lead time. I calculate and rank the individuals by percentages.
Example:
Forecaster A: 3 of 4 75% Rank 1
Forecaster B: 1 of 2 50% Rank 2
Forecaster C: 0 of 3 0% Rank 3
Or if ties:
Forecaster A: 6 of 6 100% Rank 1
Forecaster B: 3 of 3 100% Rank 1
Forecaster C: 1 of 2 50% Rank 3
Is there a way in Excel in case of ties, to award a higher rank (1) to the individual who went 100%, 6 for 6, compared to the individual who also earned 100%, but only 3 of 3.
More reward for more chances and opportunities.
I have a custom sort order that I use for sorting military ranks in a number of massive spreadsheets I am using. Normally the data we get has a long version for the ranks (column 1) and a rank code (column 2). When the rank code is there it is simple to sort by that column in descending order, but without it I need a custom sort. The problem is there are up to three equivalent ranks at each level across Army, Navy and Air Force as you can see in the attachment.
Is there a way I can set up a custom sort based on both columsn of data but sorting only on the second column. I could do vlookups but there are up to 50,000 rows and I don't really want to keep that formula there, and as I use it across multiple sheets (yes I know all one sheet, but it is segregated data and used in different situations) including a vba written value would have to occur for every row on every sheet - not the best option in my opinion - maybe it is.
I have data that has filters and hidden rows and my current formula ranks with ties which is what I want, but I do not want the ranking to skip numbers.
Value Rank Now Rank I want
508 6
1001 1
696 4
704 3
833 2
557 5
1001 1
704 3
508 6
I had tried ranking formula to rank employees on some cirteria it was working, but if there is any tie between two employees then the next rank is not displaying for example in Ach Rank : rank 5 is missing because there is tie is between two employees is rank 4. Request your suggestion and help on this for overcoming the issue.
Formula used for ranking : =IF(B2>=75%,RANK(B2,$B$2:$B$41,1)-COUNTIF($B$2:$B$41,"
I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.
I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.
ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023
I ran my macro on a spreadsheet and it was fine.
But when I ran it from another spreadsheet I received the below error.
i.e. I placed the macro in book1.xls & from there open book2.xls.
ERROR:
HTML Run-time error '1004':
Application-defined or object-defined error
The error pops up when the macro reach the line "Rows ("3:3").select".
I have three different values in three different columns and I want to find the maximum amongst the 3 values.
A1 B1 C1
100 50 40
in D1 I have written a formula
=MAX(A1, B1, C1)
But it is returning an error saying formula you typed contains an error
I Spoke too soon here, I am getting value errors, how to do it with conditional formating. I tried if cell value is =to #DIV/0! then white (dosent work). Richard this dosent seem to work mate =IF(SUM(F6:V6),"",AL6/SUM(F6:V6))
View 9 Replies View RelatedI have 10 sales figures in C2:C11.
In D2:D11, I want the rank of those values, with one twist.
If the value in B2:B11 is X, then that value is excluded from the ranking.
I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))
Which formula can I use to rank the people below.
Consider that the lower the value on attendance and cancellations is, the better this number will be.
In the other cells the highest the value, the better the number is.
NameAttendanceUtilizationProductivityQualityCancellationsCharles089.38%26.60NA0Nick587.69%56.921001Brian384.51%51.32800Joe080.71%36.151000
Using formula, how do I rank
CAM100-24
COM98-21
CAM100-18
CAM27-37
COM121-5
COM98-23
so it first sorts in alphabetical order using the string before the "-" i.e. CAM27 comes before CAM100, and then by the biggest 'secondary values' (the value after the "-") so that COM98-23 comes before COM98-21?
I have a list of values (some are duplicates) which I need to rank.
The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.
Example:
Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4
Got a question about RANK. As you can see from the example below, I want the rank of 1 to be 6, not 7 - same values keep the same rank but the next value takes the next rank number.
Is there a way to achieve this - I need to rank around 4000 lines...
Anyone know of a function that will return a rank of cell C3, in range C3:C800 that does contain a few #N/A values?
View 9 Replies View RelatedIn a range of 27000 records I dlike to find the lowest 5 values excluding, div0, N/A and other error things like that
View 9 Replies View Relatedam currently setting up a fantasy football league.
i have set up the league, with team name overall points and position, my problem is that when 2 teams have the same points one team is omitted and the other team has its name on both entries
the formula's i am using are
vlookup = to find the name of team for the points
large = to find out points of each team
I have two sheets of data and would like to rank according to the order in which the data fit on both sheets. Rank function only seems to allow one sheet at a time.
View 9 Replies View RelatedI have an issues with a formulae I have written to measure two factors;
A; How many different countries appear within a certain date range. B; What countries are they.
I tried a unique list, but could not get it to work within a range? I dont particuarly care how the data is pulled through as long as I do it. At the moment I have become stuck in 'Unique Ranges' and have n given up?
The formulae issue is in the tab "Problem_Formulae", the dat in the sheet 1, and the date ranges in sheet 2. Also, just to make life a bit more challenging pivot tables are a no go .
Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.
OhioBlockGroup.xlsm
I'm using a Large function embedded in a Match function to a ranking.
The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once.
I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.
I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).
If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls
here is some code that I have,
Dim zima As Range
Dim Col As Long
On Error Resume Next
Set zima = Application.InputBox("Select column to paste to", Type:=8)
On Error GoTo 0
If zima Is Nothing Then Exit Sub
[code]....
Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?
I recently made a formula of net sales contribution (sales per product category/total sales). For weeks 1.2008 to 30.2008. But I have several product categories. I would like near the value of the formula (not in another cell!) to insert a rank of these contributions. I d love to have the rank inside a parenthesis() near the percentage.
Here is the format
NET SALES CONTRIBUTION PER PRODUCT HIERARCHY 239.200740.200741.2007
COM RETAIL MEDIA0,03%0,73%0,22%
DTM STORAGE MEDIA0,00%0,06%0,00%
Of course I dont know how to do it.