+ Reply to Thread
Results 1 to 5 of 5

Maximum/Minimum value from range and then from cell color

  1. #1
    Registered User
    Join Date
    04-18-2015
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    5

    Thumbs up Maximum/Minimum value from range and then from cell color

    3.6 Formula Range 4.02 3.97 3.93 3.89 3.8 3.68 3.3 3.7 3.69 3.79 3.72 3.72 3.74 3.61 3.58 3.5 3.6 3.5 3.55 3.57


    I want to take a value in Cell "A2" that is less then or equal to the value in cell "A2" from range of "E2:W2"
    The problem is, there are 6 values which are that are less then or equal to 3.6
    I only want to take the value which comes first by using a formula.

    Any one please help me.
    Last edited by mughal1990; 04-19-2015 at 02:47 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,953

    Re: Maximum/Minimum value from range and then from cell color

    welcome to the forum
    =IFERROR(INDEX(E2:W2,MATCH(TRUE,INDEX(E2:W2<=A2,0),0)),"")
    try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,787

    Re: Maximum/Minimum value from range and then from cell color

    Or perhaps this...
    =MIN(A2,INDEX($E$2:$X$2,MATCH(SMALL($E$2:$X$2,1),$E$2:$X$2,0)))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-18-2015
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    5

    Re: Maximum/Minimum value from range and then from cell color

    Quote Originally Posted by nflsales View Post
    welcome to the forum
    =IFERROR(INDEX(E2:W2,MATCH(TRUE,INDEX(E2:W2<=A2,0),0)),"")
    try this
    Nflsales thanxxx

    This worked like a charm can you please also explain me the syntax of this formula how it worked
    Last edited by mughal1990; 04-19-2015 at 02:46 AM.

  5. #5
    Registered User
    Join Date
    04-18-2015
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    5

    Re: Maximum/Minimum value from range and then from cell color

    Quote Originally Posted by FDibbins View Post
    Or perhaps this...
    =MIN(A2,INDEX($E$2:$X$2,MATCH(SMALL($E$2:$X$2,1),$E$2:$X$2,0)))
    Thank you for trying but it did not worked. it is only taking minimum value

+ 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: 2
    Last Post: 10-03-2014, 03:46 AM
  2. Maximum and Minimum Time Value in a Range
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-17-2014, 05:01 PM
  3. Color maximum and Minimum
    By thong127 in forum Excel General
    Replies: 6
    Last Post: 01-09-2014, 11:20 AM
  4. [SOLVED] Getting the maximum & Minimum value in a range
    By Navin Agrawal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-13-2013, 12:39 PM
  5. [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

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