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

scottwisler

New Member
Joined
Jan 7, 2014
Messages
19
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 1Game 2Game 3Game 4Game 5
1 vs 31 3 21 20 21 20 21 20 21 20 21 20
6 vs 86 8 21 20 21 20 21 20 21 20 21 20
5 vs 75 7 21 20 21 20 21 20 21 20 21 20
2 vs 42 4 21 20 21 20 21 20 21 20 21 20
6 vs 76 7 21 20 21 20 21 20 21 20 21 20
5 vs 85 8 21 20 21 20 21 20 21 20 21 20
2 vs 32 3 21 20 21 20 21 20 21 20 21 20
1 vs 41 4 21 20 21 20 21 20 21 20 21 20
1 vs 51 5 21 20 21 20 21 20 21 20 21 20
2 vs 62 6 21 20 21 20 21 20 21 20 21 20
4 vs 84 8 21 20 21 20 21 20 21 20 21 20
3 vs 73 7 21 20 21 20 21 20 21 20 21 20
2 vs 52 5 21 20 21 20 21 20 21 20 21 20
3 vs 83 8 21 20 21 20 21 20 21 20 21 20
1 vs 61 6 21 20 21 20 21 20 21 20 21 20
4 vs 74 7 21 20 21 20 21 20 21 20 21 20
4 vs 64 6 21 20 21 20 21 20 21 20 21 20
1 vs 71 7 21 20 21 20 21 20 21 20 21 20
2 vs 82 8 21 20 21 20 21 20 21 20 21 20
3 vs 53 5 21 20 21 20 21 20 21 20 21 20
2 vs 72 7 21 20 21 20 21 20 21 20 21 20
4 vs 54 5 21 20 21 20 21 20 21 20 21 20
1 vs 81 8 21 20 21 20 21 20 21 20 21 20
3 vs 63 6 21 20 21 20 21 20 21 20 21 20
3 vs 43 4 21 20 21 20 21 20 21 20 21 20
1 vs 21 2 21 20 21 20 21 20 21 20 21 20
5 vs 65 6 21 20 21 20 21 20 21 20 21 20
7 vs 87 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>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

scottwisler

New Member
Joined
Jan 7, 2014
Messages
19
here is where I need to calculate the # of wins:
WINSLOSSESPOINTSPLACE
Team #1 350351
Team #2 303252
Team #3 256153
Team #4 20954
Team #5 1512-55
Team #6 1015-156
Team #7 518-257
Team #8 021-358

<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

New Member
Joined
Jan 7, 2014
Messages
19
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

New Member
Joined
Jan 7, 2014
Messages
19
I am sure there is a much more efficient way of calculating # of wins in this that I am overlooking?
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

Maybe something like

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

Adjust ranges and criteria
 

scottwisler

New Member
Joined
Jan 7, 2014
Messages
19
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

New Member
Joined
Jan 7, 2014
Messages
19

ADVERTISEMENT

any other contributions ? :)
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

New Member
Joined
Jan 7, 2014
Messages
19
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

New Member
Joined
Jan 7, 2014
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top