Formula Help IF Above Row Value is Greater than Below Row Value

[parvezs27]

Hi!

I have a table with Australian Football League Statistics. For column E "Disposal W/L", if D2 value is greater than D3 value then "W" if false then "L" and then if D4 value is greater than D5 value then "W" if false then "L" and so on.

I tried =IF(D2>D3, "W", "L") but that moves down and it's not correct. I just want to compare D2 with D3 and then D4 with D5 and then D6 with D7 and so on.

Would appreciate any help for a beginner here, thanks!

Ovens and Murray League Macro Enabled.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Round	Team	Win/Lose	Total Disposals	Disposal W/L	Contested Disposals	Contested Disposal W/L	Uncontested Disposals	Uncontested Disposals W/L	Total Kicks	Total Kicks W/L
2	1	Wangaratta Magpies	W	318		53		265		222
3	1	Albury	L	321		81		240		211
4	1	Lavington	L	275		91		184		192
5	1	Corowa-Rutherglen	W	355		83		272		203
6	1	Myrtleford	W	270		85		185		206
7	1	Wangaratta Rovers	L	286		82		204		208
8	2	Albury	W	395		122		273		240
9	2	Wodonga	L	385		104		281		215
10	2	Wangaratta Magpies	W	347		138		209		214
11	2	Wangaratta Rovers	L	293		93		200		193
12	2	Lavington	W	288		90		198		208
13	2	Myrtleford	L	323		96		227		210
14	2	Wodonga Raiders	L	268		82		186		197
15	2	Yarrawonga	W	355		117		238		241
16	2	North Albury	L	306		83		223		184
17	2	Corowa-Rutherglen	W	398		71		327		232
18	2	Wangaratta Rovers	W	388		89		299		227
19	2	Wodonga Raiders	L	352		80		272		227
20	2	Corowa-Rutherglen	L	348		105		243		209
21	2	Albury	W	346		99		247		213
22	2	Wodonga	L	376		87		289		224
23	2	Wangaratta Magpies	W	344		80		264		228
24	2	Lavington	L	260		70		190		195
25	2	Yarrawonga	W	322		112		210		219
26	2	North Albury	L	256		78		178		150
27	2	Myrtleford	W	352		72		280		233
28	3	Lavington	L	341		100		241		219
29	3	Wangaratta Rovers	W	372		118		254		231
30	3	Corowa-Rutherglen	L	270		114		156		151
Match Data

 

[aaewalsh]

You can use the IF and MOD functions in combination to achieve this. The MOD function returns the remainder of a division, so you can use it to check if a cell is even or odd.

For example, the formula in cell E2 would be:

=IF(D2>D3,"W","L")

And then in E3 you can use:

=IF(D4>D5, "W", "L")

You can drag down this formula for the rest of the columns.

Alternatively, you can use the following formula in E2 and drag it down for the rest of the columns:

=IF(MOD(ROW(),2)=0,IF(D2>D3,"W","L"),IF(D4>D5,"W","L"))

This formula uses the MOD function to check if the row number is even (D2 and D3) or odd (D4 and D5) and then uses the IF function to compare the values and return "W" or "L" accordingly.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Round	Team	Win/Lose	Total Disposals	Disposal W/L	Contested Disposals	Contested Disposal W/L	Uncontested Disposals	Uncontested Disposals W/L	Total Kicks	Total Kicks W/L
2	1	Wangaratta Magpies	W	318	L	53		265		222
3	1	Albury	L	321	W	81		240		211
4	1	Lavington	L	275	L	91		184		192
5	1	Corowa-Rutherglen	W	355	W	83		272		203
6	1	Myrtleford	W	270	L	85		185		206
7	1	Wangaratta Rovers	L	286	W	82		204		208
8	2	Albury	W	395	W	122		273		240
9	2	Wodonga	L	385	L	104		281		215
10	2	Wangaratta Magpies	W	347	W	138		209		214
11	2	Wangaratta Rovers	L	293	L	93		200		193
12	2	Lavington	W	288	L	90		198		208
13	2	Myrtleford	L	323	W	96		227		210
14	2	Wodonga Raiders	L	268	L	82		186		197
15	2	Yarrawonga	W	355	W	117		238		241
16	2	North Albury	L	306	L	83		223		184
17	2	Corowa-Rutherglen	W	398	W	71		327		232
18	2	Wangaratta Rovers	W	388	W	89		299		227
19	2	Wodonga Raiders	L	352	L	80		272		227
20	2	Corowa-Rutherglen	L	348	W	105		243		209
21	2	Albury	W	346	L	99		247		213
22	2	Wodonga	L	376	W	87		289		224
23	2	Wangaratta Magpies	W	344	L	80		264		228
24	2	Lavington	L	260	L	70		190		195
25	2	Yarrawonga	W	322	W	112		210		219
26	2	North Albury	L	256	L	78		178		150
27	2	Myrtleford	W	352	W	72		280		233
28	3	Lavington	L	341	L	100		241		219
29	3	Wangaratta Rovers	W	372	W	118		254		231
30	3	Corowa-Rutherglen	L	270	W	114		156		151
31
Main

