Need to find Min value based on date range entered

C

Chad S

I need to calculate the Min value within a certain date range.

My data:

A B C
D
1 Order # Date Received Date Processed Duration
2 1 01/05/04 01/15/04 10
3 2 01/12/04 01/21/04 09
4 3 01/22/04 02/01/04 10
5 4 01/30/04 02/09/04 10
6 5 02/05/04 02/20/04 15
7 6 02/15/04 02/26/04 11
8 7 02/27/04 03/05/04 07
9 8 02/28/04 03/15/04 16

I need a function that I can use to generate monthly metrics for th
above sample data (my actual data consists of 500+ orders which span
years). I would like to know the Min "Duration" value for each mont
based on "Date Processed" completions in each month (so for example m
date range for Feb 04 would be 02/01/04 -> 02/29/04 and the MI
"Duration" would be 10).

Eventually I am going to generate similar functions for MAX, AVG an
STD DEV.

I have tried the DMIN function but keep getting #VALUE! errors.

Any help would be greatly appreciated!!! Thanks
 
A

Aladin Akyurek

Assuming that the durations are true numbers, e.g., 09 is really 9, not
text...

=MIN(IF(TEXT(DateProcessedRange,"mm-yyyy")=TEXT(F2,"mm-yyyy")),DurationRange))

which you need to confirm with control+shift+enter instead of just with
enter.

F2 supposed to house a true date criterion in the form of e.g.,
1-Feb-04.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top