Using MIN function in column with blanks

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
the cells actually have the formula of =MIN(E99:I99) or corresponding to that particular row
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
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:

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
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
 

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
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
 

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
sorry but approaching 80 I find I am not as sharp as I once was
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,336
Office Version
365
Platform
Windows
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 Lvalue 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
 

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">CM HD</td><td style="font-weight: bold;text-align: center;;">223</td><td style="font-weight: bold;text-align: center;;">V</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.13</td><td style="font-weight: bold;text-align: center;color: #008000;;">3.271</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.283</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.546</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.539</td><td style="font-weight: bold;text-align: center;color: #008000;;">3.257</td><td style="font-weight: bold;text-align: center;;">11.896</td><td style="font-weight: bold;text-align: center;;">2.379</td><td style="text-align: right;;">1.283</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">BE BB</td><td style="font-weight: bold;text-align: center;;">6PPC</td><td style="font-weight: bold;text-align: center;;">U</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.14</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.851</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.987</td><td style="font-weight: bold;text-align: center;color: #008000;background-color: #FFFF00;;">0.342</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.756</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.521</td><td style="font-weight: bold;text-align: center;;">3.457</td><td style="font-weight: bold;text-align: center;;">0.691</td><td style="text-align: right;background-color: #FFFF00;;">0.342</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">BE RR</td><td style="font-weight: bold;text-align: center;;">223</td><td style="font-weight: bold;text-align: center;;">V</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.15</td><td style="font-weight: bold;text-align: center;color: #008000;;">4.925</td><td style="font-weight: bold;text-align: center;color: #008000;;">3.168</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.074</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.220</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.941</td><td style="font-weight: bold;text-align: center;;">13.328</td><td style="font-weight: bold;text-align: center;;">2.666</td><td style="text-align: right;;">1.220</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">DN BB</td><td style="font-weight: bold;text-align: center;;">223</td><td style="font-weight: bold;text-align: center;;">AR</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.16</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.108</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.448</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.513</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.306</td><td style="font-weight: bold;text-align: center;color: #008000;;">2.595</td><td style="font-weight: bold;text-align: center;;">10.970</td><td style="font-weight: bold;text-align: center;;">2.194</td><td style="text-align: right;;">1.448</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">MR BL</td><td style="font-weight: bold;text-align: center;;">22</td><td style="font-weight: bold;text-align: center;;">V</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.17</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.025</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.048</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.052</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.773</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.034</td><td style="font-weight: bold;text-align: center;;">4.932</td><td style="font-weight: bold;text-align: center;;">0.986</td><td style="text-align: right;;">0.773</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">MS JN</td><td style="font-weight: bold;text-align: center;;">6PPC</td><td style="font-weight: bold;text-align: center;;">U</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.18</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.850</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.607</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.927</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.650</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.429</td><td style="font-weight: bold;text-align: center;;">3.463</td><td style="font-weight: bold;text-align: center;;">0.693</td><td style="text-align: right;;">0.429</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">TG VN</td><td style="font-weight: bold;text-align: center;;">6PPC</td><td style="font-weight: bold;text-align: center;;">U</td><td style="font-weight: bold;text-align: center;background-color: #DAEEF3;;">1.19</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.764</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.480</td><td style="font-weight: bold;text-align: center;color: #008000;;">1.039</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.827</td><td style="font-weight: bold;text-align: center;color: #008000;;">0.706</td><td style="font-weight: bold;text-align: center;;">3.816</td><td style="font-weight: bold;text-align: center;;">0.763</td><td style="text-align: right;;">0.480</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">=MIN(E8:I8)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">=MIN(E9:I9)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;;">=MIN(E10:I10)</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;">=MINIFS(L1:L10,L1:L10,"<>")</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">I would like to display both the min and contents column a of the same row</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;">I want to find Min size of data in column L</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L1</th><td style="text-align:left">=MIN(<font color="Blue">E1:I1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=MIN(<font color="Blue">E2:I2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L3</th><td style="text-align:left">=MIN(<font color="Blue">E3:I3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L4</th><td style="text-align:left">=MIN(<font color="Blue">E4:I4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L5</th><td style="text-align:left">=MIN(<font color="Blue">E5:I5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L6</th><td style="text-align:left">=MIN(<font color="Blue">E6:I6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L7</th><td style="text-align:left">=MIN(<font color="Blue">E7:I7</font>)</td></tr></tbody></table></td></tr></table><br />

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
How about
=MINIFS(L1:L10,L1:L10,"<>0")
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,336
Office Version
365
Platform
Windows
What did you find out when you followed up on what was provided in post#16 ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,266
Messages
5,485,763
Members
407,514
Latest member
viego99

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top