为区域内每个单元格返回"too low"、"too high"或"OK"的函数

Question

我想要一个函数通过一个单元格范围运行，如果:

• 大于NormalValue则返回'too low'，

• NormalValue大于该范围最大值的两倍然后返回'too high'，

• 这两个都不是true，然后返回'OK'。

这是我目前想到的:

Function TooHighLow(rng As range, NormalValue As Double)
  For Each cell In rng
     If Application.WorksheetFunction.Max(cell.Value) > NormalValue Then
        TooHighLow = "Too Low"
     ElseIf NormalValue > 2 * (Application.WorksheetFunction.Max(cell.Value)) Then
        TooHighLow = "Too High"
     Else
        TooHighLow = "OK"
     End If
  Next cell
End Function 

Answer 1

我想你需要这样的东西:

Function TooHighLow(rng As Range, NormalValue As Double)
    Dim m As Double
    m = Application.WorksheetFunction.Max(rng)
    If m > NormalValue Then
        TooHighLow = "Too Low"
    ElseIf NormalValue > 2 * m Then
        TooHighLow = "Too High"
     Else
        TooHighLow = "OK"
     End If
End Function

1)循环是没有意义的

2)你应该只计算一次最大值，并将结果存储在一个变量中

Answer 2

无VBA:

=IF(MAX(range)>NormalValue,"too low",IF(NormalValue>2*MAX(range),"too high","OK"))

Answer 3

如果您试图从一系列单元格中找到单个低点或高点，那么您将不得不接受突出的值并在该点退出函数。继续执行循环将用范围内下一个单元格的计算结果覆盖未完成的值。

Function TooHighLow(rng As range, NormalValue As Double)
  dim cell as range
  'start with a default value
  TooHighLow = "OK"
  For Each cell In rng
     If Application.WorksheetFunction.Max(cell.Value) > NormalValue Then
        'set the function to return Too Low
        TooHighLow = "Too Low"
        'exit the For Next loop
        Exit For
     ElseIf NormalValue > 2 * (Application.WorksheetFunction.Max(cell.Value)) Then
        'set the function to return Too High
        TooHighLow = "Too High"
        'exit the For Next loop
        Exit For
     End If
     'if the loop has not been exited, the next cell in the range will be evaluated
     'if the loop has been exited, the function will return the outstanding value
  Next cell
End Function