Cricket Points Table

[amruthubballi]

Hi,

I am working on cricket points table, My table goes like this.

Match Schedule
[TABLE="width: 400"]
<tbody>[TR]
[TD]1[/TD]
[TD]Home Team(A1)[/TD]
[TD]Away Team(B1)[/TD]
[TD]Result(C1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RPS[/TD]
[TD]KKR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RCB[/TD]
[TD]RCB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]KKR[/TD]
[TD]KKR[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Points Table
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Teams(E1)[/TD]
[TD]Games Played(F1)[/TD]
[TD]Wins(G1)[/TD]
[TD]Points(H1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RPS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]KKR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]RCB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]GL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am making predections and working permutations so that my favourite team can go for playoffs. I need a formula, when I enter the winner in result column(C1) in Match schedule, Points table needs to be updated for those who played the game.

E.g If I enter the winner of 1st match as KKR in Result(C2), team played(RPS & KKR) should be shown indicating as 1 match played in points table i.e 1 in Games Played(F2) and 1 in Games Played(F3), smilarly if I enter the result of 2nd match as RCB then teams played(RCB & RPS) should shown as 1 in Games Played(F4) and 2 in Games Played(F2) (as RPS have played 2nd match in season)

In Games Played(F) column I want to indicate the number of matches played only after I enter results. Hope I have written enough to understand my problem and to resolve, Please someone help me.

Thank You

 

[FormR]

Hi, welcome to the forum!

Something like this maybe:

Excel 2013
	A	B	C	D	E	F	G	H
1	Home Team(A1)	Away Team(B1)	Result(C1)		Teams(E1)	Games Played(F1)	Wins(G1)	Points(H1)
2	RPS	KKR	KKR		RPS	2	0
3	RCB	RPS	RCB		KKR	2	2
4	KKR	AAA	KKR		RCB	1	1
5					GL	0	0
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTIFS(A:A,E2,C:C,"<>")+COUNTIFS(B:B,E2,C:C,"<>")
G2	=COUNTIFS(C:C,E2)

 

[steve the fish]

You dont seem to have made any provision for if a game is called off and both teams share the 2 points so you could maybe use something like this in G2:

=COUNTIF($C:$C,E2)+COUNTIFS($A:$A,E2,$C:$C,"rain")/2+COUNTIFS($B:$B,E2,$C:$C,"rain")/2

instead of typing the winner in column C you type 'rain' if a game is called off.

 

[amruthubballi]

Thank you so much... it really helped me a lot.

 

[amruthubballi]

Thank you so much... Now I have created as No Result column.