One way would be to hard code your quarters into 4 separate formulas.
How about entering Q1, Q2, Q3, Q4, in E1 to E4.
Then enter this *array* formulas in F1:
=MIN(IF((C2:C100=DATEVALUE("2003/1/1"))*(C2:C100<=DATEVALUE("2003/3/31")),D
2:D100))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
Copy the formula down to F4, and change the dates.
Array formulas must also be entered with CSE even after revisions.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Mark Rugers" wrote in message
...
Hello,
I want to find the minimum time in which a batch has been produced for
each
quarter of a year. I have tried different formulas (included arrays +
Control/Shift/Enter) with unsatisfied results. Below is a simplified
example
for the year 2003.
A B C D
1 Year Quarter Date Duration of batch
2 2003 Q1 01-02-03 13:45
3 02-02-03 12:45
4 10-03-03 9:54
5 2003 Q2 11-04-03 11:24
6 24-05-03 11:00
7 24-06-03 10:15
8 2003 Q3 14-07-03 11:15
9 14-07-03 10:45
10 02-08-03 16:30
11 2003 Q4 02-11-03 15:00
12 22-11-03 12:45
13 23-12-03 13:00
I hope this makes sense.
Kind regards
Mark
|