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've a problem with scoring using the rank formula.
Example: four players score, 28,17,39,6. The 39 score wins, gets 4 points, 28 = 3points, 17 = 2 points, 6 = 1 point.
The rank formula will work this out fine.
However if you get two scores tied, for example 28, 28, 39, 6, using rank the points score for each 28 returns 2 when I actually need it to be 2.5
(Thats 3points +2points / 2).
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.
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
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.
Un-Skipping the Rank after a duplicate value to get a regular Series!
Col A------Col B------Col C------Col D
Players------Scores------Rank------Answr
SACHIN------186------1------1
DHONI------183------2------2
KAPIL------175------3------3
HAYDEN------175------3------3
SEHWAG------167------5------4
BORDER------158------6------5
RAHUL------152------7------6
LARA------148------8------7
RICKY------148------8------7
KEVIN------142------10------8
BOON------133------11------9
FLINTFF------119------12------10
BOTHAM------112------13------11
I want to prevent the skipping of numbers while using the RANK function which skips the next number when it hits a duplicate value. I want to get the rank as well as not have the next number skipped.
what formula would be used to rank scores if you had the following
name score
participant 1 14
participant 2 27
Participant 3 19
to get this result
Participant 2 27
Participant 3 19
Participant 1 14
I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:
=RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1
To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.
A source below really shows the formula should work. [URL] .......
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,"
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 want to be able to rank players only if they meet or exceed a specific number of rounds. I have been able to get close but I would like to have the ranking start at "1" instead of "0" (see sheet).
View 4 Replies View RelatedI need to combined all non blank cells in a given row into 1 cell. But within each row i have 5 phases that the values fall into, which is denoted by the 1st charter 1, 2, 3, 4, or 5. for example, in a given row i have 1-a, 1-c, 2-d, and 1-f and these values occurs in non consecutive columns starting from G to ALR. I need a macro that sorts these values in one of the 5 phases. So in another sheet the macro would combined [1-a 1-c 1-f] in Phase 1 and [2-d] in phase 2. and if there are other phases it would put them in the appropriate cell.
See attached workbook : Work Order Summary Sheet.xlsx
However, in addition to what the macro already does, it is possible to add another work sheet in the same work book that outputs the values in separate cells? It also has to output the data in order: for example, in the first work sheet the data is inputted at random and has spaces but the macro will have to remove all the blank cells and output them in order based on the first value in the test string, 1, 2, 3, 4, and then 5;
input:
blank 3-x Blank 1-y blank 2-z 1-k
output:
1-k 1-x 2-z 3-y
creating a formula that will automatically bring the cursor to the next applicable cell. We have template wherein there are extra cells in between those that we fill out. What I wanted to happen is for the cursor to go directly to the next cell that I need to fill up.
Example would be that cell a1 is where we type the name then the next cell will be c1where we will put the addreas. How will I make the cursor jump to c1 when ive filled in a1.
I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...
A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)
And so on...
Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?
I'm trying to total up some rows that are put in a repetitive order repeat. I can do this, but the method I'm using is highly inefficient. Is there some slick equation I don't know about?
Example below:
Worker 1 - Double Time - each of these has 200 columns for different job codes
Worker 1 - Over Time
Worker 1 - Regular Time
Worker 2 - Double Time
Worker 2 - Over Time
Worker 2 - Regular Time
Worker 3 - Double Time
Worker 3 - Over Time
Worker 3 - Regular Time
...and so on to 1000 workers
Total Double Time - for each job code
Total Over Time
Total Regular Time
I was working on a project this morning (I actually got help here but this is more or less unrelated) and ran into a strange problem. It seems the For loop that I was using is skipping cells.
Was trying to figure out the problem using this macro and a list or random garbage.
Private Sub CommandButton1_Click()
Dim number As Integer
For Each Cell In Range("A2:A500")
number = WorksheetFunction.CountIf(Range("A:A"), Cell.Value)
Cell.Offset(0, 1).Value = number
If number > 1 Then
Cell.EntireRow.Delete
End If
number = 0
Next Cell
End Sub
Maybe someone who actually know something about programming can straighten me out.
Attached is a sample spreadsheet. Please look at the spreadsheet will reading this post, as it would be rather confusing otherwise.
From this spreadsheet, I need to create two new spreadsheets (I assume one at a time is best). One (S1) will contain data from rows where Column C = "Regular Hours". The other (S2) will contain data from rows where Column C <> "Regular Hours".
The format of S1 (3 columns) is: Payroll # (Column B), code for the type of hour (this code I will need to write into the VBA code), the number of hours (D:G,Q:T,AD:AG), and the constant "N".
The format of S2 (4 columns) is: Payroll # (Column B), code for the type of expense (this code I will need to write into the VBA code), the dollar amount (I:P,V:AC, AI:AP), the project (Column C), and the constant "N".
The trick is each row on the new spreadsheets can only contain one hour/expense (otherwise this would be easy!). So, I need to loop to copy/paste the right cells, and need to skip over blanks, and need to end at column AP, then continue to the next appropriate row. As well, columns H, U, and AH are not used on the new sheets. I have another spreadsheet slightly similar to this with looping, but it is a delete type loop, not a creation one, and it doesn't have to have seperate rows like this does. I know this one is a bit big, but I know someone out there can figure out some looping code that can handle this.
Every time i do a filter and copy a data. Keep the filter on and past in another column. The data does not copy just into the visible cells. It pretend the filter is not there.
I have tried selecting only vible cells when copying and pasting into cells by selecting only visible cells but does not seem to work. Get the error message "command cannot be used on multiple section". There must be a solution but looked on the web and really cannot find one.
Table below so i filter out the "a" so i just have numbers then want to past into vible cells.
CopyPaste ResultsResults wanted
111
a2
232
a4
33
a
44
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
I have a macro I run on thousands of rows of data which occasionally has incorrect line breaks that need to be brought up to the previous row. The macro works perfectly, however I'm trying to speed it up by skipping rows that do not need processing.
The macro inserts a check in column AQ to see if column A meets certain criteria. If it does not, "False" is entered in AQ, indicating the need for processing. As it runs now, the loop steps through each row of AQ to see if it needs further action.
I am trying to come up with a way for it to automatically just jump to "False" instances, skipping sometimes thousands of rows of blank cells that need no processing.
[Code] ......
Here are my cells and values
B38 C38 D38 E38 F38 G38 H38 i38 J38 k38 L38 M38 N38 O38
0.00
None
1.95
16
1.73
21
0.00
None
20.10
20
1.73
20
0.00
None
What I would like to do is average only every other cell AND also not count zeros. So =average(B38,D38,F38,J38,L38) generally the idea not averaging H38 & N38 since they are both zeros.
I am using excel to analyze stock changes. Yahoo spits out a column with dates and another with closing prices but occasionally inserts a row showing a dividend, something like this:
Date Closing price
12/30/13 120
12/23/13 118
12/16/13 119
12/9/13 117
12/2/13 115
11/25/13 116
11/22/13 dividend 0.23
11/18/25 116
11/11/13 115
In the spreadsheet, column A is date and Column E is closing price, such that the cell in column E where the dividend is listed is blank.
The problem- as I update data each week, the dividend row moves. How can I consistently figure price changes for 4 weeks and 13 weeks without getting messed up by the dividend row? Is there to auto delete the row or make the formula count down 4 cells in the price column, while skipping any cells without data?
I have a table showing accesssibility of given fruits in some particular shops. What I need is to list the fruits available in every shop right after the shop's name, but skipping any empty spaces (i.e. unavailable fruits. See the example, it's manually entered though).
View 2 Replies View RelatedThis was so easy to do in excel 2003...However im now using 2010 and i cant figure out how to tell excel to ignore the empty cells for a scatter plot.
View 3 Replies View RelatedI have a spreadsheet with temperature data, all in one column (D), that was recorded every 10 minutes for several months. In two adjacent columns (E & F), I would like to record the daily max and min temperatures. I can manually do this with MAX and MIN formulas in column E and F respectively, MAX(D1:D144). But when I copy this formula to the next row, I get MAX(D2:D145) when what I really want is MAX(D145:D288)....and so on...
View 1 Replies View Related