如果未找到值,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我的 B1 值是 10,我的数组是 {1,5,4,2,7} 所以在这种情况下没有值大于 10.问题是 excel 返回 0 作为空集的结果,这是一个问题,因为 0 不大于 10.在这种情况下,我可以测试结果 0 是否大于 10 并查看结果是否无效,但是,如果对于 {-15,-24,-11,-37-60} 数组,B1 是 -10当不存在正确值时,0 似乎是一个有效值.
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?
谢谢.
推荐答案
尝试使用 SMALL
而不是 MIN
,即
Try using SMALL
instead of MIN
, i.e.
=SMALL(IF(A1:A5>B1,A1:A5),1)
与 MIN
不同,SMALL
函数将为您的示例返回错误 [#NUM!]
Unlike MIN
the SMALL
function will return an error [#NUM!] for your example
....或者如果你想要一个文本值而不是一个错误,那么也使用 IFERROR 函数,即
....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),"无")
这篇关于如果未找到值,Excel 最小值大于 x 将返回 0?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!