i need to know how can i exclude 0 from rows and get the MIN Value.
我需要知道如何从行中排除0并得到最小值。
But also i need to exlude the F1 Cell.
但我还需要屏蔽F1细胞。
Ex:
例:
A B C D E F
1 0 18 20 0 150 = 18
but if i do this In excel with =MIN(A1,B1,C1,D1,E1) return 0.
但是如果我用=MIN(A1,B1,C1,D1,E1)来做这个，返回0。
Any help is appreciated.
任何帮助都是感激。
7 个解决方案
#1
5
Try this formula
试试这个公式
=SMALL((A1,C1,E1),INDEX(FREQUENCY((A1,C1,E1),0),1)+1)
=小((A1,C1,E1)指数(频率((A1,C1,E1),0),1)+ 1)
Both SMALL and FREQUENCY functions accept "unions" as arguments, i.e. single cell references separated by commas and enclosed in brackets like (A1,C1,E1).
小函数和频率函数都接受“联合体”作为参数，即用逗号分隔的单个单元引用，并包含在括号中(A1、C1、E1)。
So the formula uses FREQUENCY and INDEX to find the number of zeroes in a range and if you add 1 to that you get the k value such that the kth smallest is always the minimum value excluding zero.
公式用频率和指数来求范围内的0的个数如果加上1，就得到k的值k的最小值总是最小值，0除外。
I'm assuming you don't have negative numbers.....
我假设你没有负数……
#2
7
Enter the following into the result cell and then press Ctrl & Shift while pushing ENTER:
在结果单元格中输入以下内容，按Ctrl + Shift，按回车键:
=MIN(If(A1:E1>0,A1:E1))
#3
6
By far the most efficient method is to use the SMALL and COUNTIF formula as shown below;
目前最有效的方法是使用如下所示的小的和可数的公式;
SMALL Returns the k-th smallest value in a data set.
SMALL返回数据集中第k个最小值。
=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
=小(A1:A100条件统计(1美元:100美元,美元0)+ 1)
Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value.
countif计算范围内的0(+1)，用于告诉SMALL返回第k个最小值。
Credit: link
信贷:链接
#4
2
if all your value are positive, you can do -max(-n)
如果你的值都是正的，你可以做-max(-n)
#5
1
Not entirely sure what you want here, but if you want to discount blank cells in the range and pass over zeros then this would do it; if a little contrived:
不完全确定你想要什么，但是如果你想在范围内折现空白单元格并传递0，那么这个就可以;如果一个小的:
=MIN(IF(A1:E1=0,MAX(A1:E1),A1:E1))
With Ctrl+Shift+Enter as an array.
使用Ctrl+Shift+Enter作为数组。
What I'm doing here is replacing zeros with the maximum value in the list.
这里我要做的是用列表中的最大值来替换0。
#6
0
min() fuction exlude BOOLEAN and STRING values. if you replace your zeroes with "" (empty string) - min() function will do its job as you like!
min()功能exlude布尔和字符串值。如果你更换与“零(空字符串)- min()函数将完成其工作你喜欢!
#7
-3
All you have to do is to delete the "0" in the cells that contain just that and try again. That should work.
你所要做的是要删除的“0”细胞包含,并再试一次。这应该工作。
#1
5
Try this formula
试试这个公式
=SMALL((A1,C1,E1),INDEX(FREQUENCY((A1,C1,E1),0),1)+1)
=小((A1,C1,E1)指数(频率((A1,C1,E1),0),1)+ 1)
Both SMALL and FREQUENCY functions accept "unions" as arguments, i.e. single cell references separated by commas and enclosed in brackets like (A1,C1,E1).
小函数和频率函数都接受“联合体”作为参数，即用逗号分隔的单个单元引用，并包含在括号中(A1、C1、E1)。
So the formula uses FREQUENCY and INDEX to find the number of zeroes in a range and if you add 1 to that you get the k value such that the kth smallest is always the minimum value excluding zero.
公式用频率和指数来求范围内的0的个数如果加上1，就得到k的值k的最小值总是最小值，0除外。
I'm assuming you don't have negative numbers.....
我假设你没有负数……
#2
7
Enter the following into the result cell and then press Ctrl & Shift while pushing ENTER:
在结果单元格中输入以下内容，按Ctrl + Shift，按回车键:
=MIN(If(A1:E1>0,A1:E1))
#3
6
By far the most efficient method is to use the SMALL and COUNTIF formula as shown below;
目前最有效的方法是使用如下所示的小的和可数的公式;
SMALL Returns the k-th smallest value in a data set.
SMALL返回数据集中第k个最小值。
=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
=小(A1:A100条件统计(1美元:100美元,美元0)+ 1)
Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value.
countif计算范围内的0(+1)，用于告诉SMALL返回第k个最小值。
Credit: link
信贷:链接
#4
2
if all your value are positive, you can do -max(-n)
如果你的值都是正的，你可以做-max(-n)
#5
1
Not entirely sure what you want here, but if you want to discount blank cells in the range and pass over zeros then this would do it; if a little contrived:
不完全确定你想要什么，但是如果你想在范围内折现空白单元格并传递0，那么这个就可以;如果一个小的:
=MIN(IF(A1:E1=0,MAX(A1:E1),A1:E1))
With Ctrl+Shift+Enter as an array.
使用Ctrl+Shift+Enter作为数组。
What I'm doing here is replacing zeros with the maximum value in the list.
这里我要做的是用列表中的最大值来替换0。
#6
0
min() fuction exlude BOOLEAN and STRING values. if you replace your zeroes with "" (empty string) - min() function will do its job as you like!
min()功能exlude布尔和字符串值。如果你更换与“零(空字符串)- min()函数将完成其工作你喜欢!
#7
-3
All you have to do is to delete the "0" in the cells that contain just that and try again. That should work.
你所要做的是要删除的“0”细胞包含,并再试一次。这应该工作。