Using MIN function in column with blanks

oldpup223

Board Regular
Joined
Jul 31, 2012
Messages
83
I have a scoring sheet that lists scores in a column but never know how many names will be entered and want to find the minimum score in a column with several blanks above the formula
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Book1
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
Cell Formulas
RangeFormula
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
 
Upvote 0
How about
=MINIFS(L1:L10,L1:L10,"<>0")
 
Upvote 0
What did you find out when you followed up on what was provided in post#16 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top