+ Reply to Thread
Results 1 to 6 of 6

Getting MAX from one dataset within Date Ranges of another

  1. 10-02-2019, 07:48 PM #1
    jrtaylor
    jrtaylor is offline
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Getting MAX from one dataset within Date Ranges of another

    I've attached a workbook with two sheets. Sheet1 provides a series of irregular date ranges, with one column having an "open" date and the next column showing a "close" date.

    Sheet2 contains daily historical data which covers these date ranges.

    I'm attempting to find out the maximum value in column D of Sheet2, which corresponds to the date ranges seen in Sheet1.

    I've given examples of the desired outcome.

    I originally thought MAXIFS might be the way to go but I can't figure out how to write the conditions.

    Any help? Thanks.

    (PS for purposes of this workbook we can assume a date in Sheet1 refers to the entire date (i.e. day) even though you'll see timestamps showing various times throughout a day.)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-02-2019, 09:23 PM #2
    ikboy
    ikboy is offline
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

    Re: Getting MAX from one dataset within Date Ranges of another

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 10-02-2019, 09:46 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Getting MAX from one dataset within Date Ranges of another

    Are you certain you have MAXIFS? As I understand it is not available in 2016 unless that is Office 365.

    For MAXIFS
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  4. 10-03-2019, 02:15 AM #4
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Getting MAX from one dataset within Date Ranges of another

    (Sorry about my English)

    Please try this formula.

    E3
    =MAX(IF((--(Sheet2!$A:$A>=INT($A3))*--(Sheet2!$A:$A<=INT($B3)+0.99)),Sheet2!$D:$D,0))

    Regards.
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  5. 10-03-2019, 08:40 AM #5
    jrtaylor
    jrtaylor is offline
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Getting MAX from one dataset within Date Ranges of another

    Thank you everyone. I use Office 365 Pro Plus (due to its ability to connect to mysql) so MAXIFS works for me. I appreciate everyone's help.
    Reply With QuoteRegister To Reply
  6. 10-09-2019, 04:12 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Getting MAX from one dataset within Date Ranges of another

    You are welcome. Thank you for the feedback, added rep and marking your thread Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. rearrange database like dataset into table for large dataset better option INDEX MATCH
    By ryanbirk in forum Excel General
    Replies: 3
    Last Post: 10-20-2017, 12:40 AM
  2. Reduce the large dataset into smaller dataset using conditions
    By jdbaba in forum Excel General
    Replies: 3
    Last Post: 01-23-2014, 10:13 AM
  3. [SOLVED] Vertical chart dataset needs to be converted into pivotable dataset
    By aspak84 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-17-2013, 06:06 PM
  4. Copying a dataset multiple times - and selecting titles for this dataset
    By alocke in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2012, 10:15 PM
  5. Sorting a dataset with another using date and time
    By Matt Guernsey in forum Excel General
    Replies: 1
    Last Post: 07-22-2010, 08:29 AM
  6. graphing daily dataset with weekly dataset
    By [email protected] in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-27-2009, 09:39 AM
  7. [SOLVED] Converting A Quarterly Dataset to Weekly Dataset
    By Dan Thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2005, 05:30 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1