+ Reply to Thread
Results 1 to 5 of 5

find minimum value from range of custom formatted cells

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    find minimum value from range of custom formatted cells

    Hello

    I am trying to find a cell with a smallest value based on custom format data in range of cells

    Custom format looks like 55-XXXXXX/201X

    Is it possible to find smallest value in range of cells if is meet smallest XXXXXX and also 201X criteria.Where 201X is first in priority

    For example 46-000123/2010 is smaller than 55-000122/2011 and also 55-000015/2012 is smaller than 55-000014/2012.

    Also is important to mention that those value is result from array formula (INDEX; SMALL; MATCH; ROW)

    Any idea is highly appericiated

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: find minimum value from range of custom formatted cells

    Can you explain how

    55-000015/2012 is smaller than 55-000014/2012?

    201x criteria is the same, 55 is the same so surely

    55-000014/2012 is smaller than 55-000015/2012?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-26-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: find minimum value from range of custom formatted cells

    Thanks for an answer Special K

    I make a little mistake 55-000015/2012 is greater than 55-000014/2012.

    So what I need is next:

    When I get result from array formula which look like

    55-000014/2014
    55-003314/2012
    55-000014/2012
    55-009999/2011
    55-000114/2013
    55-005613/2012

    I need to make smallest result bold or different formatted from others

    55-000014/2014
    55-003314/2012
    55-000014/2012
    55-009999/2011
    55-000114/2013
    55-005613/2012

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: find minimum value from range of custom formatted cells

    This will get you the result, it's an
    Array formula, use Ctrl-Shift-Enter

    =(MIN(RIGHT(A$1:A$6,4)*1000000+MID(A$1:A$6,4,6))=RIGHT(A1,4)*1000000+MID(A1,4,6))

    Apparently Conditional Format formulas are treated as array formulas so this shouldn't be a problem

  5. #5
    Registered User
    Join Date
    02-26-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: find minimum value from range of custom formatted cells

    Thanks a lot Special-K, I really appreciated your willingness to help

    Seems that solutions a good and interesting.

    I tried Andrew's solution and it works well in case that values are inserted as numbers and in a case there's no empty cells in range.

    In my case a cell values are result from array formula and are in range from A10 to G40 where column B contain values I got from array formula in range B10: B40
    in many cases there's no more than two results in range (other cells in range are hidden from view with conditional formatting (ISERROR formula,(because is #NUM! inside) and formatted as white text).

    So if formula is used in filled range with real values it works great, but not in case there's formula result and not all cells in range B10 : B40 are with result.

    I do really hope you'll understand.

+ 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. Find first non formatted cell in range
    By Burt_100 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-04-2014, 04:45 PM
  2. [SOLVED] Find the minimum value from a range in multiple columns
    By newbi004 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-14-2013, 12:13 PM
  3. Find the minimum value in a range, WITH conditions
    By Phil_V in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-30-2009, 09:13 AM
  4. find minimum value for each range of numbers in a table
    By Val H in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2008, 10:23 AM
  5. Find minimum in range that has blanks and zeros
    By supermario77 in forum Excel General
    Replies: 1
    Last Post: 04-21-2005, 06:19 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