Finding the Smallest Even Value

Emin has a range of cells in which there can be either text or numbers. He needs a way to determine the smallest even number in the range. Emin wonders if this can be done with a formula, or if he needs a user-defined function.

There are a couple of ways you can approach this problem. One method you can try is to use the DMIN function. All that you need is to make sure that you have a header on your data column (such as "MyData") and then create a small criteria field in some out-of-the-way place. For instance, you might want to create the criteria field by placing a header (such as "Min Even") in cell F1 and place the formula =ISEVEN(MyData) in cell F2. Cell F2 evaluates to a #VALUE! error, but that is fine in this case. You can then use the following formula in a different cell:

=DMIN(A1:A100, 1, F1:F2)

There is a different formulaic approach you can take. The following checks whether a cell contains a text value or not and, based on the result, checks to see if the value is even or not.

=MIN(IF(ISNUMBER(A1:A100),IF(NOT(MOD(A1:A100,2)=0),"",A1:A100)))

This formula will work just fine in Excel 2019, 2021, and the version with Microsoft 365. If you are using an older version of Excel, you'll need to enter this as an array formula, meaning you enter it using Shift+Ctrl+Enter.

If you prefer, you can create a user-defined function that will return the desired value:

Function MinEven(rng As Range)
    Dim rCell As Range
    Dim bNotFound As Boolean

    Application.Volatile
    MinEven = 9.99 * 10 ^ 307
    bNotFound = True
    For Each rCell In rng
        If Application.WorksheetFunction.IsNumber(rCell) Then
            If rCell Mod 2 = 0 Then
                If rCell < MinEven Then
                    MinEven = rCell
                    bNotFound = False
                End If
            End If
        End If
    Next
    If bNotFound Then MinEven = CVErr(xlErrNum)
End Function

To use this macro, simply use the following with a cell of your worksheet:

=MinEven(A1:A100)

If there are no even numbers in the range, the function will return a #NUM! error.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (124) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Finding the Smallest Even Value.