Free Microsoft Excel 2013 Quick Reference

Ranking/Index issue with tie breakers

I would appreciate any help on this matter. I'm having an issue with a ranking feature. For my formulas, however, I have used Index, Match and Large but I am not sure if these are the proper formulas to use. Listed on the attached file, I have names in column A followed by each persons designated points in column B. As in my example, Klyde and Alfie are in a tie. I do have three extra columns D, E and F that I wish to use as the tie breaking columns. I am not understanding how to incorporate the tie breakers into a formula. For the final rankings, I need to have the person's name listed alongside the points as shown in columns H & I.

I have read through different posts on this website and also visited Chip Pearsons website and studied the Ranking functions, but it doesn't seem to work for me. Any help on this matter would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
Hello All

I'm tring to solve an issue with ranking with a tie breaker. I don't know how to explain it without sounding confusing so I'm attaching a sample, but basicly I want to be able to rank people with certain points and a set tie breaker value.

The attached spreadsheet (which has a bunch of hidden columns) is being used to generate points based on ranking and then at the end of the 4 weeks, give a ranking based on the 4 weeks' totals.

The only problem I have right now, is that if there is a tie in the final point score (column V) the rankings (coulmn W) are assigned sequentially in the order of the rows on the spreadsheet (i.e. if Name 3 and Name 4 were tied for third place, Name 3 would be third and Name 4 would be fourth).

What I need is to use column Y as the tie breaker. So, if Name 3 had 16 in column Y and Name 4 had 17, Name 4 would in fact be in thrid place, Name 3 would be in fourth, and then the ranking would continue with 5th and 6th places being assigned sequentially.

I have tried making changes in Column W, but even after many combinations I have still had no success thus far.

Any ideas?

Hi,

I am setting up a band competition spreadsheet.With some great help form your Forum Moderator I was able to do a ranking minus 0 (the high score to be left out of the rankings as they receive the Sweepstakes Award) and added a tie breaker based on the highest score in Colummn J)

However I need to provide results for the awards announcer, providing the score and school from 1st to 12th place. That was working fine until I added the tie breaker to the net scores. The placement column will reflect the tie breaker, but obvioulsy not in the list of awards.
This is the formula for the placement

=IF(W4="","",RANK(W4,(W$4:W$26),0)+SUMPRODUCT(--($W$4:$W$26=W4),--($J$4:$J$26>J4))-COUNTIFS($W$4:$W$26,LEFT($W$56,FIND(" ",$W$56))+0,$B$4:$B$26,MID($W$56,FIND(" ",$W$56)+1,255)))

and this is the formula for the list of awards

=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1)&" "&INDEX($B$4:$B$26,MATCH(1,(($X$4:$X$26<>0)*($W$4:$W$26=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1))), 0))

Is it possible to reflect the tie breaker in the list of awards?

I am attaching a sample.

Thanks for your help!

I have a spreadsheet that ranks 55 of our employees on two certain
catagories, the highest rank being and lowest being 55 in both catagories.
The two ranks are added up as points in another column and ranked in the same
manner. A little VBA code is added and the rows are sorted by the total point
catagory, then ranked by that column.
Having 55 employees on this sheet, there are many times when there are
multiple ties. The company wants the tie breaker to be decided on another
catagory, with the lowest in that catagory being ranked highest. How can I
test this? I will explain the whole worksheet and the actions next.

The spreadsheet is started with the 55 employees sorted by store number and
employee number. The columns are filled with data from reports. The result of
each column that is ranked is static ( stays with that row ). Then the rows
are sorted by the by the 'point total'.

Hope this helps in the explanation a little. I read the definition for the
RANK function, but it only has one explanation for a tie breaker, which
describes if there is a two way tie, not multiples.
Thanx for any ideas in advance.
Brian

Hi, I'm creating a spreadsheet for sports that has a Group of 4 teams.
I've set up a RANK & VLOOKUP function in order to automatically sort
the table however I run into a couple of problems.

