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

[Guest]

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

 

[Biff]

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,D213)))

Biff

 

[RagDyer]

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
2100))

--
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!
==============================================

 

[Biff]

Slight change:

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

Biff

 

[Domenic]

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

=SUMPRODUCT(--(A2:A13=F2),--(B2:B13=G2),SUBTOTAL(5,OFFSET(D213,ROW(D2
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!

 

[Guest]

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: