# Finding the Smallest Even Value

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 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 an #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)
```

If you prefer, you can use an array formula to figure out the lowest even value. Because your data range can contain text as well as numbers, not all array formulas will work, however. For instance, the following will generate an error if there is anything but numbers in the data range:

```=MIN(IF(MOD(A1:A100,2)=0,A1:A100))
```

To make sure you don't get the errors, you need to do some checking in the formula:

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

Again, remember that this is an array formula, so you need to 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 (119) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Smallest Even Value.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What is 5+3 (To prevent automated submissions and spam.)

Rick Rothstein    26 Dec 2015, 10:36
I have simplified the code I posted earlier today by removing one of the Replace function calls (by embedding the number it was replacing directly into the Evaluate function call as a number. Here is my newly modified one-liner UDF for the MinEven function...

Function MinEven(Rng As Range) As Long
End Function

Rick Rothstein    26 Dec 2015, 06:01
While it is somewhat long, the UDF for MinEven can be written as a one-liner...

Function MinEven(Rng As Range) As Long
MinEven = Evaluate(Replace(Replace("MIN(IF(ISNUMBER(@),IF(MOD(@,2)=0,@,#),#))", "@", Rng.Address), "#", 9 * 10 ^ 307))
End Function

# Our Company

Sharon Parq Associates, Inc.

# Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Excel Products

Word Products

# Our Authors

Author Index

Write for Tips.Net