Averaging using IF statements between Dates

[Steve Kay]

Hi Everyone,

Can anyone help? I have an excel chart which you could
think of timing an item on an assembly line.. but in
days. There are 500 rows (item numbers) and 20 different
locations where this item has to go. The time frame
between locations is in Days.

This table is representing all of 2002 and 2003. For
Example:
PointA PointB PointC
ITEM#0001 01-Nov-02 14-Oct-02 11-Dec-02

The Date represents its ARRIVAL Day.

I am trying to look at the averages of movement by month
for each destination.. So if the first 20 items arrived
from A to B in Oct-02 I want to look at ONLY the average
time for those 20 items which have a date in the Month of
October.

I would like to do this for each point on the 'assembly
line' and by month..

I am currently doing this manually... can any suggest
formulas??? thanks .. Steve

 

[Biff]

Hi Steve,

Here's one way:

=AVERAGE(IF(AND(C191:C200>=DATE(2003,10,1),C191:C200<=DATE
(2003,10,31)),D191200,""))

This will average all values in the range that fall
between 10/1/2003 and 10/31/2003.

Adjust range to suit.

Biff

 

[Biff]

Ooops! Entered as an array, hold CTRL,SHIFT and hiy ENTER

Biff