Formula help required please

  • Thread starter Thread starter john.bedford3
  • Start date Start date
J

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,

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)
 
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
 
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.
 
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
 
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
 
Sorry, that was my omission.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
That's OK Bob. I have still learnt something new.
Thanks for all your help.

Bob Phillips said:
Sorry, that was my omission.

--

HTH

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


john.bedford3 said:
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 said:
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
Click to expand...

-------------------------------------------------------------------------- :-)

--------------------------------------------------------------------------
Click to expand...
formula
to 0.00,
Aug I
wish without
any
Click to expand...
Click to expand...
 
Thanks, that may be a problem for me I will have to think about this again.

John B
 
Thanks, I will give this a try.

John B

CLR said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top