Wednesday, August 22, 2012

Comparing amount of cells in order to determine a result

Comparing amount of cells in order to determine a result

Player A and B play 3 sets in a game. The scores of those sets are represented in columns B3 through D4 like this:

On row 6, 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".


In cell B5:
=IF(B3=B4;"TIE";IF(B3>B4;"Player A";"Player B"))

In cell C5:
=IF(C3=C4;"TIE";IF(C3>C4;"Player A";"Player B"))

In cell C6:
=IF(D3=D4;"TIE";IF(D3>D4;"Player A";"Player B"))

In cell B6 (merged cell):
=IF(COUNTIF(B5:D5;"Player A")>1;"Player A";IF(COUNTIF(B5:D5;"Player B")>1;"Player B";"TIE"))

Note: If you want to get the result of "Winner of Game" directly without having "Winner of Set", then here is the formula:

=IF(AND(B3=B4;C3=C4;D3=D4);"TIE";IF(AND(B3=B4;C3=C4);IF(D3>D4;"Player A";"Player B");IF(AND(B3=B4;D3=D4);IF(C3>C4;"Player A";"Player B");IF(AND(C3=C4;D3=D4);IF(B3>B4;"Player A";"Player B");IF(OR(AND(B3>B4;C3>C4);AND(C3>C4;D3>D4);AND(B3>B4;D3>D4));"Player A";IF(OR(AND(B3<B4;C3<C4);AND(C3<C4;D3<D4);AND(B3<B4;D3<D4));"Player B";"TIE"))))))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 


No comments:

Post a Comment