Small formula excel

Small formula excel DEFAULT

This article describes the formula syntax and usage of the SMALL function in Microsoft Excel.

Description

Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.

Syntax

SMALL(array, k)

The SMALL function syntax has the following arguments:

  • Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.

  • K    Required. The position (from the smallest) in the array or range of data to return.

Remarks

  • If array is empty, SMALL returns the #NUM! error value.

  • If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM! error value.

  • If n is the number of data points in array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data 1

Data 2

3

1

4

4

5

8

2

3

3

7

4

12

6

54

4

8

7

23

Formula

Description (Result)

Result

=SMALL(A2:A10,4)

4th smallest number in first column (4)

4

=SMALL(B2:B10,2)

2nd smallest number in the second column (3)

3

Sours: https://support.microsoft.com/en-us/office/small-function-17da8222-7c82-42b2-961b-14c45384df07

SMALL Function in Excel

The Microsoft Excel SMALL Function is the function that is responsible for returning the nth smallest value from the supplied set of values. In other words, we can say that SMALL Function in Excel retrieves the “nth smallest” values- like the smallest value, 2nd smallest value, 3rd smallest value, etc. The SMALL Function in Excel is a built-in function of Excel and is categorized as a Statistical Function in excel. As a part of the worksheet function in ExcelThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more, SMALL Function can also be entered as a part of another formula in a cell of a worksheet.

In terms of financial analysis, SMALL Function is very much useful for finding the smallest value in a supplied set of values in Microsoft Excel. For example, if the entire salesman in a company is given the same target, then with the help of SMALL Function, we can find that which person achieved the sales target in the shortest time of the year.

SMALL Formula Excel

Below is the SMALL Formula excel.

SMALL Formula

Parameters of the SMALL Function in Excel

The SMALL function accepts the following parameters and arguments:

array – This is the range or array from which you want the function to return the nth smallest value.

nth_position – This is an integer that specifies the position from the value, which is the smallest, i.e., the nth position.

How to Use the SMALL Function in Excel?

You can download this SMALL Function Excel Template here – SMALL Function Excel Template

Below are the steps of using small function in excel –

  1. Just enter the desired SMALL in the required cell, and you will get a return value on the argument supplied.
  2. You can manually open the SMALL formula Excel dialogue box in the spreadsheet and enter the logical values to attain a return value.
  3. Consider the screenshot below to see the SMALL function option under the Statistical Function menu.


    SMALL Formula in Excel

  4. Click on the SMALL option. The SMALL formula excel dialogue box will open where you can put the argument values to obtain a return value.


    SMALL Formula in Excel 1

Return Value

The return value will be a numeric value, which is the nth smallest value in the array. Please note that if the nth_position is a larger value than the number of values in the array, then the SMALL function will return #NUM! error. If the supplied array is empty, then the SMALL function will be returning #NUM! error.

Usage Notes

  • The SMALL function is a useful function when you are going to retrieve the nth smallest value from the supplied set of data.
  • For example, the SMALL Excel Function can be used to find the first, second, or the third-lowest scores of a test or the fastest times of a race data.
  • Similar to the LARGE Excel Function, the SMALL Function also retrieve numeric values which are based on their position in a supplied list when they are sorted by value.
  • Please note that Microsoft Excel uses “k” instead of “n.” We are using “nth” because it is much easier to understand the SMALL formula excel and its working.

How to Use SMALL Function in Excel with Examples

Let’s look below at some of the examples of the SMALL function in Excel. These examples will help you in exploring the use of SMALL Formula Excel.

SMALL Function Example

Based on the above SMALL, an Excel spreadsheet, let’s consider these examples and see the SMALL function return based on the syntax of the function.

SMALL Function Example 1

Consider the below screenshots of the above SMALL in Excel examples for clear understanding.

SMALL in Excel Example #1

Using the SMALL Formula in Excel =SMALL(A1:A5, 1), we get -3.5

SMALL Function Example - 1

SMALL in Excel Example #2

Apply the SMALL formula =SMALL(A1:A5, 2) to get 4

SMALL Function Example - 2

SMALL in Excel Example #3

Now apply the SMALL in Excel here =SMALL(A1:A5, 3) to get 6.8

SMALL Function Example - 3

SMALL in Excel Example #4

Apply the SMALL formula here =SMALL(A1:A5, 4) to get 7

SMALL Function Example - 4

SMALL in Excel Example #5

