+ Reply to Thread
Results 1 to 11 of 11

MIN with criteria on range including errors

  1. #1
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    MIN with criteria on range including errors

    Hi,

    suppose my range in cells A1:A4 is 0,1,2,#DIV/0!

    I would like to avoid a solution involving AGGREGATE (or SMALL(IF())- or MINIFS())
    Apart from errors also zeroes should be excluded (or whatever other criteria added).

    What would a solution involving MIN look like since =MIN(IF(ISNUMBER(A1:D1)*(A1:D1>0),A1:D1)) doesn't work and I am looking for a fairly sleek solution without unecessarily bloating the formula.

    I also want to avoid nested IF-statements within the MIN-function ( e.g.: =MIN(IF(ISNUMBER(A1:D1),IF(A1:D1>0,A1:D1))) ) because the number of conditions can become quite long and I would like to keep the formula as readable as possible.

    Thanks
    Last edited by RaulSerg; 05-30-2020 at 12:47 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: MIN with criteria on range including errors

    Maybe that:

    =MIN(IFERROR({0,1,2,#DIV/0!},0))

  3. #3
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: MIN with criteria on range including errors

    This yields zero but I want the MIN value bigger than zero.

    I will amend above as it didn't come out clearly enough :\

    Cheers

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: MIN with criteria on range including errors

    =min(iferror({1,2,#div/0!},999^999))

  5. #5
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: MIN with criteria on range including errors

    I get a #NUM! error from that formula?

    I played around with IFERROR a little, =MIN(IFERROR(IF(A1:D1>0,A1:D1),"")) seems to work.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MIN with criteria on range including errors

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: MIN with criteria on range including errors

    I took a number that is too large (999^999)


    It works for me with 9^99 , but perhaps you will need to set it with CSE (CTRL+SHIFT+ENTER)

    =MIN(IFERROR(A1:D1,9^99))
    Last edited by Limor_OP; 05-30-2020 at 12:46 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: MIN with criteria on range including errors

    Hmm, that still gives me 0 as result, belinda200?

    =MIN(IFERROR(1/(1/A1:D1),"")) through some more playing around seems still a slightly shorter solution.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: MIN with criteria on range including errors

    look at C4 in the attached

    =MIN(IFERROR(A4:A7,9^99))
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: MIN with criteria on range including errors

    Yep, but if you include a zero, the formula fails. At least under the condition that I want the resulting minimum to be greater than zero, i.e. ignore zeros.

    Regards

  11. #11
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: MIN with criteria on range including errors

    I see, I didnt read your criteria regarding the zero elimination....then your fotmula seems perfect now.
    Last edited by Limor_OP; 05-30-2020 at 01:06 PM.

+ 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: 4
    Last Post: 09-04-2019, 05:29 AM
  2. [SOLVED] Including values in range if it meets two criteria.
    By Rasmusvwn in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-03-2016, 03:34 PM
  3. [SOLVED] Count based on 3 criteria including value range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2015, 10:43 PM
  4. [SOLVED] Using Index/Match using criteria including range between two cells
    By fer907 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2013, 04:07 PM
  5. [SOLVED] Count based on 3 criteria including date range
    By timarcarze in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-13-2013, 01:43 AM
  6. Replies: 5
    Last Post: 05-23-2011, 12:04 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