## How to ignore error values using the SMALL function

The image above shows you a formula in cell D3 that tries to get the smallest number from cell range B3:B12 but it returns an error. This happens if the cell range contains at least one error value.

Formula in cell D3:

The SMALL function ignores text and boolean values but not error values, however, the AGGREGATE function lets you choose between a variety of functions (including SMALL function).

You also have the option to ignore error values, the second argument lets you specify this.

The AGGREGATE function contains these functions AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC.

You can see a list of available functions while entering the arguments in the function, see image below.

The second argument has the following settings, I chose 6 - Ignore error values.

The AGGREGATE function was introduced in Excel 2010, if you have an earlier Excel version then I recommend using the following array formula:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Convert array formula to a regular formula

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

How to use the AGGREGATE function

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

SMALL function with duplicates

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

How to ignore zeros using the SMALL function

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros. Related articles

SMALL function – multiple conditions

The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form