Tournament Scoring Problem

[blackp]

Hi, I'm a newbie here and have read tons of useful information in this forum. Apologies if this question is answered elsewhere, but I haven't found an answer which seems applicable to my lack of Excel skill.

I have been doing the scoring for a bowls tournament and the rules are that the winner is the team winning most rounds. If there are equal rounds then the total points is the deciding factor.

I have counted the number of wins and have that in a column. I can then use the RANK function to determine the order based on number of wins. All OK so far.

My problem is that RANK might return 3 ones then 2 fours followed by a 6. This is correct and understandable. If 3 teams win all 4 rounds, 2 teams win 3 rounds and one team wins 2 rounds that is the answer I would expect.

What I cannot figure out is how to take the output of RANK to the next stage.

In the example above the 3 teams with 4 wins (RANK output =1) would be 1st 2nd and 3rd based on their total points. The team with the rank of 4 with the total points would be 4th overall.

If no team had 4 wins, a team with 3 wins with the highest total points would be the overall winner, and so on.

This is probably a very dumb question, but I've been wracking my brains to come up with an answer to no avail.

Just to add another level of complication the tournament rules state that if a team has an equal number of wins and an equal number of points then the team with the highest total margin is the winner.

Here is a concatenated picture of the worksheet. At the last tournament I calculated the final placings manually:

/Users/peterblack/Desktop/Tournament.jpg

I look forward to a response.

Thanks in anticipation.

Peter.

 

[blackp]

Sorry, I'm not sure what you are suggesting.

If I undo the sort - wouldn't that just replace the 1,2,3,4 back into the top 4 cells?

 

[InaCell]

Sorry

Was typing a step ahead.

Undo the sort is effectively resorting the selected data by Col A.

Resorts it how you wanted and has the 1-4 ranking.

Regards

InaCell

 

[blackp]

Sorry

Was typing a step ahead.

Undo the sort is effectively resorting the selected data by Col A.

Resorts it how you wanted and has the 1-4 ranking.

Regards

InaCell

Thanks InaCell,

I can make this work.

I still wonder if there is a way to do this by formula rather than a macro with 2 sorts.

In any case if nobody else responds, you have given me a workable solution - thank you!

Peter.

 

[InaCell]

Hi Peter

Paste this in the ranking column

=IF(Q3<>"",SUMPRODUCT(--($Q$3:$Q$24+$O$3:$O$24/(10^3)+$P$3:$P$24/(10^6)>Q3+O3/(10^3)+P3/(10^6)))+1,"")

Drag it into all of the cells.

Cheers,

InaCell

 

[blackp]

Hi Peter

Paste this in the ranking column

=IF(Q3<>"",SUMPRODUCT(--($Q$3:$Q$24+$O$3:$O$24/(10^3)+$P$3:$P$24/(10^6)>Q3+O3/(10^3)+P3/(10^6)))+1,"")

Drag it into all of the cells.

Cheers,

InaCell

Thanks InaCell,

Your equation gives the wrong result, and I can see what it's doing wrong, but I clearly have a bit to learn about Excel conventions, as I don't understand what to change to fix it.

The winner is Team13, 2nd is Team2 (your formula got these right), but the 3rd place is Team5 and 4th is Team17 and the formula got Team4 and Team9 respectively. It seems that the reason is that Team17 and Team9 have a higher score, but they only won 2 games so they are not considered unless there are no teams with 3 wins that are unplaced.

Do you have any recommended reading about SUMPRODUCT? You are using it for a purpose I cannot relate to what it does.

Sorry if I'm being obtuse.

Thanks again,

Peter.

 

[InaCell]

Works for me using your supplied data.

Formula only had to be dragged down to populate cells below.

 

[blackp]

Works for me using your supplied data.

Formula only had to be dragged down to populate cells below.

DOH!

I had made a cut down version of the sheet to experiment with and coincidentally the RANK column was 'R'. Stupidly I didn't take into account that you had never seen that version. Arbitrarily I changed the order of the columns to be in order of importance: WINS, POINTS, MARGIN, rather than the original: MARGIN, WINS, POINTS.

I have re-tested and you are indeed correct - although I still don't understand why!

You have been very helpful so far, but would it be too much trouble to go through the formula and explain to me why you did what you did?

Thanks again,

Peter.

 

[InaCell]

Peter

As an argument of the SUMPRODUCT funtion there is a comparison. ie

$Q$3:$Q$24+$O$3:$O$24/(10^3)+$P$3:$P$24/(10^6)>Q3+O3/(10^3)+P3/(10^6)

that generates an array of logical (boolean) values either True or False, like this

{FALSE;TRUE;TRUE;FALSE..................}

-- the so called double unary operator coerces (converts) the True and False values to, respectively, 1 and 0.

resulting in
{0;1;1;0.........}

At last, the SUMPRODUCT function, that requires arrays of numbers to work properly, adds these values doing, in essence, a counting.

For a very good explanation about SUMPRODUCT and the use of -- take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers, InaCell

 

[blackp]

Thanks for the great reference. I now understand much more about how SUMPRODUCT works, but I still have a couple of questions:

I'm not sure why you tested for <>"" before the Sumproduct, but I see if the cell had no value you would return it to "" at the end. Is this just for tidiness, or is there a reason I've not grasped?

Does Sumproduct treat the 3 columns as arrays with the plus sign treated as an "OR", or given no closing bracket should I interpret it as:

ArrayWins plus ArrayPoints/1000 plus ArrayMargin/1000000 > RowTestedWins plus RowTestedPoints/1000 plus RowTestedMargin/1000000 plus 1

I can't understand if the formula returns TRUE/FALSE converted to 1 or 0 what is the reason for dividing by 1000 and 1000000 and what is the reason for adding 1 at the end? Also what is the significance of the > sign?

I'm sure I'll get it eventually.

Regards,

Peter.

 

[blackp]

Still working through this to understand.

I tried removing the IF(Qn<>"", SUMPRODUCT(....etc.),"") test surrounding the SUMPRODUCT. The only difference was that when Wins was blank, the calculated rank was blank, after removing the test SUMPRODUCT would calculate a rank based on zero wins and accurately calculated the new rank.

Next I thought I'd expand the conditional test rather than remove it with: =IF(AND(Q3<>"",O3<>"",P3<>""),SUMPRODUCT(....etc.),"") with the view that if any of the input numbers was blank the result would be blank. This works fine with no rank being displayed unless all 3 input values are populated.

I've been trying to properly understand the SUMPRODUCT formula. I'm guessing that Wins are left whole, Points are divided by 1000, and Margin is divided by 1000000 reflects their relative ranking in the decision, but even re-reading the excellent xldynamic article I cannot fathom the actual way this works. Prefacing SUMPRODUCT with -- means it should return zeros or ones for FALSE and TRUE. Obviously we are not getting a "Divide by Zero" error so the divide must occur before the TRUE/FALSE value is calculated. Still not sure.

I'd appreciate any explanation or further reference.

Thanks again,

Peter.