Formula help required please

[john.bedford3]

I have a spreadsheet part of which shows a list of dates, values and
Locations ( in three columns) an extract of which is shown below.

DATE VALUE LOCATION
25-Jun-2004 20.00 A
16-Jun-2004 19.00 D
08-Oct-2004 18.00 G
23-Aug-2002 17.00 E
26-Nov-1983 16.00 NK
03-May-1983 15.00 R
31-Aug-2002 14.00 NK
16-Aug-2002 13.00 P
16-Aug-2002 12.00 G
06-Jul-2002 11.00 J
27-Oct-1984 10.00 K
01-Jun-2003 9.00 P
04-Oct-2002 8.00 Z
30-Aug-2002 7.00 F
17-Sep-2002 6.00 S
08-Mar-1981 5.00 W
23-Apr-1984 4.00 M

From this list which is constantly updated with new information I wish to
extract rows of information for the Maximum and Minimum value in each month
regardless of year and display in a table as follows.

Max Min
Date Value Location Date Value Location
JAN
FEB
MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00 J
AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
SEP
OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
DEC

I have been thinking how I can do this for a few days now without any
solution coming to mind. I hope there is an easy solution. So if anybody can
point me in the right direction it would be most appreciated.

Thanks
--
John Bedford
Delete extra @ to reply.

[Bob Phillips]

John,

This gets the max value for say March

=MAX(IF(MONTH(A2:A20)=3,B2:B20))

Then use that value to get the date and location

=INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))

and


=INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:[email protected]...
> I have a spreadsheet part of which shows a list of dates, values and
> Locations ( in three columns) an extract of which is shown below.
>
> DATE VALUE LOCATION
> 25-Jun-2004 20.00 A
> 16-Jun-2004 19.00 D
> 08-Oct-2004 18.00 G
> 23-Aug-2002 17.00 E
> 26-Nov-1983 16.00 NK
> 03-May-1983 15.00 R
> 31-Aug-2002 14.00 NK
> 16-Aug-2002 13.00 P
> 16-Aug-2002 12.00 G
> 06-Jul-2002 11.00 J
> 27-Oct-1984 10.00 K
> 01-Jun-2003 9.00 P
> 04-Oct-2002 8.00 Z
> 30-Aug-2002 7.00 F
> 17-Sep-2002 6.00 S
> 08-Mar-1981 5.00 W
> 23-Apr-1984 4.00 M
>
> From this list which is constantly updated with new information I wish to
> extract rows of information for the Maximum and Minimum value in each
month
> regardless of year and display in a table as follows.
>
> Max Min
> Date Value Location Date Value
Location
> JAN
> FEB
> MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00 J
> AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> SEP
> OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> DEC
>
> I have been thinking how I can do this for a few days now without any
> solution coming to mind. I hope there is an easy solution. So if anybody
can
> point me in the right direction it would be most appreciated.
>
> Thanks
> --
> John Bedford
> Delete extra @ to reply.
>
>
>
>
>
>
>
>
>
>
>

[duane]

I think this does it. I put your data in cells a2:c18, and listed months (as dates) in cells a21:a32

b21:b32 are dates for month mins, c21:c32 are min for the month, and d21:d32 are the locations for the min
b21:b32 are dates for month maxes, c21:c32 are max for the month, and d21:d32 are the locations for the max

b21:
=SUMPRODUCT((MONTH($A21)=MONTH($A$2:$A$18))*(C21=$B$2:$B$18)*($A$2:$A$18))

c21:
=MIN(IF(MONTH($A$2:$A$18)=MONTH($A21),$B$2:$B$18))

(this is an array formula - control/shift/enter)

d21:
=IF(B21=0,"",VLOOKUP(B21,$A$2:$C$18,3,FALSE))

e21:
=SUMPRODUCT((MONTH($A21)=MONTH($A$2:$A$18))*(F21=$B$2:$B$18)*($A$2:$A$18))

f21:
=MAX(IF(MONTH($A$2:$A$18)=MONTH($A21),$B$2:$B$18))

(this is an array formula - control/shift/enter)

g21:

=IF(E21=0,"",VLOOKUP(E21,$A$2:$C$18,3,FALSE))

