# Using MIN function in column with blanks

Show 40 post(s) from this thread on one page
Page 2 of 3 First 123 Last
• Sep 4th, 2019, 02:41 PM
oldpup223
Re: Using MIN function in column with blanks
the cells actually have the formula of =MIN(E99:I99) or corresponding to that particular row
• Sep 4th, 2019, 02:45 PM
Fluff
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
• Sep 4th, 2019, 04:50 PM
oldpup223
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
• Sep 4th, 2019, 04:59 PM
oldpup223
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
• Sep 4th, 2019, 05:02 PM
oldpup223
Re: Using MIN function in column with blanks
sorry but approaching 80 I find I am not as sharp as I once was
• Sep 5th, 2019, 03:25 AM
Yongle
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
• Sep 6th, 2019, 12:26 PM
oldpup223
Re: Using MIN function in column with blanks

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
• Sep 6th, 2019, 12:55 PM
Fluff
Re: Using MIN function in column with blanks