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.
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'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:
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'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 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 am dumping data out of SAP, including the following fields:
* Vendor * Material * PO # * Qty * Amt * Date
I would like to concatenate the material and vendor and pull in the PO # for the most recent date.
The VLOOKUP part is easy on the material/vendor, but I haven't had any luck in integrating a MAX, LARGE or RANK function to pull in the PO corresponding to the most recent date.
Once the PO # associated with the most current date can be identified for the material/vendor combination, I will need to utilize another vlookup to match the PO # up against a separate data file to pull in pricing. If anyone can think of a way to accomplish this in one step as opposed to multiple formulas.
I need to use the rank function to get the position of each item in a list based on its corresponding Revenue. However, the range that has the revenue numbers also contains a percentage related to that item. Basically, I need to use RANK based on a list that ignores any elements which are <= 1.
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?
In the RANK() function I can rank a value depending on one set of values (one condition), but when you SORT data you can choose a second (and a third) condition.
Is there a way to RANK with two sets of values (two conditions)?
I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete. 1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5 I have done more complex Excel formulas before, but for some reason this is stumping me.
MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)) returns 203 and IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0} when you press F9.
If I change the range to exclude the non-numeric value I get the same error (it shouldn't matter according to the help file as non-numeric values are ignored).
I have 3 columns, A B C: Distribution Center, Sales, and Top Ranked. What I am trying to get is a Rank function in the Top Ranked column that looks for the highest value in the Sales column, but returns the Distribution Center associated with this Sales value instead of the actual value. I could get this done with some helper columns and a VLOOKUP, but the report is rather large and the VLOOKUP not only slows it down but also pushes the file size over the limit. Also, I will be using this for metrics other than Sales where I'll be looking for the highest and lowest figures, so if a Max or Min function works better, that would be fine. I just haven't been able to figure out how to have it return the associated data instead of the ranked data.
I m having several problems and we will start with this one first. OK, we run machines for an 8 hour shift and these machines are down or not running for a few hours out of the 8 hour shift. I want to be able to put a number in a cell called "Delay" lets say 4:00 hours to make it easy and in the next cell over called "%" I want it to give me the % that the machine wasnt running for the 8 hours. The answer is 50%.
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
70>> "Good"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
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).
The above code get the top 20 race car drivers, based on their points,from 3 sheets and puts them onto same sheet. Column D on each sheet has their point value.
Ie. A B C D 1. Jeff Gordon 24 100 2. Tony Steward 20 92 3. Dale Earnhart 8jr. 91
my list will have over 100 Drivers already sorted in decending order by points. What I need to fix, is to select the top 20 drivers and any others who are tied for 20th place.
..Range("A2:D21") 'needs to be dynamic to select the top 20 and ties
the above will only get me the top 20. But if 5 drivers are tied for 20th place then I need then also. Remember the list is already sorted and the points are in column "D" starting in D2 and going down.
I am looking to create a sorted Top 10 list based on certain criteria with ties. For example, I would like to take the top values for White Team with both people with "42" at the top, since they are the top values.
I realize that there are not 10 values in the list above but I truncated the list for space purposes.
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.
I am trying to use the Large function to pull out First, second and third highest values in an array. problem is with ties and zeros it returns the wrong value.
I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
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.
calculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25% 32 is what percent of 25% 10 is what percent of 25%