I also suppressed zero values on the sheet (tool, option, view)
as if there is no data for a month (such as Jan) the formulas result in zero

hope this helps..................

[JE McGimpsey]

Given that this is to be updated constantly, note that Bob's excellent
solution is highly dependent your values being unique. For instance it
won't work if the max entry of a month has the same value as a previous
month, e.g.,

with

25-Jun-2004 20.00 A
16-Jun-2004 19.00 D
08-Oct-2004 19.00 G

Then your October Max Date:

=INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=10,B2:B20)),B2:B20,0))

will return

16-Jun-04



In article <[email protected]>,
"Bob Phillips" <[email protected]> wrote:

> This gets the max value for say March
>
> =MAX(IF(MONTH(A2:A20)=3,B2:B20))
>
> Then use that value to get the date and location
>
> =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
>
> and
>
>
> =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))

[duane]

I agree - that is why I went with admittedly much more unwieldy formulas - to allow for redundent values. it also seems like the data given is sorted in decending order - that is not necessary.

[john.bedford3]

I think I must be doing something wrong. I applied your first formula to the
example data I supplied for each of the 12 months and got the following
results displayed.

Jan 0.00, Feb 0.00, Mar 0.00, Apr 0.00, May 20.00, Jun 0.00, Jul 0.00, Aug
20.00, Sep 0.00, Oct 20.00, Nov 0.00, Dec 0.00

However, if I click on the = sign on the formula bar the results shown are
correct.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> John,
>
> This gets the max value for say March
>
> =MAX(IF(MONTH(A2:A20)=3,B2:B20))
>
> Then use that value to get the date and location
>
> =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
>
> and
>
>
> =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:[email protected]...
> > I have a spreadsheet part of which shows a list of dates, values and
> > Locations ( in three columns) an extract of which is shown below.
> >
> > DATE VALUE LOCATION
> > 25-Jun-2004 20.00 A
> > 16-Jun-2004 19.00 D
> > 08-Oct-2004 18.00 G
> > 23-Aug-2002 17.00 E
> > 26-Nov-1983 16.00 NK
> > 03-May-1983 15.00 R
> > 31-Aug-2002 14.00 NK
> > 16-Aug-2002 13.00 P
> > 16-Aug-2002 12.00 G
> > 06-Jul-2002 11.00 J
> > 27-Oct-1984 10.00 K
> > 01-Jun-2003 9.00 P
> > 04-Oct-2002 8.00 Z
> > 30-Aug-2002 7.00 F
> > 17-Sep-2002 6.00 S
> > 08-Mar-1981 5.00 W
> > 23-Apr-1984 4.00 M
> >
> > From this list which is constantly updated with new information I wish
to
> > extract rows of information for the Maximum and Minimum value in each
> month
> > regardless of year and display in a table as follows.
> >
> > Max Min
> > Date Value Location Date Value
> Location
> > JAN
> > FEB
> > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00 J
> > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> > SEP
> > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > DEC
> >
> > I have been thinking how I can do this for a few days now without any
> > solution coming to mind. I hope there is an easy solution. So if anybody
> can
> > point me in the right direction it would be most appreciated.
> >
> > Thanks
> > --
> > John Bedford
> > Delete extra @ to reply.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>

[Ken Wright]

