Fix the #NUM! Error in the SMALL Function Formula in Excel
Question details
The user encounters a #NUM! error on a Windows PC when using the SMALL function combined with an array constant, even though the same formula calculates correctly on a mobile device.
- Product
- Spreadsheet
- Device & OS
- Windows PC
- Scenario
- Calculating the smallest values from a dataset using the SMALL function and an array constant like {1,2,3,4,5}.
- Observed behavior
- The formula successfully evaluates on a mobile phone but returns a #NUM! error when opened on a Windows PC, indicating potential software version or data validation issues.
Before attempting to fix the formula, verify that the cell range referenced in your SMALL function contains actual numeric data and does not consist of text formatted as numbers.
Update Your Spreadsheet Software
Cross-device compatibility issues often stem from running an outdated software version on your PC that does not fully support certain array formula behaviors.
Modern spreadsheet applications handle array constants and dynamic arrays natively. If your formula works on a mobile app but fails on a PC, your desktop application may be out of date.
Launch your spreadsheet program on your Windows PC and navigate to the 'File' menu, then select 'Account' or 'Help'.
Click on 'Update Options' and select 'Update Now' to check for the latest version.
Allow the updates to install completely, then restart the application and open your file to see if the #NUM! error is resolved.
Verify Sufficient Data Points in Your Reference Range
The SMALL function returns a #NUM! error if the array constant requests more values than are available in the dataset.
Use WPS Spreadsheet to Handle Array Formulas Without Errors
WPS Spreadsheet provides robust, modern support for advanced mathematical and statistical functions. You can seamlessly use the SMALL function alongside array constants across both PC and mobile devices without running into version-related #NUM! errors.
- 1. Open WPS Spreadsheet: Launch WPS Office on your computer and open a new or existing spreadsheet.
- 2. Select target cells: Click on the cell or range of cells where you want the smallest values to appear.
- 3. Enter the SMALL formula: Type your formula using an array constant, for example: =SMALL(A1:A20, {1,2,3,4,5}).
- 4. Execute the calculation: Press Enter. WPS Spreadsheet handles modern array formulas natively, automatically returning your expected numeric values.
Frequently Asked Questions
Why does the SMALL function return a #NUM! error?
The #NUM! error in the SMALL function typically occurs when the 'k' value (the position to return) is greater than the total number of data points in the selected range, or if the referenced range contains no valid numeric values at all.
Can cell formatting cause a #NUM! error in my formulas?
Yes. If your numbers were imported from another system or entered with apostrophes, they might be stored as text. The SMALL function ignores text, meaning it will think the range is empty and return a #NUM! error. You must convert these text strings into standard numbers.
How do array constants work with the SMALL function?
An array constant like {1,2,3} used as the 'k' argument instructs the program to return the first, second, and third smallest values simultaneously. This requires that the spreadsheet software supports dynamic arrays or is entered as a legacy array formula using Ctrl+Shift+Enter.