Cell Formulas
Range	Formula
E2:E30	E2	=LET(s,SEQUENCE(ROWS(FILTER(B2:B100,B2:B100<>""))),IF(MOD(s,2)=1,IF(INDEX(D2:D100,s)>INDEX(D2:D100,s+1),"W","L"),IF(INDEX(D2:D100,s)>INDEX(D2:D100,s-1),"W","L")))
Dynamic array formulas.

 

[Peter_SSs]

.. or this spillable one?
(I'm assuming the original data was simply cut off at row 30 for the sample as there is an odd number of values listed for round 3)

23 01 14.xlsm
	D	E
1	Total Disposals	Disposal W/L
2	318	L
3	321	W
4	275	L
5	355	W
6	270	L
7	286	W
8	395	W
9	385	L
10	347	W
11	293	L
12	288	L
13	323	W
14	268	L
15	355	W
16	306	L
17	398	W
18	388	W
19	352	L
20	348	W
21	346	L
22	376	W
23	344	L
24	260	L
25	322	W
26	256	L
27	352	W
28	341	L
29	372	W
30	270	W
WL

Cell Formulas
Range	Formula
E2:E30	E2	=IF(D2:D30>IF(ISODD(ROW(F2:F30)-ROW(F2)),D1:D29,D3:D31),"W","L")
Dynamic array formulas.

BTW, Doesn't it need to allow for the case where disposals in a match are equal? The above formula would return "L" for both teams in that scenario which does not seem right.

23 01 14.xlsm
	D	E
1	Total Disposals	Disposal W/L
2	318	D
3	318	D
4	275	L
5	355	W
6	270	L
7	286	W
8	395	W
9	385	L
10	347	W
11	293	L
12	288	L
13	323	W
14	268	L
15	355	W
16	306	L
17	398	W
18	388	W
19	352	L
20	348	D
21	348	D
22	376	W
23	344	L
24	260	L
25	322	W
26	256	L
27	352	W
28	341	L
29	372	W
30	270	W
WL (2)

Cell Formulas
Range	Formula
E2:E30	E2	=MID("LDW",SIGN(D2:D30-IF(ISODD(ROW(G2:G30)-ROW(G2)),D1:D29,D3:D31))+2,1)
Dynamic array formulas.

 

[Peter_SSs]

If you are not particularly keen to have a single spillable formula at the top and are happy to copy down, you could put these two much simpler formulas in E2 and E3 and then select both cells (E2:E3) and drag the Fill Handle down.

23 01 14.xlsm
	D	E
1	Total Disposals	Disposal W/L
2	318	D
3	318	D
4	275	L
5	355	W
6	270	L
7	286	W
8	395	W
9	385	L
10	347	W
11	293	L
12	288	L
13	323	W
14	268	L
15	355	W
16	306	L
17	398	W
18	388	W
19	352	L
20	348	D
21	348	D
22	376	W
23	344	L
24	260	L
25	322	W
26	256	L
27	352	W
28	341	L
29	372	W
30	270	W
WL (3)

Cell Formulas
Range	Formula
E2,E4,E6,E8,E10,E12,E14,E16,E18,E20,E22,E24,E26,E28,E30	E2	=IF(D2>D3,"W",IF(D2=D3,"D","L"))
E3,E5,E7,E9,E11,E13,E15,E17,E19,E21,E23,E25,E27,E29	E3	=IF(D3>D2,"W",IF(D3=D2,"D","L"))

 

[parvezs27]

If you are not particularly keen to have a single spillable formula at the top and are happy to copy down, you could put these two much simpler formulas in E2 and E3 and then select both cells (E2:E3) and drag the Fill Handle down.

23 01 14.xlsm
	D	E
1	Total Disposals	Disposal W/L
2	318	D
3	318	D
4	275	L
5	355	W
6	270	L
7	286	W
8	395	W
9	385	L
10	347	W
11	293	L
12	288	L
13	323	W
14	268	L
15	355	W
16	306	L
17	398	W
18	388	W
19	352	L
20	348	D
21	348	D
22	376	W
23	344	L
24	260	L
25	322	W
26	256	L
27	352	W
28	341	L
29	372	W
30	270	W
WL (3)

Cell Formulas
Range	Formula
E2,E4,E6,E8,E10,E12,E14,E16,E18,E20,E22,E24,E26,E28,E30	E2	=IF(D2>D3,"W",IF(D2=D3,"D","L"))
E3,E5,E7,E9,E11,E13,E15,E17,E19,E21,E23,E25,E27,E29	E3	=IF(D3>D2,"W",IF(D3=D2,"D","L"))

Thank you very much for this! And for bringing to my attention that there needs to be a label when disposals in the match are equal. Much appreciated.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.