Here we apply the SMALL formula in Excel =SMALL(A1:A5, 5) to get 36

SMALL Function Example - 5

SMALL in Excel Example #6

Here we use the formula to calculate the SMALL in excel =SMALL({6, 23, 5, 2.3}, 2)

SMALL Function Example - 6

Top Applications of SMALL Function in Excel

The Microsoft Excel SMALL Function in Excel can be used for various purposes and applications within the spreadsheet. Some of the common applications of SMALL Excel Function spreadsheets are given below –

  • Highlighting the bottom values of the given data
  • To get the sum of bottom n values
  • Sorting numbers ascending or descending
  • Extracting multiple matches into several columns
  • Finding lowest n values
  • Extracting multiple matches into several rows
  • Sum bottom n values with criteria

SMALL Function in Excel – Common Problem

One of the most common problems which you can face while you are using the SMALL Function is that it returns the wrong value, or the return value is nothing but an error such as #NUM! error. This error can occur even if the supplied value of k is between 1 and the supplied array value numbers. Possibly, this can arise when you have included text representations of the number within the supplied array. Text values are ignored by the SMALL Function, and it only recognizes numerical values. If you face this error, then convert all the array values into numeric values.

SMALL Function Errors

If you get any kind of error from the SMALL in excel, then it can be any one of the following-

#NUM! – This kind of error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! – This kind of error occurs when the supplied n is a non-numeric value.

Things to Know about the SMALL Function in Excel

  • The SMALL in Excel is the function that is responsible for returning the nth smallest value from a given set of values in the spreadsheet.
  • The SMALL in Excel is categorized as a Statistical Function.
  • The function basically returns a numeric value, which is totally based on their position in a supplied list of values when sorted.
  • If the supplied array is empty, then the SMALL in Excel will be returning #NUM! error.
  • If the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array, then the SMALL in Excel will be returning #NUM! error.
  • If the supplied value is non-numeric, then the SMALL in Excel will be returning #VALUE! error.

Recommended Articles

This has been a guide to SMALL Function in Excel. Here we discuss the SMALL Formula excel and how to use SMALL in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Sours: https://www.wallstreetmojo.com/small-function-in-excel/
  1. Really good kdramas
  2. Rose flower good night images
  3. Beginner pet snails

SMALL Excel Function

SMALL Function in Excel

SMALL Function in Excel (Table of Contents)

SMALL Function in Excel

A small function in excel is used for getting the smallest number from the selected range of numbers with the help of the Kth position located in the range. For example, we have 10 different numbers, and we need to find the smallest number out of that; by using the Small function, we can get the 1st or 2nd or any Kth smallest number out of those 10 selected numbers.

SMALL Formula in Excel:

Below is the SMALL Formula in Excel :

SMALL Formula in Excel

The SMALL function has two arguments, i.e. array, k. Both are required arguments.

  • Array: This is the range of cells you are selecting it as the source data to find the K value.
  • K: This the Kth position of the number. From the list, it gives the bottom value.

In this function, the range should not be empty, and we need to specify both the arguments.

How to Use SMALL Function in Excel?

SMALL Function in Excel is very simple and easy to use. Let us now see how to use this SMALL Function in Excel with the help of some examples.

Example #1

Below are the scores of the students on a test. From the below-given data, find the smallest score and the 3rd smallest score.

SMALL Example 1-1

If we are finding the smallest number, we can simply apply MIN Function. If you look at the below image, both the formulas returning the same value as the smallest number in the given list.

SMALL Example 1-2

However, MIN Function stops there only it cannot find the 2nd, 3rd and 4th smallest number. In such cases, SMALL can give us the Kth position number.

Find the 3rd Smallest Number.

To find the third smallest score or number from the list, we need to specify the number in the Kth argument.

The formula is =SMALL (B2:B11, 3), and it reads like this:

“In the range, B2:B11 find the 3rd largest value.”

SMALL Example 1-3

So the result will be :

SMALL Example 1-4

Example #2

Below is the data for a cycle race. From this list, you need to find the winner. Data includes names, start time, end time.

SMALL Example 2-1

From this list, we need to find who has taken the least time to complete the race.

Step 1: Find the total time taken.

SMALL Example 2-2

The actual time taken to complete the race arrived by deducting the start time by the end time. The below image shows the actual time taken by each one to complete the race.

SMALL Example 2-3

Step 2: Now apply the SMALL Function to get the winner.

