game results in excel

  txguest 17:53 11 Jan 2009
Locked

[PRE]
pre>
I am trying to use excel to track the games and their results..

For example:

r/c A B C D
1 t1 7 t2 4
2 t3 9 t4 0
3 t5 4 t6 8

Above example 'score' worksheet has three games and scores of each team. Now I would like to make a 'standings' worksheet from 'score' worksheet.

r/c A B C D E F
1 team w1 w2 w3 w4 w5
2 t1
3 t2
4 t3
5 t4
6 t5
7 t6

In the above worksheet B2 need to be a formula to display 1 if t1 won the game, else 0.

Here is the algorithm that I was thinking of..
if ['score'!a1:a3] has A2 (here A2 has t1)
if 'score'!B[t1-row] > 'score'!D[t1]
1
else
0
else if [ 'score'!c1:c3] has A2
if 'score'!D[t1-row] > 'score'!B[t1-row]
1
else
0
I don't know whether it is possible to have this kind of output in excel.. if it is I don't know which formulas I need to use.

Thanks.

[/PRE]
/pre>

  Forum Editor 23:16 11 Jan 2009

which isn't the correct forum area for it. I'm moving it to the Helproom now.

  Picklefactory 08:08 12 Jan 2009

Few questions that will help.
Are there only ever 2 teams competing against each other in one game (There's no mention of what sort of game, so unknown) and is there a possibilty of teams scoring equally and drawing a game? If so what happens then?
Do all 6 teams play each week? So always 3 games per week? How many weeks in the league?

And for your last part, do you mean you would like to keep the teams listed in numerical order and just show who won a game each week?

  txguest 17:29 12 Jan 2009

[PRE]
Hi Picklefactory,

Thanks for your response.

> Are there only ever 2 teams competing against each other in one game (There's no mention of what sort of game, so unknown) and is there a possibilty of teams scoring equally and drawing a game? If so what happens then?

It is basketball. Yes, in one game, only two teams compete. Actually win gets 2 points, tie get 1 point each, and loss gets 0 points. I did not include this information to avoid too complicated scenario. Since, you mentioned, it is better to have the solution to cover win, tie and loss.

> Do all 6 teams play each week? So always 3 games per week? How many weeks in the league?

Yes, all 6 teams player every week, so there 3 games every week. There are 8 weeks in the league.

> And for your last part, do you mean you would like to keep the teams listed in numerical order and just show who won a game each week?

I used team names t1, t2, etc., as an example. Actual team names can be like longhorns, aggies, sooners, etc. I would like to have the team names in my own order. But, the win/loss points need to be derived based on name of the team in cell A.

For example:

If cell A1 has longhorns, then cells B1:I1 wil be having win/loss points for each week, and I will create a formula in cell J1 to sum B1 to I1.
[/PRE]

  Picklefactory 13:55 13 Jan 2009

Sorry for slow reply, I've been twisting my head on an Excel problem of my own.

I'm sure other mor Excel knowledgable people may have a better route than this, but the way I've managed to get round this is like so. The difficulty I had was looking for values in the grid shape you are using.

Firstly I decided to use a preliminary scoring rule, (A bit like football pools). Where home win=2, away win =3 and draw=1. I doubt home and away has any relevence, but helped me with the problem. Home team in this instance will be the 1st team labelled in each match on the left.
I made a game grid for Wk1 where cells B2:B4 contained 'home' team names, Ants,Cats and Eels.
cells D2:D4 contains 'away' teams ,Bears, Dogs and Frogs, with each teams scores in cells C2:C4 and E2:E4.
I then added the following formula to cell F2
=IF(C2>E2,2,IF(C2=E2,1,3))
Click and drag that down to cover all 3 matches, so you will have a 'pools' style value at the end to show whether game was a draw or a home or away win.
I then needed another 2 columns to place these results in a searchable format ie single column of names.
In cell H2 simply put =B2, click and drag that to cover 3 rows in total (Home teams only) make cell H5 = D5, click and drag that for away teams.
In cell I2 place =IF(F2=2,2,IF(F2=1,1,0)), click and drag for home teams. In cell I5 place =IF(F2=3,2,IF(F2=1,1,0)), click and drag to complete the table.
You should now have a single column of names with wk1 'pools' value.
That's the hard bit done, sorry if my explanation is a little long winded.
You can now copy and paste the whole lot B2:I7 further down the page to create wk2 etc

Now for Overall table on sheet 'Standings'
Set out your table as you showed above and you can now use VLOOKUP feature to search the extra columns you just created.
I had my standings table in cells C4:K10 where ist team name is in C5 and wk1 score in D5. In cell D5 place =VLOOKUP(C5,Score!$H$2:$I$7,2,FALSE), click and drag that down to complete all wk1 scores. And basically carry that across to fill the table.
Hope that all makes sense.

  Picklefactory 13:58 13 Jan 2009

"You should now have a single column of names with wk1 'pools' value"

should read

You should now have a single column of names with wk1 game points.

  Picklefactory 14:01 13 Jan 2009

.... first sheet is named 'Score'

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017