Limiting Average

[Perfx]

I am using a formula to find the average number of days between two dates across multiple columns. To Further validate this data, I would like to limit the time span which it looks back on. I would like to limit it to looking back across the last 120 days or so. Here is the Formula I am currently using:

=SUMIF(V2:V356,"<100",V2:V356)/COUNTIF(V2:V356,"<100")

Any help is appreciated.

 

[orbea_adam]

Perfx, Do you have any sample data you can post?

 

[Perfx]

Here you go

Jones Co Lot Progress Log.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	LOT#	SUBDIVISION	HOUSETYPE	ELEV#	DRAWING	FND	FNDTYPE	FirstFloorWallHeight	STARTORDER	Pre-Construction	OKtoBuild	P.O.	STCDesignReceived	TJICompleteDate	First&SecondFloorDepths	LUMBERQuoteNumber	DateSTAIRSORDERED	WINDOWSORDERED	LUMBERDELIVERY	PANELDELIVERIES	StarttoDel.Days	StartOrdertoP.O.
2	91	VintageKnolls	Elderwood	3	06/15/05		Slab		06/22/05			07/12/05		N/A	n/a				8/12/2005		51	20
3	23	Enclave	LinwoodII	7	06/03/05		Crawl		06/01/05			07/05/05							8/15/2005		75	34
4	52	VintageKnolls	Stamford	2	06/03/05		Slab		06/03/05			07/19/05		N/A	n/a				8/15/2005		73	46
5	59	VintageKnolls	LindenII	5	06/21/05		Crawl		06/22/05			07/15/05							8/16/2005		55	23
2006

 

[orbea_adam]

Which field do you want to use to limit the date range? "Start Order"?

 

[Perfx]

Lumber Delivery (Column S)

 

[barry houdini]

To give an average of values in V2:V356 where that value is less than 100 and the date in same row in S2:S356 is within the last 120 days

=AVERAGE(IF(TODAY()-S2:S356<120)*(V2:V356<100),V2:V356))

confirmed with CTRL+SHIFT+ENTER

 

[orbea_adam]

Perfx,
In addition to barry houdini's solution, here's another:

Book1
	S	T	U	V	W
1	LumberDelivery			StarttoPO	30.75
2	1/1/2006			25
3	2/1/2006			36
4	8/12/2006			20
5	8/15/2006			34
6	8/15/2006			46
7	8/16/2006			23
Sheet1