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.

 

[piccolo133]

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

hello, i was looking to build something like this but i am bad at excel, so i copied your sheet to my excel, which is version 365, but the formulas don't work, average and handicap gives 0 as result, may you help me fixing this compatibility issue? it's probably that some fomulas have changed but i don't know which ones or maybe the syntax is wrong

 

[Fluff]

Hi & welcome to MrExcel.

Rather than posting to a 3 year old thread, you are better off starting a thread of your own & posting some data along with what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Larry Anderson]

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.

You need to read and understand the rules by American Bowling Congress. Even if your average is 194.99999 your average is 194. You ALWAYS round down to get average.