Some of the suitability of small functions can be found in financial analysis, where it's used to find the smallest value in a given set of values in Excel. For instance, if the entire salesmen in an industry are given the same sales target, Excel's small function can help to find which person hits the sales target in the shortest time of the year.
Now, how can you also harness the power of the Excel small function to uncover valuable insights effortlessly? This is what we'll take you through in this article.
Golden Fact to Know About Excel Small Function
The small function in Excel is characterized as a statistical function.
Small functions retrieve a numeric value based on its position in a given list of values when sorted.
If the given or supplied value is non-numeric, then the Excel small function will return #value! Error.
Excel's small function is known to be responsible for retrieving the nth smallest value from a supplied set of values in the spreadsheet.
If the given value of n is < the numerical value 1 or > the value in the supplied array, then the small function in Excel will be returning #NUM! error.
Also, if the supplied array is blank, then the small function in Excel will be returning #NUM! error.
Parameters of Excel Small Function
Parameters and arguments accepted by Excel small function include;
Array: array is the range at which you want the small function to return the nth smallest value.
Nth position: nth position is an integer that specifies the position from the value, the nth position - which is the smallest value.
How to Use Small Functions in Excel
Below are the steps to follow when using Excel small function;
The first thing to do is to enter the desired small function formula ( this will be explained in detail in the coming subtopic) in the required cell. You'll get a return value on the arguments supplied.
Open the Excel small function formula spreadsheet's dialog box manually and input the logical values to attain a return value.
Navigate to the top toolbar and click on the More Function tab to select a statistical function from the displayed menu.
Click on the small option. The small formula Excel dialog box will open where you can put the argument values to obtain a return value.
Return value
The return value is a numeric value, which is the nth smallest value in the array. It's important to note that if the nth position is a larger value than the number of values in the array, the small function you'll get is going to be #NUM! Error. Also, if the supplied array is blank, the small function will return a #NUM error.
Applications of small function
Excel's small function can be used for various applications and purposes within the spreadsheet. We've listed some of the functions below, keep reading;
To extract multiple matches into several columns.
Sum bottom n values with criteria.
To Highlight the bottom values of the supplied data.
To get the sum of the bottom n values.
To find the lowest n values.
To sort numbers in ascending or descending order.
To extract multiple matches into several rows.
How to Use Small Functions in Excel With Practical Examples
Here are some examples of excel small functions below. These examples will help you explore using the small function formula in Excel.
Example #1
Below are the scores of the students in an examination. From the below-given data, find the smallest and the fourth smallest scores.
Now, the formula to find the smallest value in the given list will be =SMALL(B2:B11, 1) or =MIN(B2:B11).
However, the MIN function formula cannot be used to find the 2nd, 3rd, and 4th smallest numbers. Only a small function formula can kth position number.
Thus, to find the 4th smallest number, we need to specify the number in the kth argument to get the score or number we are looking for from the supplied set.
The formula is =SMALL(B2:B11, 3), and the result below is obtained.
Example #2
Below is the data obtained from a high school marathon race. From the list, the champ must be determined. Data include names, start time, and end time.
From this list, we need to determine who has taken the least time to complete the race.
Step 1: Find the total time taken
The total time taken to complete the race is derived by deducting the start time from the end time. Check out the image below.
Step 2: Apply the small function to get the champion.
Check out the formula and the result obtained below;
Small function in Excel - Common Problem
Small functions are an important feature of Excel which can help in various aspects effectively. However, there are some common problems that one may encounter while using it. Small functions sometimes return the wrong value, or the return value is nothing but an error, such as #NUM! Error.
Users may experience this error even if the supply value of n is between 1 and the supplied array value numbers. One of the things that causes this is maybe when you've included text representation of numbers within the given array. The small function ignores text values and only recognizes numerical values. Just change all the array values into numeric ones when you face this error.
WPS - The Best Excellent Alternative
Despite the effectiveness of Microsoft Excel, we can boldly assure you that the WPS office suite can carry out each of its functions. The function discussed above can smoothly be carried out on wps spreadsheet just as if you are doing it on Microsoft Excel.
However, let's check out what WPS Office is and how to download it;
What's the WPS office suite?
WPS is a free all-in-one office software, which was established in 1989 by Kingsoft Office, currently has more than 500M users worldwide with about 4.8 stars reviews. Here are some common benefits of the software;
Pros of WPS
Rich functions, it has a lot of features including powerful writing, presentation, spreadsheets, PDF, resume assistants, smart forms, screen recording, file repair, and other small tools integrated into the suite.
All these features, i.e. Word, Excel, PPT, and a powerful PDF toolkit are free to use.
Rich templates store, which contains various free and paid templates for Word, PPT, and Excel.
WPS supports Linux systems, Android, and iOS systems. All you need to do is log in to the same account if you want to use WPS office products on different systems, and all files will be synchronized.
The light weight of the product is only 200M, and it occupies a small amount of computer memory.
It's safe and stable, rarely crashes, and pays a lot of attention to users' privacy and security. These are just a few of WPS Office suite benefits.
How to Download WPS Office
Here are the steps involved in downloading and installing WPS software on your PC;
Visit the website https://www.wps.com/ to download the latest version of WPS Office.
Click the free download button.
When the download completes, open the WPS Office inste.exe file from the downloads list in the lower-left corner of the interface.
The WPS office wizard dialog appears. Read and review the WPS office license agreement and privacy policy. Clicking the Install Now button will instantly take you to finish the installation with just one click.
WPS is finally installed on your PC.
FAQS
What's the maximum number of values a small function can return?
The Excel SMALL function can return a maximum of one value. It is designed to retrieve the kth smallest value from a dataset, where "k" is specified as an argument.
How do you declare an empty cell array?
When you have data to put into a cell array, create the cell array using the cell array construction operator, {}. Like all MATLAB® arrays, cell arrays are rectangular, with the same number of cells in each row. You can use the {} to create an empty 0-by-0 cell array.
How does a small function deal with empty cells in arrays?
Empty Cells Ignored: SMALL ignores empty cells in the data array during computation.
Non-Empty Values Considered: The function only considers non-empty numeric values for ranking.
Sequential Ranking: If empty cells exist within the kth smallest range, SMALL ranks the non-empty values sequentially for accurate results.
How do I create a dynamic array in Excel?
A dynamic array formula is inserted into one cell and finished with a regular enter keystroke. To complete an old-fashioned array formula, just press ctrl + shift + enter. New array formulas spill too many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
Summary
The functionality of the Excel small function is limitless, it continues. Utilize its inside conditional formatting to highlight the smallest values. You can also use it to play around with dates and times, the importance does not stop here as you can also combine it with the IF function to perform conditional sorting.
So, folks, this article explains important features you should know about Excel's small function and WPS Office suite as the best excellent alternative to Microsoft Excel.
WPS Office suite is emphasized as a versatile tool consistent with various Microsoft Office-native formats. It provides seamless document integration, preserving formatting and information. Considering its powerful features and file support, it is recommended to download the WPS Office suite for a comprehensive digital workspace adventure.
