Tie Break Problem

[Quixote]

Hi,
I'm struggling to figure out a way of dealing with tie break situations, and would greatly appreciate it if someone could show me how to do it. I have tried so many things, the last of which being various COUNTIF setups. I've definitely hit the ceiling of my knowledge of excel. I have attached a file with the formulas I have used, in the main table I have an INDEX-MATCH-LARGE in the team column, and in the stats columns I have an INDEX-MATCH which works faultlessly. I have tried various structures inside the MATCH portion of the function and inside the LARGE. I thought I could try to use the COUNTIF to create a very slight increment in the ranking stats that didn't affect the whole number but it didn't work. That was the best I could come up with. Again, thanks for any help.

Regards,
Quixote

 

[Bob Phillips]

Who should be first, Rhinos or Lions?

 

[Quixote]

Rhinos. Basically, if two teams have equal points etc in the dummy table, the formula doesn't carry on after it hits the first team, it just keeps selecting the first team with the identical stats. How do I alter my formula to ensure that it will not do this, and the Lions will be listed after the Rhinos? I tried, as I said before to use COUNTIF to create a small increment to the 5th+ decimal place in the dummy table that doesn't alter the stats numbers in any meaningful way, but enough to make each team unique numerically for the formula. I still like the idea, but I don't know if it's possible or if I am implementing it incorrectly. I cannot think of any other way of doing this. I should mention up front that I am not very good with array formulas, which I suspect might be the overall solution to this problem.

 

[Quixote]

Okay, so I got a solution at Ozgrid.com which was =RANK(U4,$U$4:$U$7)+COUNTIF($U$4:U4,U4)-1 (to replace the rank columns previous formula). But I found an even better solution on another site. I got it to work for me by changing the cell references, but I don't know exactly why it works. Of course, I have been setting myself little projects to complete because I wanted to learn how to use Excel, so understanding the formulas are very important to me. Here it is:

{=INDEX($V$3:$V$6,MATCH(LARGE($AE$3:$AE$6-ROW($AE$3:$AE$6)/COUNT($AE$3:$AE$6),ROW(H14)-ROW(H$14)+1),$AE$3:$AE$6-ROW($AE$3:$AE$6)/COUNT($AE$3:$AE$6),0))}

It's the array formula that I feared I would need to resort to. From what I can see it is the INDEX-MATCH-LARGE function that I had come up with initially, but with a considerable level of complexity inside the LARGE function that I could not get right in my earlier attempts. And of course an array. I don't understand why this works. could someone help me reverse engineer this formula and help me understand it better? I have attached the document which has the formula. The author of the document hid the dummy table in columns to the upper right, you can enlarge the column width to see them. Thanks for your time again.

Regards,
Quixote