Sort By Rank Accounting For Ties

Jan 2, 2010

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.

View 9 Replies


ADVERTISEMENT

Rank A List With Ties?

Jul 15, 2012

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

View 6 Replies View Related

Breaking Ties In Rank Function

Jun 21, 2014

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.

View 14 Replies View Related

How To Break Ties In The RANK Function

Dec 8, 2008

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

View 7 Replies View Related

Rank Function And Percentage Ties

Jun 23, 2009

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.

View 9 Replies View Related

Rank Formula Error With Same Values (Ties)

Feb 22, 2008

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.

View 9 Replies View Related

Ranking Visible Cells With Ties Without Skipping Rank

May 19, 2014

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

View 4 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

View 5 Replies View Related

Sort Rank Data

Jan 20, 2010

I have a table that goes from columns A-AF and Rows 5 - 18
In column A I have a name and in AF I have a Rank based on the results of other factors and calculation in colums B-AE.

I would like to ahve another table under my current table that will sort the rank is order 1-14. and have the store and the place it is rancked in order.

I was hoping it was a simple Vlookup formula but I cannot seam to make it work.

View 9 Replies View Related

Sort Names Based On Rank.

Feb 12, 2009

See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.

View 2 Replies View Related

RANK With Several Conditions (like For The SORT Function)

Feb 5, 2007

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)?

View 9 Replies View Related

Count Duplicates In A Cell Then Rank Them And Sort Them

Sep 13, 2006

I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.

The number order of precendence is 0-9 with zero being the lowest.

Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.

I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)

When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.

Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.

View 3 Replies View Related

Ranking - Ties

Jun 17, 2009

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

View 11 Replies View Related

Find Top X And Ties

Jul 6, 2006

Range("A4:D23") = Sheets("410").Range("A2:D21").Value '410
Range("F4:I23") = Sheets("305").Range("A2:D21").Value '305
Range("K4:N23") = Sheets("Trucks").Range("A2:D21").Value ' truck

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.

View 6 Replies View Related

Top 10 List Based On Criteria With Ties

Jul 10, 2012

Suppose I have a table of data such as the one below.

ABC
CarolineRed Team90
NatalieBlue Team13
AmyWhite Team42
AlexaBlue Team58
LaurenBlue Team75
CaitlinRed Team66
ChristyWhite Team42
AnnieWhite Team36
NicoleRed Team90

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.

View 1 Replies View Related

Formula To Show Top Scores - Including Ties

Jul 17, 2008

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.

View 9 Replies View Related

Return Nth Largest Value But Ommit Zeros & Ties

Oct 4, 2009

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.

View 4 Replies View Related

Decimal Places For Close Ties Of Numbers Based On Cell Value

Apr 15, 2008

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?

View 4 Replies View Related

Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee
Rank

Vertox
500
4

BV
1520
3

[code].....

View 4 Replies View Related

Accounting Prepayment Schedule

Jan 28, 2010

I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.

For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.

I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.

View 7 Replies View Related

Copy Row - Accounting Monopoly

May 20, 2014

I am trying to make an accounting spreadsheet to be used with the game monopoly for my business students.

Each row I want to copy to a separate sheet within Excel.

For instance the row with the account title cash I want that to copy entire row to the cash worksheet. "go" revenue to the "go"revenue worksheet etc.

Date
Account
Debit
Credit

May 20
Cash
200

[Code] .......

Here are the sheets I set up to transfer to depending on the account title:

Cash

Properties

Acct.Pay. Monopoly Loan

"Go" Revenue

Rent Revenue

Misc. Revenue

Rent Expense

Misc. Expense

View 4 Replies View Related

$ Not Showing Up In Accounting Format

Jun 26, 2014

I have formatted several cells with an Accounting style. However, the dollar sign doesn't show up until I click in the command line.

View 5 Replies View Related

VBA Find Zero Value (Dash) In Accounting Format

Aug 15, 2013

I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.

The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.

My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?

Below is a strip down version of the code.

VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]

View 5 Replies View Related

Using IF Formula And Accounting For Zero Or Null Cell?

Dec 4, 2013

Looking for a way to correct what is going on with row 9. I need it to return either zero or empty, but also include the current formula.

View 3 Replies View Related

Adding Columns To Accounting Template?

Jun 25, 2014

I just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.

View 8 Replies View Related

Accounting For Multiple Possible Values In A IF(AND Statement

Dec 4, 2008

IF B1 has a possible value ranging from 1 - 5, and IF the value in E1 is equal or great than 2,5,10,10,15 BUT 2,5,10,10,15 need to match to specific ranges set in B1 1=2, 2=5, ,3=10 ,4=10 ,5=15 THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"

Got help earlier with this formula: =IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great! This was my attempt at expand that formula:

View 3 Replies View Related

Using The Accounting Format With No Decimal Places

Jan 15, 2009

I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:

View 4 Replies View Related

Setting Format Cell Accounting Using Vba

Mar 26, 2009

i am doing some calculations using vba in excel, i need to know how to set the cell format to 'accounting' in vba ?

View 2 Replies View Related

Modifying Accounting Custom Format?

Oct 31, 2013

I'm using this custom format

Code:
_-£* #,##0.00_-;[Red]_-£* #,##0.00_-;_-£* "-"??_-;_-@_-

How do I modify it so that if the value is 0.00 then 0.00 is showed and not the current "-"?

View 3 Replies View Related

Array Formulas And Accounting Consolidation?

Nov 16, 2013

I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.

View 5 Replies View Related







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