Comparing two ranges for where one of the values is equal to one more

[Civitas]

Hi all,
Please see soccer data below. Although it's fairly easy to do a 'more than' between two values. Can someone point me in the right direction to find scores were the winner has won by only by one goal? Can this only be done in VBA?

Any pointers in the correct direction would be most helpful.

Civi.


[TABLE="width: 342"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]HomeTeam[/TD]
[TD]AwayTeam[/TD]
[TD]FTHG[/TD]
[TD]FTAG[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Dortmund[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Stuttgart[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Freiburg[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]M'gladbach[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Wolfsburg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Mainz[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Hoffenheim[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Braunschweig[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Werder Bremen[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Nurnberg[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Hannover[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Schalke 04[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Leverkusen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Bayern Munich[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Hertha[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Hamburg[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Augsburg[/TD]
[TD]Ein Frankfurt[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]M'gladbach[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Nurnberg[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Hannover[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Wolfsburg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Mainz[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Hertha[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Augsburg[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Braunschweig[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Hamburg[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Ein Frankfurt[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Freiburg[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Schalke 04[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Leverkusen[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Hoffenheim[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Dortmund[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Werder Bremen[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Bayern Munich[/TD]
[TD]Stuttgart[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Werder Bremen[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Ein Frankfurt[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Nurnberg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Stuttgart[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Schalke 04[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Leverkusen[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Freiburg[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Hertha[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Hoffenheim[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Dortmund[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Hamburg[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]M'gladbach[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Wolfsburg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Mainz[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Hannover[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Bayern Munich[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[TD]Augsburg[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Here's one way. Another would be to use conditional formatting to highlight records where the win margin is 1 point.

Excel Workbook
	A	B	C	D	E
1	HomeTeam	AwayTeam	FTHG	FTAG	1-Pt win
2	Augsburg	Dortmund	0	4
3	Augsburg	Stuttgart	2	1	1-Point Win
4	Augsburg	Freiburg	2	1	1-Point Win
5	Augsburg	M'gladbach	2	2
6	Augsburg	Wolfsburg	1	2	1-Point Win
7	Augsburg	Mainz	2	1	1-Point Win
8	Augsburg	Hoffenheim	2	0
9	Augsburg	Braunschweig	4	1
10	Augsburg	Werder Bremen	3	1
11	Augsburg	Nurnberg	0	1	1-Point Win
12	Augsburg	Hannover	1	1
13	Augsburg	Schalke 04	1	2	1-Point Win
14	Augsburg	Leverkusen	1	3
15	Augsburg	Bayern Munich	1	0	1-Point Win
16	Augsburg	Hertha	0	0
17	Augsburg	Hamburg	3	1
18	Augsburg	Ein Frankfurt	2	1	1-Point Win
Sheet7

 

[Snakehips]

Civi,

Something like....

Excel 2007
	A	B	C	D	E
1	HomeTeam	AwayTeam	FTHG	FTAG	Won by only 1 goal?
2	Augsburg	Dortmund	0	4
3	Augsburg	Stuttgart	2	1	Yes
4	Augsburg	Freiburg	2	1	Yes
5	Augsburg	M'gladbach	2	2
6	Augsburg	Wolfsburg	1	2	Yes
7	Augsburg	Mainz	2	1	Yes
8	Augsburg	Hoffenheim	2	0
9	Augsburg	Braunschweig	4	1
10	Augsburg	Werder Bremen	3	1
11	Augsburg	Nurnberg	0	1	Yes
12	Augsburg	Hannover	1	1
13	Augsburg	Schalke 04	1	2	Yes
Sheet2

Cell Formulas
Range	Formula
E2	=IF(ABS(C2-D2)=1,"Yes","")

Hope that helps.

 

[Civitas]

This is great feed back thanks for the help. I'll start with these.