1. When 2 teams have the same rank based on points, the cell shows #N/A
so I need to know how to put in tie breakers. For example, a tie
breaker would be another column that has goal differential in it.

2. When no scores are entered in the system it also ranks all teams as
#1 and the same problem arises. I don't know if the same fix would
work for both.

Can anybody help with this?

--
hip
------------------------------------------------------------------------
hip's Profile: http://www.excelforum.com/member.php...o&userid=31954
View this thread: http://www.excelforum.com/showthread...hreadid=516752

i'm trying to learn auto sorting its been a very uneventful process.. I'm not sure what i'm missing that isnt allowing it to sort properly, i've managed to learn the ranking and such and got it working fine, however this auto sorting isnt going so well.

i'm using the world cup group as an example to learn with, I know there are lots out there but I would like to learn this so that I can use my spreadsheet for other things.. like Ball Hockey league, Euro Cup, or a tournament me and my friends may create.

the table on the right will be hidden eventually after i get all this figure out.

ALSO

how would I implement a tie breaker scenario goal differential not just goals scored so (goals for minus goals against).

all help appreciated
thanks

Hi, I'm creating a spreadsheet for sports that has a Group of 4 teams. I've set up a RANK & VLOOKUP function in order to automatically sort the table however I run into a couple of problems.

1. When 2 teams have the same rank based on points, the cell shows #N/A so I need to know how to put in tie breakers. For example, a tie breaker would be another column that has goal differential in it.

2. When no scores are entered in the system it also ranks all teams as #1 and the same problem arises. I don't know if the same fix would work for both.

Can anybody help with this?

I thought I had a good formula, but I ran across a combo of numbers that caused a problem.

This is the formula I am using. =E8+(MAX($I$8:$I$37)-I8/MAX($I$8:$I$37)) In this one, the lowest number in the tie break coulmn wins the tie.

I got this by changing this formula =E8+(MAX($I$8:$I$37)+I8/MAX($I$8:$I$37)) In this one, the higher number in the tie break coulmn wins the tie.

By using the higher number as the winner, I have not ran across a combo of numbers caused any problems.

But, using the lowest number to win I just ran across a combo that creates a problem.

I have attached two tables to help show my problem.

Hopefully someone can help me.

Thanks

Tie Breaker help.xlsx

Hi

Having searched this forum several times, I've been unable to solve this.

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.

Can somebody help with use of formulas?

Br

Hi all,

I am in need of some formula help. While this particular application is for fantasy football, I think the formula could be quite useful for other applications as well. Here is a cutout of some data:

