updating a win and loss column for Google Sheets

[Daniel12]

Good morning,

I have a spreadsheet for keeping up with a volleyball tournament. I need a formula to indicate whether a match was won (or lost). You must win 2 out of possible 3 sets played to win the match.

So, in this sheet, if D5>E5, and/or D6>E6, and/or D7>E7, then it would place a "1" in column J5:J7
So a team might lose the first set (D5<E5), but win the next two, that would count as a win. Or perhaps they win set 1 and 2, or set 1 and 3.

the column K5:K7 will indicate losses. this will be applied to all for teams listed.

I cant figure out the formula to use.

Thanks for the help

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Daniel12]

Hi & welcome to MrExcel.

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry I didnt include this. I am actually using google sheets on a '22 Macbook Air, but from what I have found, Excel and Sheets are quite compatible

 

[Fluff]

In that case you should clearly state this & such questions should be post in the General Discussion & Other Applications section of the board.
I have done this for you this time.

 

[Fluff]

Not sure if this works for sheets, but try

Fluff.xlsm
	D	E	F	G	H	I	J
5	25	20	20	25	15	25	1
6	25	19	25	18	16	25	1
7			15	10			0
Sheet6

Cell Formulas
Range	Formula
J5:J7	J5	=SIGN(D5:D7-E5:E7)
Dynamic array formulas.

 

[Daniel12]

Not sure if this works for sheets, but try

Fluff.xlsm
	D	E	F	G	H	I	J
5	25	20	20	25	15	25	1
6	25	19	25	18	16	25	1
7			15	10			0
Sheet6

Cell Formulas
Range	Formula
J5:J7	J5	=SIGN(D5:D7-E5:E7)
Dynamic array formulas.

Thank you. This is the problem I run in to. This formula works in some cases but not all.

 

[Fluff]

What cases doesn't it work in?

 

[Daniel12]

What cases doesn't it work in?

A team must win 2 out of the 3 sets to be counted as a win in the win column. So in this example, this team does win 2 out of 3, but the formula shows as -1 rather than 1

 

[Daniel12]

I found one to work
=if(D5>E5,1,0)+AND(D6>E6,1,0)+if(D7>E7,1,0)

Now I will have to repeat it for each particular match

thanks for the help

 

[Daniel12]

I found one to work
=if(D5>E5,1,0)+AND(D6>E6,1,0)+if(D7>E7,1,0)

Now I will have to repeat it for each particular match

thanks for the help

I spoke too soon. I thought I tried every variation, but I didn't. This one returns a false report if the first data set is true but not the others. So how do I make this conditional on 2 of the 3 being true, no matter the order?