+ Reply to Thread
Results 1 to 8 of 8

Logical_test on Dates to get date_wise MIN and MAX values

  1. #1
    Registered User
    Join Date
    04-30-2016
    Location
    Islamabad, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Logical_test on Dates to get date_wise MIN and MAX values

    NEED HE
    I want to find the MIN and MAX from column 'values' with respect to dates.

    Data is in below format:
    Dates (several months; daily_basis) | Time (minute by minute) | Values (data)

    Required Result format:
    Date-1 (e.g. 02-Apr-16) | MIN_value | MAX_value
    Date-2 (e.g. 03-Apr-16) | MIN_value | MAX_value
    Date-3 (e.g. 03-Apr-16) | MIN_value | MAX_value
    .
    .
    .
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,845

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    with a pivot table.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,496

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    Put this array* formula in B2 of the Summary sheet:

    =MIN(IF(Date_wise_MIN_MAX!$A$2:$A$28516=A2,Date_wise_MIN_MAX!$C$2:$C$28516))

    and this array* formula in C2:

    =MAX(IF(Date_wise_MIN_MAX!$A$2:$A$28516=A2,Date_wise_MIN_MAX!$C$2:$C$28516))

    then copy down.

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

    Hope this helps.

    Pete

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    =MIN(IF(Date_wise_MIN_MAX!$A$2:$A$29000=Summary!$A2,Date_wise_MIN_MAX!$C$2:$C$29000))


    =MAX(IF(Date_wise_MIN_MAX!$A$2:$A$29000=Summary!$A2,Date_wise_MIN_MAX!$C$2:$C$29000))

    Enter both with Ctrl+shift+Enter

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,845

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    Please read the forumrules, especialy the ones about crossposting.

    http://www.mrexcel.com/forum/excel-q...ax-values.html

  6. #6
    Registered User
    Join Date
    04-30-2016
    Location
    Islamabad, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    Thanks alot for your help!

  7. #7
    Registered User
    Join Date
    04-30-2016
    Location
    Islamabad, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    Thanks alot for your help!!!

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,845

    Re: Logical_test on Dates to get date_wise MIN and MAX values

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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