Min and Max Function

[Ruan]

Hello,

I able to find the Min and Max date from a Range of Dates. However, I would
like the Min and Max date for a range of facilities -

Data
Facility 1 12/21/2005
Facility 2 10/15/2001
Facility 1 03/27/2003
Facility 3 07/02/2006
Facility 3 11/17/2002
Facility 1 02/25/1999
Facility 2 05/10/2004
Facility 3 11/02/2005
Facility 1 08/21/2003
Facility 2 03/12/2002


Result Min Max
Facility 1 02/25/1999 12/21/2005
Facility 2 10/15/2001 05/10/2004
Facility 3 11/17/2002 07/02/2006

Thanks
Ruan

 

[Pete_UK]

Try these array* formulae:

in B20: =MIN(IF(A20=A$2:A$18,B$2:B$18,40000))
in C20: =MAX(IF(A20=A$2:A$18,B$2:B$18,0))

* As these are array formulae, then once you have typed them in (or
subsequently edit them) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly, then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

I have assumed that your data is in rows 2 to 18 and that your summary
table starts in row 20 - adjust ranges and cell references to suit.

You can copy and paste the two formulae down two more rows in the
normal way.

Hope this helps.

Pete

 

[Guest]

I think this is most easily done with a pivot table

Make sure you have column titles (eg Fac, Date)

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Fac field here
DATA: Drag the Date field here
dbl-click it and set it to Max

DATA: Drag the Date field here (again)
dbl-click it and set it to Min

Click [OK]
Select where you want the Pivot Table

Click and hold on the DATA heading,
drag it on top of the Total, and release

That will list each Facility and the respective Max and Min of dates.
Format them as dates if necessary

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ruan]

Pete,

That is exactly what I was looking for. Thanks

 

[Pete_UK]

Thanks for feeding back.

Pete

Pete,

That is exactly what I was looking for. Thanks