******** ******************** ************************************************************************>Microsoft Excel - Book3___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)boutA1=
ABCD1PosNameAdjusted*Rank*2QBDonovan*McNabb2*3QBPeyton*Manning5*4QBDrew*Brees6*5QBCarson*Palmer7*6QBMarc*Bulger11*7QBTom*Brady12*8RBLaDainian*Tomlinson1*9RBSteven*Jackson3*10RBLarry*Johnson4*11RBBrian*Westbrook8*12RBFrank*Gore9*13RBWillie*Parker10*14RBKevin*Jones22*15RBMaurice*Jones-Drew30*16WRMarvin*Harrison14*17WRSteve*Smith17*18WRTerrell*Owens23*19WRT.J.*Houshmandzadeh24*20WRDarrell*Jackson25*21WRReggie*Wayne28*22WRTorry*Holt29*23WRDonald*Driver31*24WRMarques*Colston33*25WRChad*Johnson34*26WRLee*Evans35*27WRRoy*Williams42*28WRPlaxico*Burress45*29WRJavon*Walker46*30WRLarry*Fitzgerald48*31WRHines*Ward52*32WRAndre*Johnson55*33****34PickPlayerPos*351Donovan*McNabbQB*3613Kevin*JonesRB*3725Maurice*Jones-DrewRB*3837Roy*WilliamsWR*3952Hines*WardWR*Sheet1*
[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.

My goal is to return the values in B35:B39 by using a formula that does the following (example is for cell B35): If A2:A32 matches C35 and if C2:C35 is greater than or equal to A35, return the highest ranking remaining player from B2:B32.

I had tried and INDEX/MATCH with conditions, but was unable to incorporate returning the highest ranking remaining player (I was trying to use SMALL for this).

Any help would be greatly appreciated...please let me know if I can provide more clarity.

Thanks,
Bill

I'm having a recurring issue with loading my embedded PowerPivot data in Excel 2010.

Upon loading my workbook, which is a series of worksheets with PivotTables tied to embedded PowerPivot data, the workbook begins trying to read the data, only to end in an error message: "Embedded Analysis Services Engine: Could not load embedded PowerPivot data." This message repeats itself.

Excel next prompts me: "The embedded PowerPivot data is corrupt. You have the following options: Do nothing; Attempt to recover the structure of the PowerPivot data; Delete the PowerPivot data and continue using the workbook."

I chose the second option, which recovers the structure of the data but leaves all the data out. Thus, every time I open the workbook, I have to reload all the data all over again. I have a total of five tabs of data: four from linked external Excel workbooks, and one with a linked table from the very workbook that has the embedded Power Pivot.

How do I fix the embedded data so it loads correctly?

I have a troubling issue with simultaneous execution of external links update and workbook_open macro.
The Excel workbook has a range of cells (a table of 85 rows and 20 columns) whose cells contain formulas that link to a remote server to download stocks data.
The workbook_open macro executes code that depends on content of these cells in order to setup range names and some other calculations.
The problem is that full update of external links takes a few seconds, which is too log and causes the workbook_open macro to crash upon opening because not all cells have updated yet.
What is needed is a way to force the macro to wait until all external links are updated (but without tying up computer processor time which would slow external data updating time and speed).

Any help?
thank you.

Alseikhan

I have read a lot of posts about tie breakers and using "Composite scores" and "helper columns" to sort out the winner. They make sense, but the problem I have is when you are working with 3 or more tie breaker elements .

Example - Two players are tied with their point total. After the first tie breaker player 2 wins. It should stop right there, but with the composite scoring, it tallies the 2, 3 and 4 tie breakers. So in theory, a player that does better in tie breaker 2, 3, or 4 could place higher even though they lost the main tie breaker.

I have tried placing weight values of significance with each tie breaker, but it still comes up with the same possibility.

Is there a way to stop the formula after the first tie breaker if it generates a winner, then if still tied go to second tie breaker, etc?

Thank you for your help.

I am sure this has been posted before but I have not had any luck finding it.

I have Column A as Room Numbers (A3:A340) and Column AE (AE3:AE340) has the Average of Columns E, H, K, N, Q, T, W, Z, AC. I am capable of getting the top/bottom 10 out of Column AE (the Avg) but cannot get the associated room number to go with it past the first match. Basically if the top 4 have the same number in Column AE7, AE12, AE120, AE224 I only get the Room Number in A7 for all four.

I am using the Large formula to find the top 10 values in Column AE and the following formula to find the Room Number, Column A,(A3:A340) to match:

=INDEX($A$3:$A$340,MATCH(LARGE($AE$3:$AE$340,2),$AE$3:$AE$340,0))

I have an issue with the Find functionality finding Date fields.

I was in the process of updating an custom Add-In when I noticed this issue. Here's the odd part. I'm using the same version of Excel, 2003 with SP2, with the prior and current versions of the add-in I'm updating.

The coding is identical between the 2 versions of the Add-Ins(the line where it hiccups is below):
Set rngTemp = wrkshtTemp.Columns(sf1).Find(what:=v1, After:=rngCell, LookAt:=xlWhole),
where sf1 is the column index (type = long), v1 is the search variable (variant), rngCell is the beginning of the range object (1 cell) to search after (1st row of worksheet).

The date value is pulled from a seperate data source and is in the following format in the range being searched: mm/dd/yyyy hh:mm:ss am/pm"

When the Prior Add-In is installed, I can successfully search and find a date value by using the code above or alternatively, manually, searching the date value using the Find function from the standard Excel menu.

When the current Add-in is installed, the identical search doesn't work for date fields only.

Is there anything that is cached or otherise stored with the file when saving a new Add-In which could impact the general functionality of an Excel workbook (unrelated to the Add_in), specifically the Find function?

Hello,

I have searched extensively for tie breakers but have yet to find something that works for my workbook.

On my sheet the first tab is for results. On the second sheet, the area in grey is where I use my ranking formulas. It's very important that the area inside the white stays intact without having to insert cells to make updating this file very simple.

Where I am running into difficulty is the ranking of the three metrics, Margin %, UPG %, and Discount %. I can't get consistent, sequential results. It seems like ranks are skipped or tied, which forces me to go back and manually calculate or adjust the correct value.

Any help on how to make this more efficient would be greatly appreciated.

I am having issues with the AND operator in excel.
I am trying to AND two 8-bit binary numbers (for example 11111111 and 10101010) when I attempt to AND them I get an answer of 8912898?? not the expected 10101010? Anyone have any idea what might be causing this?

code:
sub bitwiseand ()
dim answer as long

answer = 11111111 and 10101010
end sub

and I am viewing the answer by watching it when i step thourgh the code (F8)

Brundy

Issue with Excel 2K7 losing focus after minimizing, opening a second spreadsheet

Hello,

We've just begun the migration from Office 2003 to Office 2007. Along the way, we can repeadely recreate an issue with Excel and I'm hopiing someone has an explanation:

Issue: We open up an existing spreadsheet, or create a new one, minimize it and navigate to another spreadsheet in Explorer and launch it, Excel opens up the second spreadsheet as expected, but Excel does not have focus. In fact, if there are other windows open at the time, Excel is in the background, layered behind the other appliocation(s).

It's a nuisance only at this point, because a couple of clicks brings Excel to the foreground, but we didn't expect this behavior in 2007 (nor do we recall seeing it in 2003).

Thanks!

Issue with 2003 PowerPoint Show showing in IE6 for office 2007 users...

I have a web site with various PP presentations created in PowerPoint 2003. Most of my users are using Office 2003 but some are starting to use Office 2007. Users using 2003 click a link (which is on our web site) to a .pps file and it opens within their IE6 browser (yeah I know, IE6. My IT department is moving us soon). Great.
However when my Office 2007 users click the link to the .pps file, it give them a save or open dialog box. With either option, when they open the presentation, it opens in an edit mode. They see the control panel, options etc. I want my Office 2007 users to be able to see the presentation within the IE6 browser.
Is this possible???

The following code works great in Office 2003, but causes issues with 2010. It was installed on a personal macro folder in XLSTART.


	VB:
	
 ListFiles() 
     
    Dim fd As FileDialog 
    Dim PathOfSelectedFolder As String 
    Dim SelectedFolder 
    Dim SelectedFolderTemp 
    Dim MyPath As FileDialog 
    Dim ExtraSlash 
    ExtraSlash = "" 
    Dim MyFile 
    Dim Mystring As String 
    Dim sDate As String 
    Dim sApp As String 
     
    sDate = InputBox("Input the date you want to appear in the Filename") 
    sApp = InputBox("Type the name of the app you are using") 
    MsgBox ("Now browse and select the folder containing the files you want to rename") 
     
     'Prepare to open a modal window, where a folder is selected
    Set MyPath = Application.FileDialog(msoFileDialogFolderPicker) 
    With MyPath 
         'Open modal window
        .AllowMultiSelect = False 
        If .Show Then 
             
             'The user has selected a folder
             'Loop through the chosen folder
            For Each SelectedFolder In .SelectedItems 
                 
                 'Name of the selected folder
                PathOfSelectedFolder = SelectedFolder & ExtraSlash 
                Set fs = CreateObject("Scripting.FileSystemObject") 
                Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder) 
                 'Loop through the files in the selected folder
                For Each MyFile In SelectedFolderTemp.Files 
                     'Name of file
                    On Error Resume Next 
                    If InStr(1, MyFile.Name, "cfc_sec_adm__acct_acc_jrnl_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Acct Acct UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__acct_acc_jrnl_" & sApp, vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Acct Acct PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__app_user_jrnl_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " App User UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__app_user_jrnl_" & sApp, vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " App User PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__d_user_info_jrnl_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " D User UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__d_user_info_jrnl_" & sApp, vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " D User PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__priv_jrnl_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Priv UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__priv_jrnl_" & sApp & "", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Priv PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__priv_jrnl_summary_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Priv Summary UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__priv_jrnl_summary_" & sApp & "", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Priv Summary PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__user_ftu_jrnl_" & sApp & "uat", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Ftu UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__user_ftu_jrnl_" & sApp & "", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Ftu PROD.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__user_ftu_jrnl_summary_" & sApp & "uat", vbTextCompare) > 0
Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Ftu Summary UAT.out" 
                        On Error Resume Next 
                    ElseIf InStr(1, MyFile.Name, "cfc_sec_adm__user_ftu_jrnl_summary_" & sApp & "", vbTextCompare) > 0 Then 
                        Name MyFile.Name As sDate & " CFC GFTS " & UCase(sApp) & " Ftu Summary PROD.out" 
                         
                         'Name MyFile.Name As sDate & " " & ".xls"
                         'DO STUFF TO THE FILE, for example:
                    End If 
                Next 
            Next 
        End If 
    End With 
    MsgBox ("Complete") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is designed to rename files in a folder when they have certain criteria. It saves a TON of work, but I can't install it on machines that use 2010 for some reason. Any suggested workarounds?

I've written some macros to automate sorting. Very simple stuff, really, but those who don't know much about VBA are naturally suspicious. And, of course, when some corruption happens (presumably after some sorting has been done), all fingers point to my macros.

Now, I've tested the macros fairly rigorously (by commenting out the actual sort call and spitting out the sort range and sort key address) and am fairly confident that they work. So I'm wondering what else could possibly be the cause of the corruption.

Just to clarify things, the data consists of player names in column A and a long slew of stats and other info in the following columns. "Corruption" means that the data in some particular column was "mixed-up" relative to the player name column.

The spreadsheet also has autofilters. Is there some known issue (or past known issue) with sorting filtered data?

i have two guys offsite who access a shared drive to input data into two files. One file is 4mb and the other under half a MB. The smaller file has one On Open Event to sort entries. I keep getting told by IT that its an issue with my spreadsheets. this gives me the absolute %^%$#####!!! sure, they may not be as elegant as some but they produce the desired results. anyway, the guys keep getting an error message that states:

"Microsoft Visual Basic: Error accessing the system registry"

what does this mean?

I am having an issue with the combo box control in my Windows XP version of Excel. I have used the control toolbox to creat a combo box. I understand how to link this box to cells and fill ranges. However, everytime my page is printed or I view the "print preview", all of my combo boxes move over to the left side of my spreadsheet for not apparant reason. I've gone into the control's properties and manually adjusted its position, but that still does not help. Any help would be greatly appreciated.

I'm having a strange issue with a pivot table. I have a list w/ 2000+ entries with IPs and in one column and location in another.

Example:

10.234.14.1 US - Atlanta, GA
10.234.13.6 US - Chicago, IL
10.82.23.1 MX - Toluca

When I create the pivot table, I put location in the Row Area and IP in the Data area. For some reason, some of the data in the location area are represented twice.

Example:

US - Atlanta GA 10
US - Atlanta GA 2

It doesn't happen for all of the locations, just some. I've checked and the field has the exact same text, so there's not a issue with the data. Also, if i create a list, the list does NOT have multiple names for the location.

Any ideas on what might cause this?

TIA


No luck finding an answer? You could always try Google.