Function to return "too low", "too high" or "OK" for each cell in a range

Question

I want a function to run through a range of cells and if:

any are greater than NormalValue then return 'too low',
NormalValue is greater than double the maximum value in the range then return 'too high',
neither of these are true, then return 'OK'.

This is what I have come up with so far:

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

Remove the loop and replace `cell.Value` with `rng`. – GSerg Sep 22 '15 at 11:19 — GSerg, Sep 22 '15 at 11:19

score 2 · Accepted Answer · answered Sep 22 '15 at 11:23

2

I think you want something like this:

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) The loop was pointless

2) You should only compute the max once, storing the result in a variable.

answered Sep 22 '15 at 11:23

John Coleman

51,337
7
54
119

Ah yes this is exactly what I want, thank you! I see now that the loop was pointless. Thanks very much for your help. – R.Williams Sep 22 '15 at 12:23

score 1 · Answer 2 · answered Sep 22 '15 at 21:17

1

Without VBA:

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

answered Sep 22 '15 at 21:17

pnuts

58,317
11
87
139

score 0 · Answer 3 · answered Sep 22 '15 at 11:30

If you are trying to find a single low or high point from a range of cells then you will have to accept the outstanding value and exit your function at that point. Continuing through the loop will overwrite the outstanding value with whatever the next cell in the range is evaluated to.

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

Function to return "too low", "too high" or "OK" for each cell in a range

3 Answers3