Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Using MIN function in column with blanks
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2012
    Location
    Muncie, IN
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using MIN function in column with blanks

    the cells actually have the formula of =MIN(E99:I99) or corresponding to that particular row

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using MIN function in column with blanks

    In that case if E99:I99 is blank the formula will return a 0 in L99.
    You obviously have the sheet set to hide 0s, try the formula that @Yongle suggested in post#2
    Last edited by Fluff; Sep 4th, 2019 at 02:45 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Jul 2012
    Location
    Muncie, IN
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using MIN function in column with blanks

    they all have the formula of =MIN(E20:I20) or a value corresponding to the row and I have found if I take the formula out of the blank cells it will work but will not work if I leave one cell with it

  4. #14
    Board Regular
    Join Date
    Jul 2012
    Location
    Muncie, IN
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using MIN function in column with blanks

    no my cells do have the =min(e5:I5) statement in them corresponding to that row and I have found that deleting all formulas in all blank cells it will work but will not if I have one cell with the =min() statement in it

  5. #15
    Board Regular
    Join Date
    Jul 2012
    Location
    Muncie, IN
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using MIN function in column with blanks

    sorry but approaching 80 I find I am not as sharp as I once was

  6. #16
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using MIN function in column with blanks

    I tested various scenarios

    ALL cells E5:I5 blank
    =MIN(E5:I5) returns ZERO

    ALL cells E5:I5 return an EMPTY string (formula ="" placed in all 5 cells)
    =MIN(E5:I5) returns ZERO

    ONE or more cells in E5:I5 contains a NUMBER
    =MIN(E5:I5) returns the lowest number


    Try this formula instead of =MIN(E5:I5)
    =IF(SUM(E5:I5)>0,MIN(E5:I5),"")

    Which returns complete row of EMPTY STRINGS as an empty string but returns a row that contains a genuine zero as zero

    If that does not work for you then your data must contain one or more zero that you are not aware of that needs to be unmasked

    The worksheet below should help you understand how to spot where the problem is
    The TYPE of value is in each cell is critical
    Look at column E then column J then column L then column O

    Excel 2016 (Windows) 32 bit
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    4
    COMMENT Formula in L value TYPE
    in column E
    formula in O
    5
    XXX
    16
    16
    48
    25
    text in E5
    16
    =IF(SUM(E5:I5)>0,MIN(E5:I5),"") l =CELL("type",E5)
    6
    blank cell =IF(SUM(E6:I6)>0,MIN(E6:I6),"") b =CELL("type",E6)
    7
    empty strings =IF(SUM(E7:I7)>0,MIN(E7:I7),"") l =CELL("type",E7)
    8
    38
    4 blank cells
    38
    =IF(SUM(E8:I8)>0,MIN(E8:I8),"") b =CELL("type",E8)
    9
    3
    57
    69
    25
    78
    3
    =IF(SUM(E9:I9)>0,MIN(E9:I9),"") v =CELL("type",E9)
    10
    38
    25
    17
    5
    E10 is empty string
    5
    =IF(SUM(E10:I10)>0,MIN(E10:I10),"") l =CELL("type",E10)
    11
    77
    89
    60
    15
    E11 is suppressed ZERO
    0
    =IF(SUM(E11:I11)>0,MIN(E11:I11),"") v =CELL("type",E11)
    12
    0
    34
    98
    45
    35
    E12 is ZERO
    0
    =IF(SUM(E12:I12)>0,MIN(E12:I12),"") v =CELL("type",E12)
    13
    55
    89
    96
    48
    56
    48
    =IF(SUM(E13:I13)>0,MIN(E13:I13),"") v =CELL("type",E13)
    14
    5
    5
    76
    50
    24
    5
    =IF(SUM(E14:I14)>0,MIN(E14:I14),"") v =CELL("type",E14)
    15
    5
    59
    97
    82
    55
    5
    =IF(SUM(E15:I15)>0,MIN(E15:I15),"") v =CELL("type",E15)
    16
    0
    53
    69
    85
    93
    0
    =IF(SUM(E16:I16)>0,MIN(E16:I16),"") v =CELL("type",E16)
    Sheet: Min

  7. #17
    Board Regular
    Join Date
    Jul 2012
    Location
    Muncie, IN
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using MIN function in column with blanks

    ABCDEFGHIJKLMN
    1CM HD223V1.133.2711.2831.5462.5393.25711.8962.3791.283
    2BE BB6PPCU1.140.8510.9870.3420.7560.5213.4570.6910.342
    3BE RR223V1.154.9253.1682.0741.2201.94113.3282.6661.220
    4DN BB223AR1.162.1081.4482.5132.3062.59510.9702.1941.448
    5MR BL22V1.171.0251.0481.0520.7731.0344.9320.9860.773
    6MS JN6PPCU1.180.8500.6070.9270.6500.4293.4630.6930.429
    7TG VN6PPCU1.190.7640.4801.0390.8270.7063.8160.7630.480
    8=MIN(E8:I8)
    9=MIN(E9:I9)
    10=MIN(E10:I10)
    11=MINIFS(L1:L10,L1:L10,"<>")
    12I would like to display both the min and contents column a of the same rowI want to find Min size of data in column L

    Sheet2



    Worksheet Formulas
    CellFormula
    L1=MIN(E1:I1)
    L2=MIN(E2:I2)
    L3=MIN(E3:I3)
    L4=MIN(E4:I4)
    L5=MIN(E5:I5)
    L6=MIN(E6:I6)
    L7=MIN(E7:I7)



    well I finally got MrExcelHMTL working and this is a section of my spreadsheet that I need help on with both formula and where to figure this ont for myself I do have books on Excel formulas and VBA but just have not been able to find the solution for this altho I think it will have to be an arry

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using MIN function in column with blanks

    How about
    =MINIFS(L1:L10,L1:L10,"<>0")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using MIN function in column with blanks

    What did you find out when you followed up on what was provided in post#16 ?

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using MIN function in column with blanks

    Or if you don't have MINIFS you could use
    =MIN(IF(L1:L10<>0,L1:L10,""))
    This needs to be confirmed with Ctrl Shift Enter, rather than just Enter
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •