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.
我使用以下公式:= MIN(IF(A1:A5> B1,A1:A5))使用Ctrl-Shift-Enter我的B1值是10,我的数组是{1,5,4,2,7}所以在这种情况下没有值大于10.问题是excel返回0作为空集的结果,这是一个问题,因为0不大于10.在这种情况下,我可以测试结果0是否更大但是,如果对于{-15,-24,-11,-37-60}的数组,如果B1为-10,那么当没有正确的值时,0看起来像是有效值,那么结果是无效的。
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.
2 个解决方案
#1
4
Try using SMALL instead of MIN, i.e.
尝试使用SMALL而不是MIN,即
=SMALL(IF(A1:A5>B1,A1:A5),1)
Unlike MIN the SMALL function will return an error [#NUM!] for your example
与MIN不同,SMALL函数将为您的示例返回错误[#NUM!]
....or if you want a text value instead of an error then use IFERROR function, too, i.e.
....或者如果你想要一个文本值而不是一个错误,那么也使用IFERROR函数,即
=IFERROR(SMALL(IF(A1:A5>B1,A1:A5),1),"None")
#2
1
Your IF statement will return False if none of the numbers in the range are greater than 10. It appears that MIN is converting False to numeric (0). You need to add behavior to handle the False.
如果范围中的数字都不大于10,则您的IF语句将返回False。似乎MIN正在将False转换为数字(0)。您需要添加行为来处理False。
If you know that all valid values must be >=0, then you could use the "else" section of the IF formula to return -1.
如果您知道所有有效值必须> = 0,那么您可以使用IF公式的“else”部分返回-1。
MIN(IF(A1:A5>B1,A1:A5,-1))
#1
4
Try using SMALL instead of MIN, i.e.
尝试使用SMALL而不是MIN,即
=SMALL(IF(A1:A5>B1,A1:A5),1)
Unlike MIN the SMALL function will return an error [#NUM!] for your example
与MIN不同,SMALL函数将为您的示例返回错误[#NUM!]
....or if you want a text value instead of an error then use IFERROR function, too, i.e.
....或者如果你想要一个文本值而不是一个错误,那么也使用IFERROR函数,即
=IFERROR(SMALL(IF(A1:A5>B1,A1:A5),1),"None")
#2
1
Your IF statement will return False if none of the numbers in the range are greater than 10. It appears that MIN is converting False to numeric (0). You need to add behavior to handle the False.
如果范围中的数字都不大于10,则您的IF语句将返回False。似乎MIN正在将False转换为数字(0)。您需要添加行为来处理False。
If you know that all valid values must be >=0, then you could use the "else" section of the IF formula to return -1.
如果您知道所有有效值必须> = 0,那么您可以使用IF公式的“else”部分返回-1。
MIN(IF(A1:A5>B1,A1:A5,-1))