SMALL Example 2-5

So the result will be :

SMALL Example 2-6

It is a bit of a herculean task if the list is long. But we can just name the winner using the if condition.

SMALL Example 2-7

So the result will be :

SMALL Example 2-8

Similarly, it is applied to other cells in that column to get the desired output.

SMALL Example 2-9

If the value arrived by a SMALL function is equal to the actual time taken value, we call it as Winner or Better Luck Next Time.

Example #3

We can use the SMALL Function along with other functions. From the below-given list, find the sum of the bottom 3 values for Week2.

SMALL Example 3-1

Apply the below SMALL function along with the SUM & VLOOKUP function.

SMALL Example 3-2

This is an array formula. You need to close the formula by typing Ctrl + Shift + Enter. This would insert the curly brackets before and after the formula.

VLOOKUP returns the value for WEEK 2 specified by the SMALL function for 3 bottom values. Then SUM function will add the bottom values together and return the result as 1988.

SMALL Example 3-3

Things to Remember

  • SMALL Function ignores text values and considers only numerical values.

SMALL Function Error 1-1

Result is :

SMALL Function Error 1-2

  • A SMALL function returns an error if there are no numerical values in the list.

SMALL Function Error 2-1

Result is :

SMALL Function Error 2-2

  • If there are any duplicates, then SMALL considers the first value as the smaller one.
  • K should be numeric; otherwise, it returns the error as #VALUE!
  • Supplied range should not be empty.
  • In case of finding only the least value, then we can use the MIN Function. But it finds only the first smallest value.
  • Even though SMALL ignores text values, if there are any errors, then it will return the result as #DIV/0!.
  • We can use the SMALL along with many other functions to find the Nth values.
  • Use practically to get the hint of SMALL function.
  • If you are using SMALL with other function, then it becomes an array formula.

Recommended Articles

This has been a guide to SMALL Function in Excel. Here we discuss the SMALL Formula in Excel and how to use a SMALL Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Function in Excel
  2. SUM Function in Excel
  3. SUBTOTAL Function in Excel
  4. TRUE Function in Excel
Sours: https://www.educba.com/small-function-in-excel/
SMALL Formula in Excel - Get the Smallest Values from a List in Excel

Sort numbers using Excel SMALL function

Fb-Button

In this article, we will learn about how to Sort numbers using Excel SMALL function with ROWS function in Excel.

Sort numbers in excel using the excel functions. Excel SMALL function returns the kth smallest value from the data. We can get the data in sorted way.

The SMALL function returns the kth smallest value starting from the smallest in a range of values.
Syntax:

=SMALL (array, k)

K : k=1 return the smallest value and if k equals the number of values in an array then it returns the largest value of the data or else it returns #NUM! error. The ROWS function returns the number of rows in excel.
Syntax:

=ROWS (array)

Let’s make generic formula from the information stated above
Generic formula:

=SMALL (array, ROWS(fixed_cell : varying_cell))

Array : data of numbers
Fixed_cell : cell given as reference. For precaution take the first cell of the data as fixed cell and lock it.
varying _cell : cell reference to vary the kth value for the SMALL function.
Let’s understand this function using it in an example.
001
Here we have a list of numbers that needed to be sort in ascending
Use the formula:

=SMALL(A2:A21,ROWS($A$2:A2))

$A$2 : locked cell which doesn’t change when copied in excel.
A2 : unlocked cell which changes when copied to other cells.
Explanation:
The value of k here will be 1. The formula drops down to this =SMALL(A2:A21,1) as the ROWS function returns the number of rows which is 1 here..
002
As you can see, 23 is the smallest number. Now we need this formula to generate increasing value of k for the SMALL function.

Let’s see when we use Ctrl + D shortcut key for copying the formula to other cells.
003
As you can see we got accurate value of k for every new cell.
The trick is the value of k for the SMALL function. So here we used the ROWS function which returns increasing value of k.
Now we need to get the descending order for the same data.
So now we Use the formula:

=SMALL(A2:A21,ROWS($A$2:A21))

$A$2 : locked cell which doesn’t change when copied in excel.
A21 : unlocked cell which changes when copied to other cells.
Explanation:
The value of k here will be 20. The formula drops down to this =SMALL(A2:A21,20) as the ROWS function returns the number of rows which is 20 here.
004
As you can see, 87 is the largest number. Now we need this formula to generate decreasing value of k for the SMALL function.

Let’s see when we use Ctrl + D shortcut key for copying the formula to other cells.
005
As you can see we got accurate value of k for every new cell.
The trick is the value of k for the SMALL function. So here we used the ROWS function which returns decreasing value of k.
As you can see, the SMALL function can sort numbers in both ways.
Hope you understood How to use the SMALL function in Excel. Explore more articles on Mathematical functions like SMALL and SMALL functions here. Please state your query or feedback in the comment box below.

Related Articles:

How to use the LARGE Function in Excel

How to Use RANK in Excel

Sort numbers using Excel SMALL function

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF in Excel 2016

How to use the SUMIF Function in Excel

 

 

Sours: https://www.exceltip.com/excel-functions/how-to-use-the-small-function-in-excel.html

Formula excel small

The Excel SMALL Function

Function Description

The Excel Small function returns the k'th smallest value from an array of numeric values.

The syntax of the function is:

SMALL( array, k )

where the function arguments are:

array-An array of numeric values from which you want to find the k'th smallest value.
k-The index (i.e. the function returns the k'th smallest value from the supplied array).

The array argument can be supplied to the function either directly, or as a reference to a range of cells containing numeric values. If values in the supplied range of cells are text values, these values are ignored.


Small Function Examples

The following spreadsheet shows the Excel Small function, used to retrieve the 1st, 2nd, 3rd, 4th and 5th smallest values from the set of values in cells A1-A5.

 Formulas:

 AB
16=SMALL( A1:A5, 1 )
212=SMALL( A1:A5, 2 )
315=SMALL( A1:A5, 3 )
41=SMALL( A1:A5, 4 )
54=SMALL( A1:A5, 5 )

 Results:

 AB
161
2124
3156
4112
5415

Note that in the above example spreadsheet:

  • In cell B1, where k is set to 1, the Small function performs the same action as the Excel Min function;
  • In cell B5, when k is set to 5 (the number of values in the supplied array), the Small function performs the same action as the Excel Max function.

Further details and examples of the Excel Small function are provided on the Microsoft Office website.


Small Function Errors

If you get an error from the Excel Small Function, this is likely to be one of the following:

Common Errors

#NUM!-

Occurs if either:

  • The supplied value of k is less than 1 or greater than the number of values in the supplied array
or
  • The supplied array is empty.
#VALUE!-Occurs if the supplied k is non-numeric.


Also, the following problem is encountered by some users:

Common Problem

The Small function returns the wrong value, or returns the #NUM! error, even though the supplied value of k is between 1 and the number of values in the supplied array.

Possible Reason

Text values, including text representations of numbers within the supplied array, are ignored by the Small function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values.

Solution

This problem can be solved by converting all array values into numeric values. For details of how to do this, see the Convert Text to Number page.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions


Sours: https://www.excelfunctions.net/excel-small-function.html
Use the SMALL Function in Excel

Excel SMALL IF formula to find Nth lowest value with criteria

Trying to get a bottom value based on one or more conditions? Just use the SMALL function together with IF. This tutorial shows how to build a working solution and explains its internal logic, so that you could easily decipher the formula and adjust for your needs.

Microsoft Excel has a number of functions to do "conditional" calculations such as MAXIFS, MINIFS, AVERAGEIF, and the like. Regrettably, the SMALL IF function does not exist. However, nothing prevents you from building your own formula to find the n-th smallest value with criteria. If you are not familiar with the Excel SMALL function yet, then you might want to start with the basics and read the above linked tutorial first.

SMALL IF formula in Excel

To get the n-th lowest value that matches the criteria you specify, you can use the following generic formula:

{=SMALL(IF(criteria_range=criteria, values), n)}

Where n is the 1st, 2nd, 3rd, etc. lowest value to return.

For the formula to work correctly, you should enter it as an array formula by pressing the Ctrl + Shift + Enter keys simultaneously. When you do this, Excel will surround the formula in curly brackets like shown in the screenshot below. In Excel 365, it also works as a regular formula due to support for dynamic arrays.

From our sample the table, let's pick bottom 3 scores in a specific subject, say Art. With the list of subjects (criteria_range) in B2:B15, scores in C2:C15 (values) and n in E3, the formula takes this form:

This formula returns the smallest Art score in F3. Copy it down through F5, and you will get the 2nd and 3rd lowest results.

For convenience, you can also input the names of the target subjects in predefined cells (F2 - Art and G2 - Science), and refer to those cells as criteria:

SMALL IF formula in Excel

If the design of your worksheet does not provide for n numbers, you can generate them directly in the formula by using the ROWS function with an expanding range reference like this:

Due to the clever use of absolute and relative references, the range reference expands automatically as the formula is copied to the below cells. In E3, ROWS(A$2:A2) generates n equal to 1, and the formula returns the smallest score for Art. In E4, the reference changes to A$2:A3 causing ROWS to return 2, so we get the 2nd smallest score, and so on.
SMALL IF formula to return bottom 3 values based on condition

In case the n-th smallest value with specified criteria is not found, the SMALL IF formula would return a #NUM error. To trap this error and replace it with whatever value you find appropriate ("-" in our case), you can use the IFERROR function:

SMALL IF together with IFERROR to trap possible errors

How this formula works:

From an array supplied for the 1st argument, the SMALL function returns the n-th smallest value specified in the 2nd argument. The problem is that we do not want every single value in the array to be processed, but only the scores in a given subject. To limit the array to Art values, we tell the IF function to compare the list (B2:B15) against the target subject:

Because the logical test is performed on an array of numbers, the result is also an array, where the numbers represent the Art scores, and FALSE values any other scores:

As the SMALL function ignores errors (and anything else that is not a number), the result is the n-th smallest value from the Art scores in the above array.

Excel SMALL IF with multiple criteria

To cause a SMALL IF formula to evaluate several conditions, choose one of the following approaches.

Supply multiple criteria by using nested IF statements:

{=SMALL(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, values)), n)}

