A more Dynamic pread sheet to calculate averages

[americanpie3]

Hello everyone,

I run a dart league. I have a worksheet with players on their teams. Each week I enter the stats. I have stats for averages for the season and per week.

Per week it runs perfectly fine
=IFERROR(AVERAGE($B4:$X4),0)

Here's the average for the season

=AVERAGE(Week1!B69:X69,Week2!B69:X69,Week3!B69:X69,Week4!B4:X4,Week5!B4:X4,Week6!B4:X4,Week7!B4:X4)

As you can see this person has changed teams. I want to make this sheet more dynamic because if this person changes teams again I won't have to change the formula, each week I will have to change this formula and of course all I want to do is enter numbers. Of course once the sheet if perfect, I will hand it off to someone else.

Can anyone suggest something?

Thanks

 

[oldbrewer]

running average	6	01/01/2014	08/01/2014	15/01/2014	22/01/2014	29/01/2014	05/02/2014
67.17	ann	63	65	64	72	69	70
70.50	bill	64	66	77	70	72	74
71.67	colin	65	67	69	81	73	75
71.00	dave	66	68	70	72	74	76
70.83	eddie	60	69	71	73	75	77
73.83	fred	68	75	72	74	76	78
75.17	george	69	71	80	75	77	79
73.33	harry	70	72	74	66	78	80
75.33	ian	71	73	75	77	75	81
75.83	john	72	74	76	78	73	82
76.83	kevin	73	75	77	79	74	83
79.50	larry	74	79	78	80	82	84
	I assume the average throw of 3 darts is what you want to average
	or is it darts to finish - if this opponent does not finish
	or is it game wins ie best of 11 won 8 lost 3

<colgroup><col><col><col span="2"><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[juweh]

I think, it will be much easier for you if you will just use a pivot table for this.

 

[americanpie3]

running average	6	01/01/2014	08/01/2014	15/01/2014	22/01/2014	29/01/2014	05/02/2014
67.17	ann	63	65	64	72	69	70
70.50	bill	64	66	77	70	72	74
71.67	colin	65	67	69	81	73	75
71.00	dave	66	68	70	72	74	76
70.83	eddie	60	69	71	73	75	77
73.83	fred	68	75	72	74	76	78
75.17	george	69	71	80	75	77	79
73.33	harry	70	72	74	66	78	80
75.33	ian	71	73	75	77	75	81
75.83	john	72	74	76	78	73	82
76.83	kevin	73	75	77	79	74	83
79.50	larry	74	79	78	80	82	84
	I assume the average throw of 3 darts is what you want to average
	or is it darts to finish - if this opponent does not finish
	or is it game wins ie best of 11 won 8 lost 3

<colgroup><col><col><col span="2"><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Well it goes like this. We play 6 games in one night. We take each player's 1st scores per game.

So it would be like
Game 1 Game 2
Dan 45 60 100 Dan 45 40 15
Pat 22 45 35 Pat 22 40 40
Dave 60 100 60 Dave 45 45 57

I enter in all scores of the 1st 3 shots per game as the example above. This is why my 1st like is =IFERROR(AVERAGE($B4:$X4),0) which calculates averages for the week and then my season average will change with Week 2.

Let me know if this info helps and thanks for the reply.

 

[oldbrewer]

		6
	49.33	ann	20	60	45	100	26	45
	59.67	bill	95	57	41	45	95	25
	89.17	fred	100	120	60	180	45	30
		as you enter more scores the total will be first 12, first18 automatically divided by 12, 18 etc
		formula for ann's average of 49.33
		=SUM(E8:Z8)/$D$7
		notice it goes right to Z8
		as does thecounter cell (D7)

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[americanpie3]

		6
	49.33	ann	20	60	45	100	26	45
	59.67	bill	95	57	41	45	95	25
	89.17	fred	100	120	60	180	45	30
		as you enter more scores the total will be first 12, first18 automatically divided by 12, 18 etc
		formula for ann's average of 49.33
		=SUM(E8:Z8)/$D$7
		notice it goes right to Z8
		as does thecounter cell (D7)

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

Hi old brewer,

If I understand correctly, your formula only shows how to calculate averages. My question is not about calculating averages but making it more dynamic as in your example, Ann, next week, will be in row 2 instead of 8. Then I need a formula to make sure it calculates averages from week 1 and week 2 and so on.

So taking that example I would need 1 formula that would look for Ann take all the scores in week 1 and week 2 no matter which row their on and put the results in my season summery sheet.

Thanks again.

 

[americanpie3]

Hi everyone. I would love to hear more suggestions.

Thanks