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
the cells actually have the formula of =MIN(E99:I99) or corresponding to that particular row
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 03: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
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
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
sorry but approaching 80 I find I am not as sharp as I once was
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 4COMMENT Formula in L value TYPE
in column Eformula in O 5XXX 16 16 48 25text in E5 16=IF(SUM(E5:I5)>0,MIN(E5:I5),"") l =CELL("type",E5) 6blank cell =IF(SUM(E6:I6)>0,MIN(E6:I6),"") b =CELL("type",E6) 7empty strings =IF(SUM(E7:I7)>0,MIN(E7:I7),"") l =CELL("type",E7) 8 384 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 5E10 is empty string 5=IF(SUM(E10:I10)>0,MIN(E10:I10),"") l =CELL("type",E10) 11 77 89 60 15E11 is suppressed ZERO 0=IF(SUM(E11:I11)>0,MIN(E11:I11),"") v =CELL("type",E11) 12 0 34 98 45 35E12 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
A B C D E F G H I J K L M N 1 CM HD 223 V 1.13 3.271 1.283 1.546 2.539 3.257 11.896 2.379 1.283 2 BE BB 6PPC U 1.14 0.851 0.987 0.342 0.756 0.521 3.457 0.691 0.342 3 BE RR 223 V 1.15 4.925 3.168 2.074 1.220 1.941 13.328 2.666 1.220 4 DN BB 223 AR 1.16 2.108 1.448 2.513 2.306 2.595 10.970 2.194 1.448 5 MR BL 22 V 1.17 1.025 1.048 1.052 0.773 1.034 4.932 0.986 0.773 6 MS JN 6PPC U 1.18 0.850 0.607 0.927 0.650 0.429 3.463 0.693 0.429 7 TG VN 6PPC U 1.19 0.764 0.480 1.039 0.827 0.706 3.816 0.763 0.480 8 =MIN(E8:I8) 9 =MIN(E9:I9) 10 =MIN(E10:I10) 11 =MINIFS(L1:L10,L1:L10,"<>") 12 I would like to display both the min and contents column a of the same row I want to find Min size of data in column L Sheet2
Worksheet Formulas
Cell Formula 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
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
What did you find out when you followed up on what was provided in post#16 ?
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
Like this thread? Share it with others