Did you array enter the formula using CTRL+SHIFT+ENTER?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:O%[email protected]...
> I think I must be doing something wrong. I applied your first formula to
the
> example data I supplied for each of the 12 months and got the following
> results displayed.
>
> Jan 0.00, Feb 0.00, Mar 0.00, Apr 0.00, May 20.00, Jun 0.00, Jul 0.00, Aug
> 20.00, Sep 0.00, Oct 20.00, Nov 0.00, Dec 0.00
>
> However, if I click on the = sign on the formula bar the results shown
are
> correct.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > John,
> >
> > This gets the max value for say March
> >
> > =MAX(IF(MONTH(A2:A20)=3,B2:B20))
> >
> > Then use that value to get the date and location
> >
> > =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> >
> > and
> >
> >
> > =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > news:[email protected]...
> > > I have a spreadsheet part of which shows a list of dates, values and
> > > Locations ( in three columns) an extract of which is shown below.
> > >
> > > DATE VALUE LOCATION
> > > 25-Jun-2004 20.00 A
> > > 16-Jun-2004 19.00 D
> > > 08-Oct-2004 18.00 G
> > > 23-Aug-2002 17.00 E
> > > 26-Nov-1983 16.00 NK
> > > 03-May-1983 15.00 R
> > > 31-Aug-2002 14.00 NK
> > > 16-Aug-2002 13.00 P
> > > 16-Aug-2002 12.00 G
> > > 06-Jul-2002 11.00 J
> > > 27-Oct-1984 10.00 K
> > > 01-Jun-2003 9.00 P
> > > 04-Oct-2002 8.00 Z
> > > 30-Aug-2002 7.00 F
> > > 17-Sep-2002 6.00 S
> > > 08-Mar-1981 5.00 W
> > > 23-Apr-1984 4.00 M
> > >
> > > From this list which is constantly updated with new information I wish
> to
> > > extract rows of information for the Maximum and Minimum value in each
> > month
> > > regardless of year and display in a table as follows.
> > >
> > > Max Min
> > > Date Value Location Date Value
> > Location
> > > JAN
> > > FEB
> > > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> > > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> > > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00
J
> > > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> > > SEP
> > > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> > > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > > DEC
> > >
> > > I have been thinking how I can do this for a few days now without any
> > > solution coming to mind. I hope there is an easy solution. So if
anybody
> > can
> > > point me in the right direction it would be most appreciated.
> > >
> > > Thanks
> > > --
> > > John Bedford
> > > Delete extra @ to reply.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

[CLR]

My approach to this would be to copy the list to Sheets 2 and 3 and install
a helper column on each with the formula =MONTH(b2) in say D2 and copy down
and sort on this column..........then do Data > Subtotals > For each change
in: Month > Use function: Max (on sheet 2)or Min (on sheet 3) > Add subtotal
to: Value...........this would give a table on sheet 2 for your Max
values/month, and a table on sheet 3 for your Min values/month........the
whole thing could be recorded into a macro and run whenever your data
changes..........

Sub MaxMinTables()
Range("A1:C18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "month"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-3])"
Selection.AutoFill Destination:=Range("D2:D18")
Range("D2:D18").Select
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "month"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-3])"
Selection.AutoFill Destination:=Range("D2:D18")
Range("D2:D18").Select
Range("A2").Select
Selection.Subtotal GroupBy:=4, Function:=xlMax, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Sheets("Sheet3").Select
Range("A2").Select
Selection.Subtotal GroupBy:=4, Function:=xlMin, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Vaya con Dios,
Chuck, CABGx3


"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:[email protected]...
> I have a spreadsheet part of which shows a list of dates, values and
> Locations ( in three columns) an extract of which is shown below.
>
> DATE VALUE LOCATION
> 25-Jun-2004 20.00 A
> 16-Jun-2004 19.00 D
> 08-Oct-2004 18.00 G
> 23-Aug-2002 17.00 E
> 26-Nov-1983 16.00 NK
> 03-May-1983 15.00 R
> 31-Aug-2002 14.00 NK
> 16-Aug-2002 13.00 P
> 16-Aug-2002 12.00 G
> 06-Jul-2002 11.00 J
> 27-Oct-1984 10.00 K
> 01-Jun-2003 9.00 P
> 04-Oct-2002 8.00 Z
> 30-Aug-2002 7.00 F
> 17-Sep-2002 6.00 S
> 08-Mar-1981 5.00 W
> 23-Apr-1984 4.00 M
>
> From this list which is constantly updated with new information I wish to
> extract rows of information for the Maximum and Minimum value in each
month
> regardless of year and display in a table as follows.
>
> Max Min
> Date Value Location Date Value
Location
> JAN
> FEB
> MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00 J
> AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> SEP
> OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> DEC
>
> I have been thinking how I can do this for a few days now without any
> solution coming to mind. I hope there is an easy solution. So if anybody
can
> point me in the right direction it would be most appreciated.
>
> Thanks
> --
> John Bedford
> Delete extra @ to reply.
>
>
>
>
>
>
>
>
>
>
>

[john.bedford3]

No I did not. I have not used this type of formula before so only used
ENTER. I have re-entered them now and they work.