Multiply the logical expressions:

{=SMALL(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), values), n)}

Please notice that both are array formulas, so don't forget to press Ctrl + Shift + Enter to properly complete them. In Excel 365, these will also work as regular formulas.

To test the formula "in the field", we will extend our sample table with the School column and input 2 criteria in separate cells like shown below.

Now, we have all needed parameters for our SMALL IF formula:

  • Criteria_range1 - list of subjects (B2:B15)
  • Criteria1 - target subject (G1)
  • Criteria_range2 - school types (C2:C15)
  • Criteria2 - target school (G2)
  • Values - scores (D2:D15)
  • N - numbers 1 to 3 in F6, F7 and F8

Putting the arguments together, we get these formulas:

Enter either one in G6, drag it through G8, and you will get the following result.
SMALL IF formula to return bottom values with multiple criteria

How these formulas work:

In essence, both formulas use the IF function to test multiple conditions, so that only the values for which all the conditions are TRUE get into the array argument of the SMALL function.

Nested IFs:

In the logical test of the first IF function, we compare the list of subjects against the target one (Art): $B$2:$B$15=$G$1. The result of this operation is an array of TRUE and FALSE values, where TRUE corresponds to the Art values in column B:

The second IF statement checks which cells in the range C2:C15 match the criterion in G2 (Junior), and also returns an array of TRUE and FALSE:

For the value_if_true argument of the second IF, we supply the scores (D2:D15). This ensures that only the items that have TRUE in the above two arrays "survive"; all other scores are replaced with the FALSE values:

This final array goes to the SMALL function, from which it returns the n-th lowest value.

Multiplying criteria:

The second formula checks both conditions within a single logical test, where the multiplication operation works as AND operator.

When the elements of two TRUE/FALSE arrays are multiplied, the logical values are converted to 1's (TRUE) and 0's (FALSE). As multiplying by 0 gives zero, the resulting array has 1 for data that meet both criteria:

The IF function evaluates this array of 1's and 0's in the logical test and passes the scores corresponding to 1's to SMALL.

SMALL IF formula with multiple OR criteria

The previous example shows how to find bottom values based on multiple criteria using AND logic, i.e. when all the conditions are satisfied. To get the smallest values that satisfy any of the conditions, you need to build a SMALL IF formula with OR logic. For this, add up the criteria instead of multiplying them.

{=SMALL(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), values), n)}

As an example, let's pick the lowest scores in two different subjects, say History and Literature. In terms if Excel, the formula will return the n-th smallest score if Subject is either History OR Literature.

With the subjects in B2:B15 and scores in C2:C15, here's the formula to return the lowest score:

Of course, you can enter the criteria and n numbers in separate cells, complete the formula by pressing Ctrl + Shift + Enter and get this result:

