Rolling Look-backs In Excel... With Intermittent Zero Value Cells Throughout

[cmjay]

Hi All,

I've never asked the board for help directly before as I've always been able to find answers in reply to other posters' questions! This time I am truly stumped.

I've got a spreadsheet that lists employee hours worked by week, I'm trying to create a function that would give an automatic look-back of both 90 days and 6 months. I've found some useful answers in other threads, but they tend to hinge on a zero value cell signaling the latest week (not) worked; however, for this purpose, some employees may have zero value fields for some weeks but have worked others.

I'm looking for a formula that can be uniformly applied to all employees in the spreadsheet and average their weekly hours for the past 90 or 180 days without having to update the formula each week new hours are entered.

Any assistance you all could provide would be greatly appreciated!

Thanks,

Chris

 

[bschwartz]

Can you show how your data is structured?

 

[cmjay]

Thanks for the reply!

Does this help? I'm trying to add the average at the end of a long set of data; it will correspond to a row rather than a column.

[TABLE="class: grid, width: 1100"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]FT/PT[/TD]
[TD]Notes[/TD]
[TD]Shift[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD="align: right"]5/25/2013[/TD]
[TD="align: right"]6/1/2013[/TD]
[TD="align: right"]6/8/2013[/TD]
[TD="align: right"]6/15/2013[/TD]
[TD="align: right"]6/22/2013[/TD]
[TD="align: right"]6/29/2013[/TD]
[TD="align: right"]7/6/2013[/TD]
[TD="align: right"]7/13/2013[/TD]
[TD="align: right"]7/20/2013[/TD]
[TD="align: right"]7/27/2013[/TD]
[/TR]
[TR]
[TD]Deo[/TD]
[TD]John[/TD]
[TD]FT/PT[/TD]
[TD]None[/TD]
[TD]Nights[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12.5[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]20.5[/TD]
[/TR]
</tbody>[/TABLE]

Chris

 

[bschwartz]

Here are two possible methods:

Excel 2010
	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
1	Last Name	First Name	FT/PT	Notes	Shift	5/18/2013	5/25/2013	6/1/2013	6/8/2013	6/15/2013	6/22/2013	6/29/2013	7/6/2013	7/13/2013	7/20/2013	7/27/2013	8/3/2013	8/10/2013	8/17/2013	8/24/2013	8/31/2013	9/7/2013	9/14/2013	9/21/2013	9/28/2013	10/5/2013	10/12/2013	10/19/2013	10/26/2013	11/2/2013	11/9/2013	11/16/2013
2	Deo	John	FT/PT	None	Nights	12	12.5	31	0	16	18	15.5	21	15.5	7	20.5	4	19	19	14	21	28	18	8	7	0	11	24	4	30	15	22
Average 90

Excel 2010
	AH	AI	AJ
1		90	180
2	With helper	15.785714	15.296296
3	Without Helper	15.785714	15.296296
Average 90

Cell Formulas
Range	Formula
AI2	=SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=AI1),$F$2:$AF$2)/SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=AI1))
AI3	=SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=90),$F$2:$AF$2)/SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=90))
AJ2	=SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=AJ1),$F$2:$AF$2)/SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=AJ1))
AJ3	=SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=180),$F$2:$AF$2)/SUMPRODUCT(--(TODAY()-$F$1:$AF$1<=180))