Thanks


--
John Bedford
Delete extra @ to reply.
"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> Did you array enter the formula using CTRL+SHIFT+ENTER?
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:O%[email protected]...
> > I think I must be doing something wrong. I applied your first formula to
> the
> > example data I supplied for each of the 12 months and got the following
> > results displayed.
> >
> > Jan 0.00, Feb 0.00, Mar 0.00, Apr 0.00, May 20.00, Jun 0.00, Jul 0.00,
Aug
> > 20.00, Sep 0.00, Oct 20.00, Nov 0.00, Dec 0.00
> >
> > However, if I click on the = sign on the formula bar the results shown
> are
> > correct.
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > John,
> > >
> > > This gets the max value for say March
> > >
> > > =MAX(IF(MONTH(A2:A20)=3,B2:B20))
> > >
> > > Then use that value to get the date and location
> > >
> > > =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > >
> > > and
> > >
> > >
> > > =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > > news:[email protected]...
> > > > I have a spreadsheet part of which shows a list of dates, values and
> > > > Locations ( in three columns) an extract of which is shown below.
> > > >
> > > > DATE VALUE LOCATION
> > > > 25-Jun-2004 20.00 A
> > > > 16-Jun-2004 19.00 D
> > > > 08-Oct-2004 18.00 G
> > > > 23-Aug-2002 17.00 E
> > > > 26-Nov-1983 16.00 NK
> > > > 03-May-1983 15.00 R
> > > > 31-Aug-2002 14.00 NK
> > > > 16-Aug-2002 13.00 P
> > > > 16-Aug-2002 12.00 G
> > > > 06-Jul-2002 11.00 J
> > > > 27-Oct-1984 10.00 K
> > > > 01-Jun-2003 9.00 P
> > > > 04-Oct-2002 8.00 Z
> > > > 30-Aug-2002 7.00 F
> > > > 17-Sep-2002 6.00 S
> > > > 08-Mar-1981 5.00 W
> > > > 23-Apr-1984 4.00 M
> > > >
> > > > From this list which is constantly updated with new information I
wish
> > to
> > > > extract rows of information for the Maximum and Minimum value in
each
> > > month
> > > > regardless of year and display in a table as follows.
> > > >
> > > > Max Min
> > > > Date Value Location Date Value
> > > Location
> > > > JAN
> > > > FEB
> > > > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > > > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> > > > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > > > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> > > > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00
> J
> > > > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> > > > SEP
> > > > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> > > > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > > > DEC
> > > >
> > > > I have been thinking how I can do this for a few days now without
any
> > > > solution coming to mind. I hope there is an easy solution. So if
> anybody
> > > can
> > > > point me in the right direction it would be most appreciated.
> > > >
> > > > Thanks
> > > > --
> > > > John Bedford
> > > > Delete extra @ to reply.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Bob Phillips]

