new member..need help calculating # of Wins/Losses

[scottwisler]

Hello all! thanks in advance for any assistance! I am using Excel 2003 and trying to calculate the number of wins based on a set schedule of 5 game matches as follows: (MATCH - UPS is in A11, etc) I previously only had 3 games and nested if/than,s but since I am in 2003 and now have 2 more games in each matchup, the formula is too long.

MATCH - UPS			Game 1	Game 2	Game 3	Game 4	Game 5
1 vs 3	1	3	21	20	21	20	21	20	21	20	21	20
6 vs 8	6	8	21	20	21	20	21	20	21	20	21	20
5 vs 7	5	7	21	20	21	20	21	20	21	20	21	20
2 vs 4	2	4	21	20	21	20	21	20	21	20	21	20
6 vs 7	6	7	21	20	21	20	21	20	21	20	21	20
5 vs 8	5	8	21	20	21	20	21	20	21	20	21	20
2 vs 3	2	3	21	20	21	20	21	20	21	20	21	20
1 vs 4	1	4	21	20	21	20	21	20	21	20	21	20
1 vs 5	1	5	21	20	21	20	21	20	21	20	21	20
2 vs 6	2	6	21	20	21	20	21	20	21	20	21	20
4 vs 8	4	8	21	20	21	20	21	20	21	20	21	20
3 vs 7	3	7	21	20	21	20	21	20	21	20	21	20
2 vs 5	2	5	21	20	21	20	21	20	21	20	21	20
3 vs 8	3	8	21	20	21	20	21	20	21	20	21	20
1 vs 6	1	6	21	20	21	20	21	20	21	20	21	20
4 vs 7	4	7	21	20	21	20	21	20	21	20	21	20
4 vs 6	4	6	21	20	21	20	21	20	21	20	21	20
1 vs 7	1	7	21	20	21	20	21	20	21	20	21	20
2 vs 8	2	8	21	20	21	20	21	20	21	20	21	20
3 vs 5	3	5	21	20	21	20	21	20	21	20	21	20
2 vs 7	2	7	21	20	21	20	21	20	21	20	21	20
4 vs 5	4	5	21	20	21	20	21	20	21	20	21	20
1 vs 8	1	8	21	20	21	20	21	20	21	20	21	20
3 vs 6	3	6	21	20	21	20	21	20	21	20	21	20
3 vs 4	3	4	21	20	21	20	21	20	21	20	21	20
1 vs 2	1	2	21	20	21	20	21	20	21	20	21	20
5 vs 6	5	6	21	20	21	20	21	20	21	20	21	20
7 vs 8	7	8	21	20	21	20	21	20	21	20	21	20

<colgroup><col width="219" style="width: 164pt; mso-width-source: userset; mso-width-alt: 8009;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="5"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="4"> <tbody>
</tbody>

 

[scottwisler]

here is where I need to calculate the # of wins:

		WINS	LOSSES	POINTS	PLACE
Team #1		35	0	35	1
Team #2		30	3	25	2
Team #3		25	6	15	3
Team #4		20	9	5	4
Team #5		15	12	-5	5
Team #6		10	15	-15	6
Team #7		5	18	-25	7
Team #8		0	21	-35	8

<colgroup><col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <tbody>
</tbody>

 

[scottwisler]

I have the following and it calc OK for the instances where the team in the matchup is always listed first (Team 1). =SUMPRODUCT(--('Scores-CT#1'!$B$12:$B$45=$P14),--('Scores-CT#1'!$D$12:$D$45>'Scores-CT#1'!$E$12:$E$45))+SUMPRODUCT(--('Scores-CT#1'!$B$12:$B$45=$P14),--('Scores-CT#1'!$F$12:$F$45>'Scores-CT#1'!$G$12:$G$45))+SUMPRODUCT(--('Scores-CT#1'!$B$12:$B$45=$P14),--('Scores-CT#1'!$H$12:$H$45>'Scores-CT#1'!$I$12:$I$45))+SUMPRODUCT(--('Scores-CT#1'!$B$12:$B$45=$P14),--('Scores-CT#1'!$J$12:$J$45>'Scores-CT#1'!$K$12:$K$45))+SUMPRODUCT(--('Scores-CT#1'!$B$12:$B$45=$P14),--('Scores-CT#1'!$L$12:$L$45>'Scores-CT#1'!$M$12:$M$45))

 

[scottwisler]

I am sure there is a much more efficient way of calculating # of wins in this that I am overlooking?

 

[Brian from Maui]

ADVERTISEMENT

Maybe something like

=SUMPRODUCT(--(D1:D100="a")+(E1:E100="a"),--(G1:G100>H1:H100)+(I1:I100>J1:J100))

Adjust ranges and criteria

 

[scottwisler]

hmmm I am not sure if I follow you here. So the D1:D100="a" ..... I assume you mean the D column would be the first team listed in the matchup in the example? What is the "a" I apologize for the ignorance

 

[scottwisler]

ADVERTISEMENT

any other contributions ?

 

[Brian from Maui]

hmmm I am not sure if I follow you here. So the D1:D100="a" ..... I assume you mean the D column would be the first team listed in the matchup in the example? What is the "a" I apologize for the ignorance

My bad, Column D & E houses team names. A was just an example.

 

[scottwisler]

OK so you are just referring the point totals achieved in each game to one team or the other, but then what is the last part attempting to achieve? Sorry but I am a bit lost with this

 

[scottwisler]

So basically there are 8 teams. Each team plays round robin style, all other 7 teams based on the schedule below, but plays 5 games each week. the schedule is recorded as illustrated above with points for the first team listed, then points for the 2nd team listed...thx just wanted to clarify