# Limiting Average

#### Perfx

##### Board Regular
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Perfx, Do you have any sample data you can post?

Here you go
Jones Co Lot Progress Log.xls
ABCDEFGHIJKLMNOPQRSTUV
291VintageKnollsElderwood306/15/05Slab06/22/0507/12/05N/An/a8/12/20055120
323EnclaveLinwoodII706/03/05Crawl06/01/0507/05/058/15/20057534
452VintageKnollsStamford206/03/05Slab06/03/0507/19/05N/An/a8/15/20057346
559VintageKnollsLindenII506/21/05Crawl06/22/0507/15/058/16/20055523
2006

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

Lumber Delivery (Column S)

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

Perfx,
In addition to barry houdini's solution, here's another:
Book1
STUVW
1LumberDeliveryStarttoPO30.75
21/1/200625
32/1/200636
48/12/200620
58/15/200634
68/15/200646
78/16/200623
Sheet1

Replies
3
Views
220
Replies
5
Views
191
Replies
4
Views
150
Replies
3
Views
303
Replies
0
Views
189

1,219,961
Messages
6,151,170
Members
451,012
Latest member
needvbahelp1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back