How to Find the Smallest Value and Smallest Positive Value in Excel

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate how can we get the smallest positive value by different formulas.

Returns the Smallest Value by Small Function


If there are all positive values for a set of values, we can use SMALL function to get the smallest value directly. See the table below.

Find the Smallest Positive Value 1

There are a set of values, obliviously the smallest value is 1. But if we want use a formula to find out the smallest value, we can use SMALL function.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,1).

Find the Smallest Positive Value 2

Step 2: Click Enter to get the result.

Find the Smallest Positive Value 3

Returns the k-th Smallest Value by Small Function


In above case, we enter the formula =SMALL(A2:B6,1) then we can get the smallest value 1. For SMALL function, the parameter is SMALL(array,k), k is ‘Returns the k-th minimum value in the dataset’. So, we can change k number to get the k-th smallest value.

Follow above steps, in D2 cell change the formula to =SMALL(A2:B6,2). Then we can get the second smallest value 2.

Find the Smallest Positive Value 4

Returns the Smallest Positive Value by Small Function


Find the Smallest Positive Value 5

Update above table a little. Replace a positive number with 0. So If we still use above SMALL function without any criteria, it will return 0 as the smallest value. So if we still want to use the SMALL function here, we need to count the number of zero value, then we need to add 1 base on the number of zero value.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,COUNTIF(A2:B6,0)+1), where COUNTIF function is used for counting the number of zero value.

Find the Smallest Positive Value 6

Step 2: Let’s replace some values with zero values and check the formula again.

Find the Smallest Positive Value 7

Returns the Smallest Positive Value by MIN Function


If we don’t want to add any criteria in SMALL formula to identify if zero value or negative value exists among the set of values, we can directly use MIN function.

See the table below, it contains both negative value and zero value.

Find the Smallest Positive Value 8

Step 1: In D2 cell, enter the formula =MIN(IF(A2:B6>0,A2:B6)). IF function returns an array which satisfy the criteria ‘every value in the array >0’.

Step 2: Press control+shift+enter to returns value.

Find the Smallest Positive Value 9

Related Functions


  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
Related Posts

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array

What is Excel Array? In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values. The dimension ...

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract all Partial Matches

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate ...

Sidebar