Find the winner and percentage difference between two teams

I want to find the winning team based on four different scores between the two teams.

I want to output which teams that wins and what the percentage score difference is.

I am not sure about how the percentage difference should be calculated.

See attached workbook for more information.

There are different max scores for the five different routes. Ideally, I would like to enter the max score for each route and then calculate the % difference!

Desired output is:

Teamname ( % difference in scores between winning team and runner up)

Ex.

Walkers (75%)
Runners (49%)

May be this. Pl see file.
In P8

=SUMPRODUCT(($J$8:$J$12>$F$8:$F$12)*($K$8:$K$12>$G$8:$G$12))/COUNTIF($F$8:$F$12,"<>""")

I think you'll need to explain how you're calculating the percentage. For example:

Route 1 : Runners (10, 8) : Walkers (15, 10) - I can see that Walkers win but how do you get the 50% number?
Route 2 : Runners (15, 2) : Walkers (10, 1) - How do you calculate 75%?

Also, who wins for your Case 2 and Case 3? Do you then add the scores for A&B to determine the winner? How do you then calculate the percentage number?

WBD

OK. Just shooting in the dark a bit still but try this formula in N8:

Please Login or Register  to view this content.

Note: You may need to change "0.15" to "0,15" for your number formatting options.

WBD

Hi WideBoyDixon,

thank you for your reply!

Your formula works like I want but I am not sure on what the % diffrence tells me?

Ex. Runner (-14%, 13%)

What does this mean?

Also;

Please Login or Register  to view this content.

What does this mean?

Please Login or Register  to view this content.

You said the maximum score was 15 so I converted each score to a percentage by dividing by 0.15 then subtracted the percentages to give a difference in the scores. You never said how you expected to calculate the percentages so I created something.

This part of the formula:

Please Login or Register  to view this content.

Is what creates those numbers.

WBD

If you change the maximum score to 10 then divide by 0.1 to get the percentage.

15%, 30% means that the winning team got 15% more (e.g. 85%-70%) for the first category and 30% more (e.g. 90%-60%) for the second one.

WBD

Originally Posted by waimea

Should I change all occurences of 0.15 to ex. 0.1? To get 10 as the maximun score?

Yes; basically it's the maximum score divided by 100.

WBD