Finding the minimum in a selected number of rows of the same colum

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

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

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" <[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

Slight change:

=MIN(OFFSET(A2,MATCH(G1&H1,A2:A11&B2:B11,0)-1,3,3))

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Here's another way, provided your format is consistent...

=SUMPRODUCT(--(A2:A13=F2),--(B2:B13=G2),SUBTOTAL(5,OFFSET(D2:D13,ROW(D2:D
13)-ROW(D2),0,3)))

....where F2 contains the year, such as 2003, and G2 contains the
quarter, such as Q2. Also, if you're going to change the way your data
is laid out, as Biff has described, you can easily do that by doing the
following...

1) Select A2:B13

2) Edit > Go To > Special > Blanks > Ok

3) Press =

4) Press the 'Up Arrow'

5) Confirm with CONTROL+ENTER

Hope this helps!

In article <[email protected]>,
"Mark Rugers" <[email protected]> wrote:

> 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

Thanks a lot, it works. I changed the formula a little bit, so I can copy the
formula without changing the dates every time.

F G H I
1
2 2003
3 Q1 Q2 Q3 Q4
4 01-01-03 01-04-03 01-07-03 01-10-03
5 31-03-03 30-06-03 30-09-03 31-12-03
6 9:54 10:15 10:45 12:45

=IF(TODAY()>F5;MIN(IF(($C$2:$C$25>=DATE(YEAR(F4);MONTH(F4);DAY(F4)))*($C$2:$C$25<=DATE(YEAR(F5);MONTH(F5);DAY(F5)));$D$2:$D$25));"")

Mark


"RagDyer" schreef:

> 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" <[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
>
>