SMALL IF formula to return lowest 3 values based on multiple OR criteria

How this formula works:

The formula's logic is very similar to what is discussed in SMALL IF with multiple AND criteria example. The difference is that the range=criteria equations are joined with the addition operation that works like the OR operator in array formulas:

Checking the list of subjects against 2 different criteria yields 2 arrays of TRUE and FALSE values. Adding the elements of those arrays produces an array of 1's and 0's, where 1's correspond to the items that meet at least one condition (no matter which) and zeros correspond to the items that do not meet any condition. This final array goes to the logical test of IF:

The IF function evaluates each element of the above array and passes the scores corresponding to 1's to the SMALL function, from which it picks the specified n-th value:

How to find smallest values ignoring zeros

Though your dataset may sometimes contain some zero values, it does not always make sense to include those zeros in the formula result. To leave out all 0 values, we will be using the already familiar SMALL IF formulas with the "not equal to zero" criteria.

Formula 1. SMALL IF not zero

To get the n-th smallest value ignoring 0, use this generic array formula:

{=SMALL(IF(values<>0, values), n)}

Suppose a few students missed some exams and have 0 scores for those subjects. To get the lowest 3 scores that are greater than 0, the formula is:

Please remember to press CTRL + Shift + Enter to complete it correctly.

This formula goes to the topmost cell (E2), into which it extracts the lowest score. And then, you drag the formula down through two more cells to extract the 2nd and 3rd lowest scores. The ROWS(A$2:A2) function automatically generates n numbers, so you needn't type them anywhere in the sheet.
SMALL IF not zero formula

Formula 2. SMALL IF greater than zero with condition

To find the n-th smallest value greater than zero based on criteria, place additional criteria in this way:

{=SMALL(IF((values<>0) * (criteria_range=criteria), values), n)}

As an example, let's pull the bottom 3 scores for each subject individually. For this, we enter the subject names in E2 (Art) and F2 (Science) and use this formula to extract the smallest Art scores:

Drag the formula to the right, and you will get the smallest Science scores too:
SMALL IF greater than zero with criteria

Filter smallest values based on criteria

This solution only works in recent builds of Excel 365 where dynamic array functions are available.

Another way to get the n-th bottom value in Excel based on conditions is using the SMALL function together with FILTER. This works beautifully as a normal formula completed with the Enter key.

The formula's logic is the same as in the previous examples. The difference is that you use the FILTER function instead of IF to apply the criteria.

Formula 1. Find n-th bottom value with one condition

If there is just one condition to be met, you can get the nth smallest value with this formula:

SMALL(FILTER(values, criteria_range=criteria), n)

For our sample dataset, the formula goes as follows:

Where B2:B15 is the criteria range (list of subjects), C2:C15 are the values (scores), F2 is the criteria (the subject of interest) and E3 is the n-th smallest score to return.

SMALL FILTER formula with one condition

Formula 2. Get n-th smallest value with multiple criteria

To test multiple conditions, here's the formula to use:

SMALL(FILTER(values, (criteria_range1=criteria1) * (criteria_range2=criteria2)), n)

Assuming you are looking to find the n-th lowest score in a specific subject (G1) among students of a given school (G2), the formula is:

Where B2:B15 is the list of subjects, C2:C15 are the school types, D2:D15 are the scores, and F6 is the n-th smallest value to return.

SMALL FILTER formula with two conditions

Formula 3. Filter n-th smallest value with OR criteria

To filter the n-th smallest number when this or that condition is TRUE, the formula is:

SMALL(FILTER(values, (criteria_range1=criteria1) + (criteria_range2=criteria2)), n)

For instance, you can find the lowest score in humanitarian subjects (History or Literature) by using this formula:

Where B2:B15 is the list of subjects, C2:C15 are the scores, E2 and F2 are the subjects of interest, and E6 is the n-th smallest value.

SMALL FILTER formula with two OR criteria

That's how to use SMALL IF in Excel to find smallest values based on conditions. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel SMALL IF examples (.xlsx file)

You may also be interested in:

Sours: https://www.ablebits.com/office-addins-blog/2020/11/03/small-if-excel-smallest-value-criteria/

You will also be interested:

Listen to me, you lost consciousness yesterday and it's good that you managed to catch Alex, we brought you home and. Called a doctor, the tests showed a terrible thing. - What, I'm pregnant. - barely moving her lips, whispering very slowly. - You fool.



476 477 478 479 480