如果没有找到值,Excel最小值大于x返回0? [英] Excel min value greater than x returns 0 if no value found?
问题描述
我使用以下公式:
= MIN(IF(A1:A5> B1,A1:A5))使用Ctrl-Shift-Enter
我的值为10, {1,5,4,2,7}所以在这种情况下没有值大于10.
问题是,excel返回0作为空集的结果,这是一个问题,因为0不大于10.
在这种情况下,我可以测试结果0是否大于10,并且看到结果无效,但是如果对于{-15,-24,-11, -37-60}比0似乎是一个有效的值,当没有正确的值存在。
所以任何人都知道如何可以找到一个集合的最小或最大值
尝试使用 SMALL
而不是 MIN
,即
= SMALL(IF(A1:A5> B1,A1:A5),1)
b $ b
与 MIN
不同, SMALL
函数将返回一个错误[#NUM! / p>
....或者如果你想要一个文本值而不是错误,那么使用IFERROR函数,也就是
= IFERROR(SMALL(IF(A1:A5> B1,A1:A5),1),None)
I am using the following formula: =MIN(IF(A1:A5>B1,A1:A5)) use Ctrl-Shift-Enter My value for B1 is 10 and my array is {1,5,4,2,7} so in this case no value is greater than 10. The problem is that excel returns 0 as the result of the empty set which is a problem as 0 is not greater than 10. In this case, I can test if the result 0 is greater than 10 and see that the result is invalid, however, if B1 is -10 for an array of {-15,-24,-11,-37-60} than the 0 seems like a valid value when no correct value exists.
So anybody know of how I can find the min or max value of a set with constraints, but return either an error or something distinct if the solution set is empty?
Thank you.
Try using SMALL
instead of MIN
, i.e.
=SMALL(IF(A1:A5>B1,A1:A5),1)
Unlike MIN
the SMALL
function will return an error [#NUM!] for your example
....or if you want a text value instead of an error then use IFERROR function, too, i.e.
=IFERROR(SMALL(IF(A1:A5>B1,A1:A5),1),"None")
这篇关于如果没有找到值,Excel最小值大于x返回0?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!