Taking the most recent 6 scores and averaging them in an Array

[CycloneSteel]

OK -another golf spreadsheet question! I want to take the most 6 recent weeks for handicap but having trouble with an efficient way to do that. It has to ignore any blank cells too. There will be 20-21 columns and want column B (A for name) to be a running most recent Handicap average of the most recent 6 weeks (in cells C-W for example). Input a new week for everyone in Cell G and want cell B to automatically update with the average. Seems simple enough but I can't for the life of me find a foolproof way. Most recent cell goes to the right of the previous entry.

Thanks!

 

[AhoyNC]

Maybe:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	V	W
1	Name	HC Avg.	4/3/2018	4/10/2018	4/17/2018	4/24/2018	5/1/2018	5/8/2018	5/15/2018	5/22/2018	5/29/2018	6/5/2018	6/12/2018	8/14/2018	8/21/2018
2	Bill	88.00	88		85	91	92	87		88		85
3	Sam	98.83		98	101		95	99	97	102	99
4	Mary	81.83	85	82	84	80		78	82
5	Dan	Less than 6		89	94	92		91
Sheet

 

[CycloneSteel]

Thank you I'll give it shot!

 

[CycloneSteel]

That worked great, thanks! To further complicate things -how do I show the most recent 6 scores on another tab automatically then too?

 

[AhoyNC]

Try:
Again this is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down and across as needed.
This formula will show the most recent score 1st.
You may also just want to use the scores on Sheet2 to calculate the average.

Sheet 2 shows the most recent 6 scores. Raw data is on Sheet 1.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Score 1	Score 2	Score 3	Score 4	Score 5	Score 6
2	Bill	85	88	87	92	91	85
3	Sam	99	102	97	99	95	101
4	Mary	82	78	80	84	82	85
5	Dan	91	92	94	89
Sheet 2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	HC Avg.	4/3/2018	4/10/2018	4/17/2018	4/24/2018	5/1/2018	5/8/2018	5/15/2018	5/22/2018	5/29/2018	6/5/2018	6/12/2018
2	Bill	88.00	88		85	91	92	87		88		85
3	Sam	98.83		98	101		95	99	97	102	99
4	Mary	81.83	85	82	84	80		78	82
5	Dan	Less than 6		89	94	92		91
Sheet 1

 

[shaowu459]

Hi,

Would you please do me a favor? I'm located in China, I create a link of onedrive file, an excel workbook, but I can't open the link with my browser. I'm not sure whether it because the onedrive server is in other country.

Will you please see whether you can open the link or not? The link is :

https://1drv.ms/x/s!AoWUNraGq0TDiiGn34TSAi4MFqXk

Thanks a lot!

Clark

 

[CycloneSteel]

Thanks you AhoyNC, problem(s) solved!

 

[AhoyNC]

You're welcome. Thanks for the feedback.