Sorry, that was my omission.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
news:[email protected]...
> No I did not. I have not used this type of formula before so only used
> ENTER. I have re-entered them now and they work.
>
> Thanks
>
>
> --
> John Bedford
> Delete extra @ to reply.
> "Ken Wright" <[email protected]> wrote in message
> news:%[email protected]...
> > Did you array enter the formula using CTRL+SHIFT+ENTER?
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
> --
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
> --
> >
> > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > news:O%[email protected]...
> > > I think I must be doing something wrong. I applied your first formula
to
> > the
> > > example data I supplied for each of the 12 months and got the
following
> > > results displayed.
> > >
> > > Jan 0.00, Feb 0.00, Mar 0.00, Apr 0.00, May 20.00, Jun 0.00, Jul 0.00,
> Aug
> > > 20.00, Sep 0.00, Oct 20.00, Nov 0.00, Dec 0.00
> > >
> > > However, if I click on the = sign on the formula bar the results
shown
> > are
> > > correct.
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > John,
> > > >
> > > > This gets the max value for say March
> > > >
> > > > =MAX(IF(MONTH(A2:A20)=3,B2:B20))
> > > >
> > > > Then use that value to get the date and location
> > > >
> > > > =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > > >
> > > > and
> > > >
> > > >
> > > > =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > > > news:[email protected]...
> > > > > I have a spreadsheet part of which shows a list of dates, values
and
> > > > > Locations ( in three columns) an extract of which is shown below.
> > > > >
> > > > > DATE VALUE LOCATION
> > > > > 25-Jun-2004 20.00 A
> > > > > 16-Jun-2004 19.00 D
> > > > > 08-Oct-2004 18.00 G
> > > > > 23-Aug-2002 17.00 E
> > > > > 26-Nov-1983 16.00 NK
> > > > > 03-May-1983 15.00 R
> > > > > 31-Aug-2002 14.00 NK
> > > > > 16-Aug-2002 13.00 P
> > > > > 16-Aug-2002 12.00 G
> > > > > 06-Jul-2002 11.00 J
> > > > > 27-Oct-1984 10.00 K
> > > > > 01-Jun-2003 9.00 P
> > > > > 04-Oct-2002 8.00 Z
> > > > > 30-Aug-2002 7.00 F
> > > > > 17-Sep-2002 6.00 S
> > > > > 08-Mar-1981 5.00 W
> > > > > 23-Apr-1984 4.00 M
> > > > >
> > > > > From this list which is constantly updated with new information I
> wish
> > > to
> > > > > extract rows of information for the Maximum and Minimum value in
> each
> > > > month
> > > > > regardless of year and display in a table as follows.
> > > > >
> > > > > Max Min
> > > > > Date Value Location Date
Value
> > > > Location
> > > > > JAN
> > > > > FEB
> > > > > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > > > > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> > > > > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > > > > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00
P
> > > > > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00
> > J
> > > > > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> > > > > SEP
> > > > > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> > > > > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > > > > DEC
> > > > >
> > > > > I have been thinking how I can do this for a few days now without
> any
> > > > > solution coming to mind. I hope there is an easy solution. So if
> > anybody
> > > > can
> > > > > point me in the right direction it would be most appreciated.
> > > > >
> > > > > Thanks
> > > > > --
> > > > > John Bedford
> > > > > Delete extra @ to reply.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[john.bedford3]

Thanks, that may be a problem for me I will have to think about this again.

John B

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Given that this is to be updated constantly, note that Bob's excellent
> solution is highly dependent your values being unique. For instance it
> won't work if the max entry of a month has the same value as a previous
> month, e.g.,
>
> with
>
> 25-Jun-2004 20.00 A
> 16-Jun-2004 19.00 D
> 08-Oct-2004 19.00 G
>
> Then your October Max Date:
>
> =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=10,B2:B20)),B2:B20,0))
>
> will return
>
> 16-Jun-04
>
>
>
> In article <[email protected]>,
> "Bob Phillips" <[email protected]> wrote:
>
> > This gets the max value for say March
> >
> > =MAX(IF(MONTH(A2:A20)=3,B2:B20))
> >
> > Then use that value to get the date and location
> >
> > =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> >
> > and
> >
> >
> > =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))

[john.bedford3]

That's OK Bob. I have still learnt something new.
Thanks for all your help.

