+ Reply to Thread
Results 1 to 7 of 7

Getting the maximum & Minimum value in a range

  1. #1
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Getting the maximum & Minimum value in a range

    Hi:

    I have a series of data (Open - High - Low - Close) based on date.

    I need the maximum & minimum value between certain dates.

    I am attaching an excel sheet explaining what I mean.

    Looking forward to help...

    Thanks & Best Regards,
    Attached Files Attached Files
    Regards,
    Navin Agrawal

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Getting the maximum & Minimum value in a range

    Array formulas
    =MAX(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))
    and
    =MIN(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Adjust ranges accordingly

    EDIT: Changed range to Column C
    Last edited by Ace_XL; 09-13-2013 at 07:09 AM.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Getting the maximum & Minimum value in a range

    Sligthly quicker:
    =MAX(IF($A$2:$A$2041>=A3,IF(G2:$G$2041<=G3,$C$2:$C$2041)))
    =MIN(IF($A$2:$A$2041>=A3,IF(G2:$G$2041<=G3,$C$2:$C$2041)))

    Confirm COntrol+Shift+Enter
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Getting the maximum & Minimum value in a range

    It does not work becoz I am getting the maximum and minimum values in column C & D respectively...

    Quote Originally Posted by Ace_XL View Post
    Array formulas
    =MAX(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))
    and
    =MIN(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$D$2:$D$2041))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Adjust ranges accordingly

  5. #5
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Getting the maximum & Minimum value in a range

    What's your expected result for let say M3 and Q3?

  6. #6
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Getting the maximum & Minimum value in a range

    Apologies to the forum.... There was an error in the formula.... I will be correcting the same and attaching a new file...

  7. #7
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Getting the maximum & Minimum value in a range

    Thanks friends...

    I have managed to resolve the issue with some permutations & combinations....

    Best Regards,

+ 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. [SOLVED] Getting minimum and maximum dates from a variable length range
    By ianbhenderson73 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-13-2013, 12:25 PM
  2. [SOLVED] Get Maximum and Minimum Value from a range of cells that contains certain values
    By jotol in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-28-2013, 10:33 PM
  3. Maximum and Minimum
    By lurchybold in forum Excel General
    Replies: 6
    Last Post: 10-13-2010, 11:04 AM
  4. [SOLVED] Automatic formatting of minimum/maximum value in a range.
    By Manish Kumar in forum Excel General
    Replies: 3
    Last Post: 03-05-2005, 03:06 PM

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