Week to week averages

[americanpie3]

Hello,

I run a dart league that runs for 30 weeks. I have a weekly worksheet that I enter the scores and the column Z (starting from 2 all the way down) calculates the average =IFERROR(AVERAGE($B2:$X2),0) and the player's name is on A2 of the sheet

Then I have my results worksheet that gets that info for that player =INDEX(Week1!$Z$1:$Z$60,MATCH(A2,Week1!$A$1:$A$60,0))

That works well. Now here is what I'd like to simplify.

Week 2 on column Z I have this formula =IFERROR(AVERAGE(Week1!B2:X2,Week2!B2:X2),0) so you can imagine what week 30 looks like. As you can see, the player has to always be on A2. So if that player is on like A15 that week, I have to change my formula of week 2 to reflect that R(AVERAGE(Week1!B2:X2,Week2!B15:X15),0).

So to eliminate manual labour, is there something else I can do? It can be on either my sheets of week1 or week2 and so on, or is it better to put it in my results page?

Thank you

 

[Oaktree]

What if you put your data on one sheet (instead of 30) and just used a new "Week Number" column to differentiate between weeks?

If it's all on one page, you could use something like:

=SUMPRODUCT((A2:A500="americanpie3")*B2:X500)/SUMPRODUCT((A2:A500="americanpie3")*(B2:X500>0))

 

[americanpie3]

Because we play multiple games per week. Here is what my weekly sheet looks like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Team 1	Game 1				Game 2				Game 3				Game 4				Game 5				Game 6			Total	Average	High Score	Finish	High Finish							25 paid
2	J-C	52	26	41	J-C	37	14	30	J-C	57	81	81	J-C	41	78	43	J-C	23	93	22	J-C	78	44	41	882	49.00	93	0	0	0	0	0	0	0	0	0
3	Jonathan	40	58	2	Jonathan	24	30	3	Jonathan	13	11	45	Jonathan	52	26	52	Jonathan	58	10	45	Jonathan	6	66	40	581	32.28	66	0	0	0	0	0	0	0	0	4
4	Roland	11	92	11	Roland	85	25	30	Roland	31	37	65	Roland	24	56	32	Roland	46	22	45	Roland	12	36	39	699	38.83	92	2	40	40	0	0	8	0	0	0
5	Close	Roland	Score	40	Close		Score		Close		Score		Close	Roland	Score	8	Close		Score		Close		Score
6	Team 2	Game 1				Game 2				Game 3				Game 4				Game 5				Game 6			Total
7	Sylvain	45	44	36	Sylvain	92	29	37	Sylvain	50	18	48	Sylvain	11	85	28	Sylvain	19	22	65	Sylvain	28	64	35	756	42.00	92	0	0	0	0	0	0	0	0	0
8	Marc-Andr	19	45	29	Marc-Andr	25	36	13	Marc-Andr	58	40	6	Marc-Andr	14	38	37	Marc-Andr	43	33	26	Marc-Andr	60	79	107	708	39.33	107	3	40	14	40	0	22	0	0	1
9	Sylvie F	25	53	30	Sylvie F	43	26	24	Sylvie F	40	18	100	Sylvie F	40	53	25	Sylvie F	11	58	50	Sylvie F	33	60	18	707	39.28	100	0	0	0	0	0	0	0	0	0
Week1

The averages are in column Z

Here is the results page

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team 1	Sex	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
2	J-C	M	49.00	49.00	93	93	0	0	0	0
3	Jonathan	M	32.28	32.28	66	66	0	0	0	0
4	Roland	M	38.83	38.83	92	92	2	2	40	40
5
6
7	Team 2	Sex	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
8	Sylvain	M	42.00	42.00	92	92	0	0	0	0
9	Marc-Andr	M	39.33	39.33	107	107	3	3	40	40
10	Sylvie F	F	39.28	39.28	100	100	0	0	0	0
SeasonSummary1

So in C2 the formula is =INDEX(Week1!$Z$1:$Z$60,MATCH(A2,Week1!$A$1:$A$60,0))

So as you can see, I cannot put it all on one page

 

[Oaktree]

Try this:

Somewhere in your workbook (could be a new sheet, could be on your summary sheet... doesn't really matter), list the names of the worksheets you want to pull from, presumably Week1, Week2, ..., Week30.

Select the range of cells with those sheet names, click in the Name Box (just above column A's header), and type SheetNames to assign the named range SheetNames to reference that list. Note that the list should only include sheets you already have (i.e. if you only have tabs for Week1, Week2, and Week3, those should be the only sheets listed there... if you need to, you can adjust the named range in formulas --> name manager --> edit].

Then, use something like:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!A1:A100"),A2,INDIRECT("'"&SheetNames&"'!Z1:Z100")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!A1:A100"),A2))

That formula sums the averages from column Z for the person listed in cell A2 then divides by the number of times the person listed in cell A2 was found on each of the weekly tabs. That inherently assumes you always play 6 games each week, but it wouldn't matter if, for example, J-C was in row 2 on one sheet and row 25 on another.