Wednesday, August 22, 2012

yogi_Determine The Winner From Any Number Of Games Played By Two Players

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #707   Aug 21, 2012

use igge said: (!category-topic/docs/spreadsheets/tEljLsZs0gA)
Comparing amount of cells in order to determine a result
Hello. I've tried many different approaches to this problem, but I can't seem to find a solution.
Player A and B play 3 sets in a game. The scores of those sets are represented in columns B3 through D4 like this: 
     A      B C D
1           Game1
2 Set:      1 2 3
3 Player A: 5 2 1
4 Player B: 2 3 1
5 Winner:    tie 
On row 5, in a merged B-D cell, I want to calculate a winner based upon how many sets they won. If someone won 2 sets they win the game, and also if they won only 1 but the 2 other sets were tied. Likewise, in case of a tie (1 win each and 1 tied game or 3 tied games) the result should be "tie".
Like I said I've tried to do this in several ways using COUNTIF or ArrayFormula, but I just can't seem to get it to work. Using the line below I'm able to display the winner of each set, but I still need to combine this data to determine an over-all winner:
=ArrayFormula(IF((B3:D3)>(B4:D4), "Player A", IF(ArrayFormula((B3:D3)<(B4:D4)), "Player B", "tie")))

Any ideas?

following is a solution to the problem 
in Sheet2, I have provided the solution to a bit generalized problem in finding the winner from a specified number of games played regardless of the total number of games played.