+ Reply to Thread
Results 1 to 3 of 3

Return Max Value in a row based on date ranges.

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Return Max Value in a row based on date ranges.

    Hi

    I am having trouble creating a max formula for the attached data.

    What I am looking for is a formula which will return the max number in a row specified by the ID# but between a specific date range. E.g. I want the maximum number in the row for 50418141 between 1st October and 31st October, the formula should return 3626.5

    All help is appreciated. I have spent a full afternoon trying to figure it out but everything I find relates to looking up based on columns rather than rows.

    Thank youHelp.xlsx

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Return Max Value in a row based on date ranges.

    Hi,

    It seems as though you've already put a formula together, however I suspect you're wanting a more generic solution. See if the attached helps.

    One caveat, you need to remove all the '-' cell values since the array formula used does not like non numeric cells.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Return Max Value in a row based on date ranges.

    How about
    =MAX(INDEX(1:1048576,MATCH(A11,A:A,0),MATCH(F1,1:1,0)):INDEX(1:1048576,MATCH(A11,A:A,0),MATCH(L1,1:1,0)))

    Where A11 is the code
    F1 is the start date
    L1 is the end date
    (as in, you can change these addresses to point to somewhere else)
    Last edited by Cheeky Charlie; 04-04-2013 at 12:47 PM. Reason: Make more generic
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return Max Value in a row based on date ranges.

    That's fantastic, worked brilliantly.

    Thank you

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    East Riding of Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Return Max Value in a row based on date ranges.

    That's fantastic, worked brilliantly.

    Thank you

+ 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