Bowling Secretary Spreadsheet

[rlabonte68]

I am trying to create a spreadsheet that will calculate averages and handicap. Bowl 3 games per week with an average each week, so an average after 3 games, 6 games and so forth. Handicap is calculated as such, 90% of 190. You subtract their average from 190 then times .9. I also need the handicap result to round down if decimal is .5 or lower and round up if over .5.

 

[mackc557]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Standard	Percentage	Game Average
3	190	90%	6
4
5		Average	Handicap	Game 1	Game 2	Game 3	Game 4	Game 5	Game 6	Game 7	Game 8	Game 9	Game 10	Game 11	Game 12
6	Player 1	0	171	0	0	0	0	0	0	0	0	0	0	0	0
7	Player 2	0	171	0	0	0	0	0	0	0	0	0	0	0	0
8	Player 3	0	171	0	0	0	0	0	0	0	0	0	0	0	0
9	Player 4	0	171	0	0	0	0	0	0	0	0	0	0	0	0
10	Player 5	0	171	0	0	0	0	0	0	0	0	0	0	0	0
11	Player 6	0	171	0	0	0	0	0	0	0	0	0	0	0	0
12	Player 7	0	171	0	0	0	0	0	0	0	0	0	0	0	0
13	Player 8	0	171	0	0	0	0	0	0	0	0	0	0	0	0
14	Player 9	0	171	0	0	0	0	0	0	0	0	0	0	0	0
15	Player 10	0	171	0	0	0	0	0	0	0	0	0	0	0	0
16	Player 11	0	171	0	0	0	0	0	0	0	0	0	0	0	0
17	Player 12	0	171	0	0	0	0	0	0	0	0	0	0	0	0
18	Player 13	0	171	0	0	0	0	0	0	0	0	0	0	0	0
19	Player 14	0	171	0	0	0	0	0	0	0	0	0	0	0	0
20	Player 15	0	171	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B6:B20	B6	=AVERAGE(INDIRECT(ADDRESS(ROW(),COUNTA(INDIRECT(ROW()&":"&ROW()))- GamesToAverage +1) & ":" & ADDRESS(ROW(),COUNTA(INDIRECT(ROW()&":"&ROW())))))
C6:C20	C6	=IF(MOD(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),1)<= 0.5, ROUNDDOWN(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),0),ROUNDUP(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),0))

Named Ranges
Name	Refers To	Cells
GamesToAverage	=Sheet1!$C$3	B6:B20
HandicapPercentage	=Sheet1!$B$3	C6:C20
Standard	=Sheet1!$A$3	C6:C20

You can change the names of the players, adjust your standard as needed, change your handicap percentage as needed, change how many games to use for averaging, add more players by expanding the columns B and C downward. Player name must have something in it. Games can not have gaps (if there is a Game 5 with a value, game 1,2,3, and 4 must all have some value). The average is calculated with the last X number of games; if you need it to be the first X number of games, let me know.

 

[mackc557]

I made a correction so that if a player has less games than the averaged number, it will use all of their games.
Please use the below pasted formulas as the previous ones didn't have this correction.
Not every player needs to have the same number of games, for example:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Standard	Percentage	Game Average
3	190	90%	6
4
5		Average	Handicap	Game 1	Game 2	Game 3	Game 4	Game 5	Game 6	Game 7	Game 8	Game 9	Game 10	Game 11	Game 12
6	Player 1	117.75	65	179	40	102	96	40	143	219	182	185	121	35	71
7	Player 2	143.6666667	42	218	117	196	47	153	125	119	157	161
8	Player 3	125.6666667	58	116	81	177	101	58	71	127	135	216	144	220	62
9	Player 4	111	71	212	122	102	87	94	49
10	Player 5	145.75	40	173	100	66	183	95	142	193	137	176	200	182	102
11	Player 6	113.0833333	69	213	199	78	191	52	105	58	59	83	180	37	102
12	Player 7	141.625	44	170	110	160	89	123	195	71	215
13	Player 8	130.3333333	54	40	185	218	146	211	148	74	53	201	154	66	68
14	Player 9	126.1666667	57	51	102	101	101	166	203	130	100	131	83	218	128
15	Player 10	118.1818182	65	159	112	186	113	62	78	73	89	99	206	123
16	Player 11	128.8333333	55	95	53	211	196	74	76	106	219	81	214	130	91
17	Player 12	134.4166667	50	59	163	40	109	195	160	115	133	217	91	200	131
18	Player 13	120.5833333	62	169	81	46	106	77	86	213	142	37	151	197	142
19	Player 14	114.2	68	93	41	55	75	168	115	171	50	215	159
20	Player 15	118	65	48	188
Sheet1

