User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation?

[SteveM]

Excel Genii,

I've Created MaxIf and MinIf functions analogous to SumIf. But it's
not clear to me how to insert the Condition string captured in the
dialog box into the IF block in the functions themselves in order to
check each value in the selected range.

Any ideas?

Thanks,

Steve

 

[Joel]

Below is a simple form of the function. It doesn't cover the case where the
criteria may be a range of cells, but does show how to break the condition
into multiple parts.


Function maxif(Target As Range, criteria As String)

If InStr(criteria, "<") > 0 Then
Value1 = Left(criteria, InStr(criteria, "<") - 1)
If InStr(criteria, "=") > 0 Then
Condition = "<="
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Else
Condition = "<"
Value2 = Mid(criteria, InStr(criteria, "<") + 1)
End If
Else
If InStr(criteria, ">") > 0 Then
Value1 = Left(criteria, InStr(criteria, ">") - 1)
If InStr(criteria, "=") > 0 Then
Condition = ">="
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Else
Condition = ">"
Value2 = Mid(criteria, InStr(criteria, ">") + 1)
End If
Else
'must be just an equal sign
Value1 = Left(criteria, InStr(criteria, "=") - 1)
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Condition = ">"
End If

If Not IsNumeric(Value1) Then
Value1 = Range(Value1)
End If
If Not IsNumeric(Value2) Then
Value2 = Range(Value2)
End If

Select Case Condition
Case ">"
Truth = Value1 > Value2
Case ">="
Truth = Value1 >= Value2
Case "<"
Truth = Value1 < Value2
Case "<="
Truth = Value1 <= Value2
Case "="
Truth = (Value1 = Value2)
End Select

End Function