"Bob Phillips" <[email protected]> wrote in message
news:e%[email protected]...
> Sorry, that was my omission.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:[email protected]...
> > No I did not. I have not used this type of formula before so only used
> > ENTER. I have re-entered them now and they work.
> >
> > Thanks
> >
> >
> > --
> > John Bedford
> > Delete extra @ to reply.
> > "Ken Wright" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Did you array enter the formula using CTRL+SHIFT+ENTER?
> > >
> > > --
> > > Regards
> > > Ken....................... Microsoft MVP - Excel
> > > Sys Spec - Win XP Pro / XL 97/00/02/03
> > >
> >
>
> --------------------------------------------------------------------------
> > --
> > > It's easier to beg forgiveness than ask permission
:-)
> >
>
> --------------------------------------------------------------------------
> > --
> > >
> > > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > > news:O%[email protected]...
> > > > I think I must be doing something wrong. I applied your first
formula
> to
> > > the
> > > > example data I supplied for each of the 12 months and got the
> following
> > > > results displayed.
> > > >
> > > > Jan 0.00, Feb 0.00, Mar 0.00, Apr 0.00, May 20.00, Jun 0.00, Jul
0.00,
> > Aug
> > > > 20.00, Sep 0.00, Oct 20.00, Nov 0.00, Dec 0.00
> > > >
> > > > However, if I click on the = sign on the formula bar the results
> shown
> > > are
> > > > correct.
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > John,
> > > > >
> > > > > This gets the max value for say March
> > > > >
> > > > > =MAX(IF(MONTH(A2:A20)=3,B2:B20))
> > > > >
> > > > > Then use that value to get the date and location
> > > > >
> > > > > =INDEX(A2:A20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > > > >
> > > > > and
> > > > >
> > > > >
> > > > > =INDEX(C2:C20,MATCH(MAX(IF(MONTH(A2:A20)=3,B2:B20)),B2:B20,0))
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> > > > > news:[email protected]...
> > > > > > I have a spreadsheet part of which shows a list of dates, values
> and
> > > > > > Locations ( in three columns) an extract of which is shown
below.
> > > > > >
> > > > > > DATE VALUE LOCATION
> > > > > > 25-Jun-2004 20.00 A
> > > > > > 16-Jun-2004 19.00 D
> > > > > > 08-Oct-2004 18.00 G
> > > > > > 23-Aug-2002 17.00 E
> > > > > > 26-Nov-1983 16.00 NK
> > > > > > 03-May-1983 15.00 R
> > > > > > 31-Aug-2002 14.00 NK
> > > > > > 16-Aug-2002 13.00 P
> > > > > > 16-Aug-2002 12.00 G
> > > > > > 06-Jul-2002 11.00 J
> > > > > > 27-Oct-1984 10.00 K
> > > > > > 01-Jun-2003 9.00 P
> > > > > > 04-Oct-2002 8.00 Z
> > > > > > 30-Aug-2002 7.00 F
> > > > > > 17-Sep-2002 6.00 S
> > > > > > 08-Mar-1981 5.00 W
> > > > > > 23-Apr-1984 4.00 M
> > > > > >
> > > > > > From this list which is constantly updated with new information
I
> > wish
> > > > to
> > > > > > extract rows of information for the Maximum and Minimum value in
> > each
> > > > > month
> > > > > > regardless of year and display in a table as follows.
> > > > > >
> > > > > > Max Min
> > > > > > Date Value Location Date
> Value
> > > > > Location
> > > > > > JAN
> > > > > > FEB
> > > > > > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > > > > > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00
M
> > > > > > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > > > > > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00
> P
> > > > > > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00
> > > J
> > > > > > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00
F
> > > > > > SEP
> > > > > > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00
Z
> > > > > > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > > > > > DEC
> > > > > >
> > > > > > I have been thinking how I can do this for a few days now
without
> > any
> > > > > > solution coming to mind. I hope there is an easy solution. So if
> > > anybody
> > > > > can
> > > > > > point me in the right direction it would be most appreciated.
> > > > > >
> > > > > > Thanks
> > > > > > --
> > > > > > John Bedford
> > > > > > Delete extra @ to reply.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[john.bedford3]

Thanks, I will give this a try.

John B

"CLR" <[email protected]> wrote in message
news:e9c54%[email protected]...
> My approach to this would be to copy the list to Sheets 2 and 3 and
install
> a helper column on each with the formula =MONTH(b2) in say D2 and copy
down
> and sort on this column..........then do Data > Subtotals > For each
change
> in: Month > Use function: Max (on sheet 2)or Min (on sheet 3) > Add
subtotal
> to: Value...........this would give a table on sheet 2 for your Max
> values/month, and a table on sheet 3 for your Min values/month........the
> whole thing could be recorded into a macro and run whenever your data
> changes..........
>
> Sub MaxMinTables()
> Range("A1:C18").Select
> Selection.Copy
> Sheets("Sheet2").Select
> Range("A1").Select
> ActiveSheet.Paste
> Sheets("Sheet3").Select
> Range("A1").Select
> ActiveSheet.Paste
> Range("D1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "month"
> Range("D2").Select
> ActiveCell.FormulaR1C1 = "=MONTH(RC[-3])"
> Selection.AutoFill Destination:=Range("D2:D18")
> Range("D2:D18").Select
> Sheets("Sheet2").Select
> Range("D1").Select
> ActiveCell.FormulaR1C1 = "month"
> Range("D2").Select
> ActiveCell.FormulaR1C1 = "=MONTH(RC[-3])"
> Selection.AutoFill Destination:=Range("D2:D18")
> Range("D2:D18").Select
> Range("A2").Select
> Selection.Subtotal GroupBy:=4, Function:=xlMax, TotalList:=Array(2), _
> Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> Sheets("Sheet3").Select
> Range("A2").Select
> Selection.Subtotal GroupBy:=4, Function:=xlMin, TotalList:=Array(2), _
> Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> End Sub
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:[email protected]...
> > I have a spreadsheet part of which shows a list of dates, values and
> > Locations ( in three columns) an extract of which is shown below.
> >
> > DATE VALUE LOCATION
> > 25-Jun-2004 20.00 A
> > 16-Jun-2004 19.00 D
> > 08-Oct-2004 18.00 G
> > 23-Aug-2002 17.00 E
> > 26-Nov-1983 16.00 NK
> > 03-May-1983 15.00 R
> > 31-Aug-2002 14.00 NK
> > 16-Aug-2002 13.00 P
> > 16-Aug-2002 12.00 G
> > 06-Jul-2002 11.00 J
> > 27-Oct-1984 10.00 K
> > 01-Jun-2003 9.00 P
> > 04-Oct-2002 8.00 Z
> > 30-Aug-2002 7.00 F
> > 17-Sep-2002 6.00 S
> > 08-Mar-1981 5.00 W
> > 23-Apr-1984 4.00 M
> >
> > From this list which is constantly updated with new information I wish
to
> > extract rows of information for the Maximum and Minimum value in each
> month
> > regardless of year and display in a table as follows.
> >
> > Max Min
> > Date Value Location Date Value
> Location
> > JAN
> > FEB
> > MAR 08-Mar-1981 5.00 W 08-Mar-1981 5.00 W
> > APR 23-Apr-1984 4.00 M 23-Apr-1984 4.00 M
> > MAY 03-May-1983 15.00 R 03-May-1983 15.00 R
> > JUNE 25-Jun-2004 20.00 A 01-Jun-2003 9.00 P
> > JUL 06-Jul-2002 11.00 J 06-Jul-2002 11.00 J
> > AUG 23-Aug-2002 17.00 E 30-Aug-2002 7.00 F
> > SEP
> > OCT 08-Oct-2004 18.00 G 04-Oct-2002 8.00 Z
> > NOV 26-Nov-1983 16.00 NK 26-Nov-1983 16.00 NK
> > DEC
> >
> > I have been thinking how I can do this for a few days now without any
> > solution coming to mind. I hope there is an easy solution. So if anybody
> can
> > point me in the right direction it would be most appreciated.
> >
> > Thanks
> > --
> > John Bedford
> > Delete extra @ to reply.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>

[duane]

I will re offer my solution from above

I think this does it. I put your data in cells a2:c18, and listed months (as dates) in cells a21:a32

b21:b32 are dates for month mins, c21:c32 are min for the month, and d21:d32 are the locations for the min
b21:b32 are dates for month maxes, c21:c32 are max for the month, and d21:d32 are the locations for the max

b21:
=SUMPRODUCT((MONTH($A21)=MONTH($A$2:$A$18))*(C21=$ B$2:$B$18)*($A$2:$A$18))

c21:
=MIN(IF(MONTH($A$2:$A$18)=MONTH($A21),$B$2:$B$18))

(this is an array formula - control/shift/enter)


d21:
=IF(B21=0,"",VLOOKUP(B21,$A$2:$C$18,3,FALSE))

e21:
=SUMPRODUCT((MONTH($A21)=MONTH($A$2:$A$18))*(F21=$ B$2:$B$18)*($A$2:$A$18))

f21:
=MAX(IF(MONTH($A$2:$A$18)=MONTH($A21),$B$2:$B$18))

(this is an array formula - control/shift/enter)

g21:

=IF(E21=0,"",VLOOKUP(E21,$A$2:$C$18,3,FALSE))

copy these formula down through row 32


I also suppressed zero values on the sheet (tool, option, view)
as if there is no data for a month (such as Jan) the formulas result in zero