OmniDochow to fill in the parameters of Excel function: type, order and common errors
Explain clearly which types of values can be accepted by the parameters of the Excel function, why the order cannot be disordered, and how to troubleshoot common errors caused by the mismatch of parameter types and region sizes, with a set of check steps before and after filling in.
- main points of this article *
- Function parameters can be numbers, text, logical values, cell references, ranges, arrays, or the return result of another function.
- The order of parameters is determined by each function. Excel reads them by position and will not swap them according to your intention.
- Text parameters must be enclosed in English double quotes, otherwise they will be treated as names or references.
- The number of rows in each area of SUMIFS must be consistent. If the area size is different, #VALUE! will be returned directly.
- Before copying the formula, use absolute references to fix the areas that should not be changed to prevent parameters from shifting with their positions.
When you write a formula in Excel, it is often not the function name that is easy to get stuck, but what should be filled in parentheses. The area where VLOOKUP has finished writing but returns # Nhampar A Magi SUMIFS looks correct, but the calculated number does not match. Most of these problems lie in the parameters: the type is wrong, the order is reversed, or the size of the region is inconsistent.
This article is for readers who already know how to use simple formulas, but are prone to difficulties in the face of multi-parameter functions. It only talks about the parameters themselves and does not repeat the input steps in the getting started tutorial. After reading, you should be able to determine which type of each parameter should be filled in, know where to look up the order of the parameters, and quickly locate several common errors.
What kind of parameters can be used for functions in Excel? One sentence conclusion
Function parameters can be numbers, text, logical values, cell references, ranges, arrays, and the return results of other functions. What can be filled in specifically is determined by the role this position plays in the function, rather than Excel's unified rules.
| your situation | Recommended practices | reason |
|---|---|---|
| Fixed values are required, such as tax rates and coefficients | Write the number directly, or refer to a separately stored cell | The number is written in the formula and cannot be modified. It only needs to be changed in one place when it is put in the cell. |
| Need to match text, such as department name | The text is enclosed in English double quotes, such as "Sales Department" | Without quotes, Excel will treat it as a name or reference |
| A list of data needs to be counted. | Fill in the area, such as B2:B100 | Statistical functions require a set of numbers, and area is more reliable than cell by cell. |
| The parameters should be calculated according to the conditions | Nest another function, such as IF within TEXT | The return type of the inner function must comply with the requirements of the outer parameters |
| Parameters can be omitted | The optional parameters at the end can be left blank; the commas in the middle must be left blank. | The position is determined by the order of parameters, and skipping the intermediate parameters will lead to all subsequent dislocations. |
What types of parameters are there and how to write them?
Excel's parameter types can be divided into several categories, and they are written in different ways.
- Number: For example, 0.13, 12, can be entered directly, or it can be a formula that returns a number.
- Text: Must use English double quotes, such as "Completed". Most search functions are not case-sensitive, but spaces before and after the text will affect the matching results.
- logical value: TRUE and FALSE only. Fill in the last parameter of VLOOKUP with FALSE to indicate an exact match, or you can write 0 instead.
- Cell reference and range: A1 is a single reference, A1:A10 is a range, and Sheet2!B5 points to other sheets. When the worksheet name contains spaces, it needs to be enclosed in single quotes.
- Array and nested functions: Formulas that can return multiple values can be used as parameters, but the type required by the outer function must be consistent with the type returned by the inner function.
The order of parameters is determined by the function and cannot be filled in based on feeling.
Excel reads parameters positionally and does not adjust the order based on what you want to express. The order of parameters of each function is fixed, so you must confirm the specifications of the function before filling in.
Taking VLOOKUP as an example, the order is: search value, table area, column number, matching method. The column number refers to the column starting from the left column of the table area, not the column number of the worksheet. This is easy to get wrong.
SUMIF and SUMIFS are also easily confused:
- The order of SUMIF is: condition area, condition, summation area, and the summation area is placed last.
- The order of SUMIFS is: summation area, condition area 1, condition 1, with the summation area first.
There are two versions of the same function name with opposite parameter positions. You need to pay attention when rewriting from one to the other.
After entering the function name and opening bracket in the edit bar, Excel will display parameter prompts. Filling in the information one by one according to the prompts is more reliable than relying on memory.
Common misunderstandings and areas of confusion
Misunderstanding 1: Area sizes can be different. The number of rows in the summation area and condition area of SUMIFS must be the same, otherwise #VALUE! will be returned. For example, if the summing range is B2:B100 but the condition range is written as A2:A50, an error will be reported.
Myth 2: Text numbers and numbers are interchangeable. "100" is text and 100 is a number, which are not equal in most comparisons. Data exported from the system often has this condition and needs to be converted or cleaned first.
Misunderstanding 3: The parameters will be automatically aligned after copying the formula. Relative references will be offset with position. The area that needs to be fixed must use absolute references, such as $B$2:$B$100, otherwise the area will move quietly when copied downwards.
# Core comparison
The following table lists the parameter order and error-prone points of several commonly used functions for easy comparison.
| Function | Parameter order | Error-prone point |
|---|---|---|
| VLOOKUP | Look up values, table areas, column serial numbers, matching methods | The column sequence number is calculated from the left column of the area, not the worksheet column number |
| SUMIF | Conditional region, conditional region, summation region | The summation area is at the end, so it is easy to write to the front. |
| SUMIFS | Summation region, condition region 1, condition 1 | The number of rows in all regions must be the same. |
| INDEX | Area, line number, column number | Line number and column number refer to the relative position within the area |
# how to use it
- Before writing a formula, list the type required for each parameter: number, text, region, or reference.
two. Enter the function name and the opening parenthesis, fill in one by one according to the parameters, and add English double quotation marks to the text.
- After filling in, check that the number of rows in each range is the same, and confirm that the reference points to the expected cell.
- If the data is from PDF, you can export to Excel using the PDF editor of the OmniDoc desktop client. After export, check whether the form is aligned with the original text, and then fill in the area into the formula.
- If the header or description needs another language, you can upload the .xlsx file in omnidoc.top, select bilingual control or translation only, and check that the formula is still in the corresponding cell and the reference is not misplaced after download. The contents of machine translation should also be checked item by item.
# which method is suitable for you?
- only need to find data from one column, and the table structure is fixed : use the lookup class function, fill in the above order one by one, and confirm the column serial number. - Statistics based on multiple conditions : SUMIFS is preferred. The summation region is determined first, and then condition regions and conditions are added pair by pair. - often need to modify the value *: put the number into a separate cell, the formula refers to the cell, change one place.
# frequently asked questions (FAQ)
1. Why are the delimiters between parameters sometimes semicolons?
Parameter delimiters depend on the locale of the system, and some environments use semicolons. If the formula reports an error or automatically becomes text, check that the delimiter is consistent with the current environment.
2. The text parameter is clearly written, why is it not in effect?
In most cases, there is a lack of English double quotation marks or a space before and after the text. You can use TRIM to remove extra spaces before making a comparison.
3. # Namp An and # VALUE! What's the difference?
NCMA usually indicates that no match was found. # VALUE! It usually indicates that the parameter type is incorrect, or the size of the area is not consistent. The directions of the two are different, so don't mix them up.
4. Can parameters refer to data from other worksheets?
Yes, write the name of the worksheet! The form of the region, such as Sheet2regions B2VR B50. Single quotation marks are required when the worksheet name contains spaces.
# Summary
The key to the parameters of the Excel function is the type and order required for each location. Prompt for confirmation against the parameters before filling in. When reporting an error, priority is given to checking the type, order, and area size, and troubleshooting the parameters of the Excel function is much faster.
