=AVERAGE(IF(AND....

[Steve Kay]

Hi Everyone, This is a repost from the 03-Oct-03 with an
addtional question: The answer I recd. from Biff (Thanks
Biff) works.. but is there a different way?? Here is the
original posting w/answer:
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

Answer: =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.
Entered as an array, hold CTRL,SHIFT and hit ENTER-Biff

Additional Question: This provides the average DATE.. How
can I use this to get the Average Number of Days for for a
month? Example... In october the average was 10 days...
In November the average was 15 days.. etc. Thanks

 

[Biff]

Hi Steve,

I think this will work:

=COUNT(A1:T1)/SUMPRODUCT((A1:T1>=DATE(2003,10,1))*
(A1:T1<=DATE(2003,10,30)))

This will count all the date entries on a row and then
divide by the total number of days that fall between the
specified date criteria thus producing the average number
of days.

Biff