Cell Formulas
Range	Formula
B6:B20	B6	=AVERAGE(INDIRECT(IF(COUNTA(INDIRECT(ROW()&":"&ROW())-3)>=GamesToAverage,ADDRESS(ROW(),COUNTA(INDIRECT(ROW()&":"&ROW()))- GamesToAverage +1), ADDRESS(ROW(),COLUMN()+2))& ":" & ADDRESS(ROW(),COUNTA(INDIRECT(ROW()&":"&ROW())))))
C6:C20	C6	=IF(MOD(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),1)<= 0.5, ROUNDDOWN(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),0),ROUNDUP(((Standard-INDIRECT(ADDRESS(ROW(),COLUMN()-1)))*HandicapPercentage),0))

Named Ranges
Name	Refers To	Cells
GamesToAverage	=Sheet1!$C$3	B6:B20
HandicapPercentage	=Sheet1!$B$3	C6:C20
Standard	=Sheet1!$A$3	C6:C20

Hope it helps

 

[rlabonte68]

thank you for the input, this is not quite what i need plus I'm a little lost. I've attached an example. As you can see in the example i have 3 games then a total for the the games. I need an average for the total pins which is at the bottom. After adding the 2nd week i need an average for 6 games and 9 after week three and so on.

bowling sheet.xlsx
	A	B	C	D	E	F	G
1	Week	Game 1	Game 2	Game 3	Series		Average
2	1	175	187	200	562		180
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	562
27
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(B2:D2)
E26	E26	=SUM(E2:E25)

 

[rlabonte68]

forgot a cell for handicap

bowling sheet.xlsx
	A	B	C	D	E	F	G	H
1	Week	Game 1	Game 2	Game 3	Series		Average	Handicap
2	1	175	187	200	562		180	9
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	562
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUM(B2:D2)
H2	H2	=(190-G2)*0.9
E26	E26	=SUM(E2:E25)

 

[Fluff]

Not sure where your average of 180 comes from, but is this what you need

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Week	Game 1	Game 2	Game 3	Series		Average	Handicap
2	1	175	187	200	562		187.33	2
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	562
Lists

Cell Formulas
Range	Formula
E2	E2	=SUM(B2:D2)
G2	G2	=AVERAGE(B2:D25)
H2	H2	=ROUND((190-G2)*0.9,0)
E26	E26	=SUM(E2:E25)

 

[rlabonte68]

the 180 was just a number i put in to show average. Will this work once i start adding other games in other weeks. I would also like the average to be a whole number no decimal but it needs to round down, so 187.5 will be 187 and 187.6 will be 188

 

[Fluff]

Will this work once i start adding other games in other weeks.

Yup.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Week	Game 1	Game 2	Game 3	Series		Average	Handicap
2	1	175	187	200	562		188	2
3	2	163	205	197	565
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	1127
Lists

Cell Formulas
Range	Formula
G2	G2	=ROUND(AVERAGE(B2:D25)-0.01,0)
H2	H2	=ROUND(((190-G2)*0.9)-0.001,0)
E2:E3	E2	=SUM(B2:D2)
E26	E26	=SUM(E2:E25)

 

[rlabonte68]

That is just what i was looking for. thank you so much for your help and for the quick responses.

 

[Fluff]

Glad we could help & thanks for the feedback.