Hi!
Try this......
Enter the year and quarter you want the info for in two cells:
G1 = 2003
H1 = Q2
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MIN(OFFSET(A2,MATCH(G1&H1,A2:A11&B2:B11,0),3,3))
Format the cell as TIME
This assumes the pattern of 3 batches per qtr is constant.
You'd be much better off if you stored the data in a true database style:
> 1 Year Quarter Date Duration of batch
> 2 2003 Q1 01-02-03 13:45
> 3 2003 Q1 02-02-03 12:45
> 4 2003 Q1 10-03-03 9:54
> 5 2003 Q2 11-04-03 11:24
> 6 2003 Q2 24-05-03 11:00
> 7 2003 Q2 24-06-03 10:15
Then you could use this array formula:
=MIN(IF(A2:A13=G1,IF(B2:B13=H1,D2:D13)))
Biff
"Mark Rugers" <[email protected]> wrote in message
news:[email protected]...
> 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
Bookmarks