Having trouble with generating correct data with Formulas

[Bill]

Hi, I have a large excel data worksheet which has this
basic look:

Item: PointA PointB PointC PointD PointE
#01 01-Feb 04-Feb 05-Feb 13-Feb 22-Feb
#02 03-Feb 06-Feb 10-Feb 15-Feb 27-Feb
#03 03-Feb 15-Feb 19-Feb 01-Mar 10-Mar
#04 15-Feb 22-Feb 28-Feb 01-Mar 12-Mar
#05 20-Feb 22-Feb 28-Feb 03-Mar 06-Mar
#06 24-Feb 28-Feb 11-Mar 12-Mar 14-Mar
#07 11-Mar 14-Mar 16-Mar 17-Mar 22-Mar

Its much larger.. (700 rows/25 columns) but this gives a
picture: I have been trying to figure out a way
(Formuals) to evaluate the AVERAGE time in Days between
Points (by month..and in days) and place the results on
another worksheet. I am doing this manually... but there
has to be a better way (and faster) Currently I am
manually calculating the days between points for each
item. Then sorting point "A" for all of Feb and then
calculating the Average for Feb - March Etc. so I can
say for example:
In January-02 the Average time it took for (insert the
number of time Jan-02 appeared in Column B) an
average of 5.8 days as compared to June-03 we had the
volume (insert the number of times Jun-02 appears in
Column B) an average of 10.4 days .. to get from point A
to point B
Thanks for your help

 

[Biff]

Hi Bill,

I think I have it this time. Each time this problem is
posted however, the description of what you're trying to
do changes! And so the quality of response is based on the
quality of detail provided. This is a rather complicated
problem which is why there haven't been a lot of replies!

Anyhow, column B is pointA and column C is pointB:

=SUM(IF(C2:C8<=DATE(2003,2,28),IF(B2:B8>=DATE
(2003,2,1),C2:C8-B2:B8,0)))/SUMPRODUCT((B2:B8>=DATE
(2003,2,1))*(B2:B8<=DATE(2003,2,28)))

Entered as an array - CTRL+SHIFT+ENTER

Based on the posted example data returns 5.16 avg number
of days from pointA to pointB for the month of FEB 2003.

In an earlier post the date format was DD-MMM-YY.
You'll have to enter a seperate formula for each month of
each year and edit for the appropriate DATE values and do
the same for each point to the next.

Biff

 

[Bill]

Hi Biff,

Thanks for the reply.. I do realize that I wasnt
explaning it correctly.. and for a while I was waiting
to get chastized for reposting it

I think (?) I am understanding what your doing...

So your equation reads in basic english:

=SUM(IF(C2:C8<=DATE(2003,2,28),IF(B2:B8>=DATE

Add all the dates less than 28-Feb-03 in Column C
Add all the dates greather than 01-Feb-03 in Column B

(2003,2,1),C2:C8-B2:B8,0)))/SUMPRODUCT((B2:B8>=DATE

(2003,2,1))*(B2:B8<=DATE(2003,2,28)))

Total of C minus B (whats the zero for???????????)
Divided by the number of occurances in the B column with
respected dates given??

I am trying it now.. but its not working... but let me
keep trying it.... Also, (to make this alot more fun
for me----> I have N/D, N/A, NA, ND, NK listed where
the dates were not done, not applicable etc etc... Yes..
life gets better as the equations get harder.. but I am
learning..

 

[Biff]

Hi Bill,

Some things that seem so simple aren't that easy to
explain sometimes! As I said, this is a pretty tricky
puzzle and that's why there were few, if any replies.
These are the kinds of things I look for here! Sometimes I
can figure them out and sometimes I can't.

Here's what the formula does - It subtracts column B from
column C and sums the total to come up with the total
number of days that fall within the date criteria. It then
counts the number of entries in column B that fall within
the date criteria. It then divides the the total days by
the date count to arrive at an avg.

The 0 is the value that will be returned if none of the
date criteria are met. You can use anything you want for
this. If you ommit this argument the default is FALSE.

The ND, NA and such do throw a monkey wrench into the mix!!
Let me ask you this, based on the sample data posted what
result are you expecting? It may be easier to decipher in
reverse.

Here's what I got for FEB 2003 from pointA to pointB - 31
total days divided by the number of entries for FEB, 6.

Biff

 

[Bill]

Hi Biff,

Thanks again for responding! I agree its throwing a
wrench into the system.. heheheh

Would you be willing to look at what I have and the
results that I manually generated... ??

I am really trying to learn more and more about excel and
the formulas... even though I have the results... I want
to learn more and more.. as I find it awesome to be able
to generate formulas.. that make excel that much
easier.... Thanks,

 

[Biff]

Yeah, if you want to send a copy I'll take a look ... no
guarantees though! You'll have to change my address to
biff_in_pitt

Biff