+ Reply to Thread
Results 1 to 14 of 14

Find First, Max, Min, Last value of a month of an year

  1. #1
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Find First, Max, Min, Last value of a month of an year

    Column A consists of date (ranging from Jan 2000 - till date)
    Column B consists of value

    Need to find following values of a month of a year
    First
    Max
    Min
    Last

    First/last value can be in somewhere in the middle of the month. Not necessarily be in start/end (date) of a month.

    I want something like this...

    Date | First | Max | Min | Last

    Jan-00 | 100 | 102 | 99 | 101
    Feb-00 | 101 | 103 | 98 | 98
    ...

    =MAX(IF(MONTH($A$1:$A$5)=4,B1:B5))
    I tried this formula for Max value, but it just picks the max value of April (2000 - 2013). It's not finding specific month/year like Apr-13's max value...
    Last edited by TK2013; 08-26-2013 at 07:14 PM.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find First, Max, Min, Last value of a month of an year

    Are the dates sorted in ascending order or are they random?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Date are in ascending order...

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find First, Max, Min, Last value of a month of an year

    Ok, if the dates are sorted in ascending order like this:

    Data Range
    A
    1 Date
    2 1/2/2000
    3 1/5/2000
    4 1/11/2000
    5 1/16/2000
    6 1/18/2000
    7 1/19/2000
    8 1/19/2000
    9 1/19/2000
    10 2/6/2000
    11 2/7/2000
    12 2/9/2000
    13 2/10/2000
    14 2/14/2000
    15 2/14/2000
    16 2/15/2000
    17 2/16/2000
    18 2/20/2000
    19 2/21/2000
    20 2/21/2000

    Then, the first date will also be the min date and the last date will also be the max date for the month/year. Right?

  5. #5
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Yes, it is in ascending order. But the value I am looking to get is not the first/last/min/max date, but the values which are in column B.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find First, Max, Min, Last value of a month of an year

    OK, now I understand. Give me a couple of minutes...

  7. #7
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Find First, Max, Min, Last value of a month of an year

    Hi

    Then try
    =INDEX(B:B,MATCH(MIN(A:A),(A:A)))
    and
    =INDEX(B:B,MATCH(MAX(A:A),(A:A)))
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  8. #8
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Roger,
    That results Max/min value of the whole data. Not the specific month's...

    Tony,
    Sure... Thanks for your time...

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find First, Max, Min, Last value of a month of an year

    Ok, try this...

    Data Range
    A B
    1 Date Value
    2 1/2/2000 9
    3 1/5/2000 73
    4 1/11/2000 48
    5 1/16/2000 58
    6 1/18/2000 21
    7 1/19/2000 31
    8 1/19/2000 12
    9 1/19/2000 41
    10 2/6/2000 22
    11 2/7/2000 4
    12 2/9/2000 10
    13 2/10/2000 87
    14 2/14/2000 4
    15 2/14/2000 17
    16 2/15/2000 70
    17 2/16/2000 72
    18 2/20/2000 8
    19 2/21/2000 81
    20 2/21/2000 53

    Data Range
    D E F G H
    1 Date First Max Min Last
    2 1/1/2000 9 73 9 41
    3 2/1/2000 22 87 4 53

    Enter this array formula** in E2:

    =INDEX(B$2:B$20,MATCH(TRUE,TEXT(A$2:A$20,"mmmyyyy")=TEXT(D2,"mmmyyyy"),0))

    Enter this array formula** in F2:

    =MAX(IF(TEXT(A$2:A$20,"mmmyyyy")=TEXT(D2,"mmmyyyy"),B$2:B$20))

    Enter this array formula** in G2:

    =MIN(IF(TEXT(A$2:A$20,"mmmyyyy")=TEXT(D2,"mmmyyyy"),B$2:B$20))

    Enter this regular formula in H2:

    =LOOKUP(2,1/(TEXT(A$2:A$20,"mmmyyyy")=TEXT(D2,"mmmyyyy")),B$2:B$20)

    Select E2:H2 and copy down as needed.

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  10. #10
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Quote Originally Posted by TK2013 View Post
    Roger,
    That results Max/min value of the whole data. Not the specific month's...
    I mean oldest/latest date's value...

  11. #11
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Tony,
    Ok, I will try it and get back to you...

  12. #12
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Find First, Max, Min, Last value of a month of an year

    Hi

    Apologies
    I missed the part about for the "month".
    Tony's solution will sort you out.

  13. #13
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Find First, Max, Min, Last value of a month of an year

    Yes, it works....
    Thank you Tony.

    Thank you guys for your time. I really appreciate it...
    Solved.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find First, Max, Min, Last value of a month of an year

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  2. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM
  3. Replies: 22
    Last Post: 11-20-2012, 02:44 AM
  4. Find month and year
    By mikke in forum Excel General
    Replies: 4
    Last Post: 11-24-2009, 12:19 PM
  5. [SOLVED] I need to find out the YEAR, MONTH, DAY between two dates
    By jimmy in forum Excel General
    Replies: 2
    Last Post: 07-26-2006, 08:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1