The concept of a function in Excel. Using different types of arguments The argument of an excel function can be
Working with large formulas can be problematic even for experienced users. The most difficult thing is to understand someone else's design and understand how it works. Recently, as part of , I was asked to analyze several complex formulas, and it turned out that the formulas were really rewarding - I counted 7-8 IFs and about 5-6 other functions in one cell. In such situations, it is very important to determine what the argument of each function is. Therefore, I decided to write a short article about an important thing - the Excel argument and its role in calculations. And most importantly, I will describe in the article how convenient it is to find and highlight each of the arguments when writing huge formulas.
I think I need to first say a few words of banal theory.
Arguments are the quantities used in the calculations of functions/formulas. Arguments can be a number, text, or even a formula with other functions.
Arguments can be either mandatory (without which the formula will not work) or optional (without which the function will work by default), they are highlighted in square brackets.
WEEKDAY(date_in_numeric_format, [type])
Where “date_in_numeric_format” must be filled in, but [type] can be left blank, and even a semicolon is not necessary.
It is important to note that functions may not contain an argument.
TODAY()
Or they may have a variable number of arguments, like:
SUMIFS()
How to conveniently find and highlight function arguments?
To understand large, heavy formulas, it is very important to be able to isolate the arguments of nested functions. Even if you wrote this formula, I guarantee you that in six months you will not immediately understand it. To conveniently see the function argument, click on the tooltip below that appears when you enter a cell and the argument will be highlighted:
In the example, click on “the desired value” and in the formula itself it will be highlighted.
For convenience, I’m also attaching a GIF
But there are even heavier records. Such:
Or like this
The formulas are not mine, I hope the creators will not be offended. I’m sure many (myself included) have encountered mountains of more serious symbols. It can be extremely difficult to understand such structures without the ability to select them.
What else is important to say
With such constructions, it is very important to understand the formula in parts and highlight its argument. Excel's method of highlighting an argument is very convenient. I can also advise you to place each function on a new line using Alt + Enter - it also helps a lot - more details.
It is also important to note that selecting a function argument can be done after selecting any formula argument.
For example, you don’t understand how the function in the example works (you didn’t specifically fill in the 4th argument of the VLOOKUP).
You can click on any place in the formula with your mouse, after which a hint will appear for you which argument it is.
Functions represent the dependence of one element (result) on other elements (arguments,.. those inside :-)). This is kind of understandable. In order to use any function in, you must enter it as a formula (the nuances are described) or as part of a formula in a worksheet cell.
The sequence in which the symbols and arguments used in the formula must be located is called function syntax. All functions use the same syntax rules. If you violate these rules, Excel will display a message that there is an error in the formula and will not be friendly with you. But believe me, in Excel functions everything is quite the same type and once you figure it out once, on one or two functions, in other cases everything will be quite simple.
Syntax rules for writing functions
If a function appears at the very beginning of a formula, it must be preceded by an equal sign, as is the case at the beginning of any formula. I already talked about this in previous articles, but it’s not a sin to repeat it again.
After this, the name of the function is entered, followed immediately by a list of arguments in parentheses. The arguments are separated from each other by a semicolon ";". Parentheses allow Excel to determine where the argument list begins and ends.
Please note that the function entry must contain opening and closing brackets, and spaces cannot be inserted between the function name and the brackets. Otherwise, Excel will display an error message.
Arguments can be numbers, text, booleans, arrays, error values, or references. In this case, the parameters specified by the user must have values valid for this argument.
For example, the formula below sums the values in cells B2, B3, B4, B5, and E7, with some cells B2 through B5 represented as a continuous range.
Consider the function ROUND(arg1;arg2), which returns a number rounded to a specified number of decimal places and has two arguments:
arg1 – address of the cell with the number (or the number itself) to be rounded;
arg2 – the number of digits after the decimal point of the number after rounding.
To round the number 2.71828 found in cell A1 to one, two, or three decimal places and write the results of the calculations in cells B1, C1, and D1, respectively, proceed as follows.
- Enter the number 2.71828 in cell A1.
- Enter formulas of the following form in cells B1, C1 and D1:
ROUND(A1;1)
ROUND(A1,2)
ROUND(A1;3)
Arguments can be either constants or functions. Functions that are arguments to another function are called nested.
For example, let’s sum the values of cells A1 and A2, having first rounded these values to two decimal places:
SUM(ROUND(A1,2),ROUND(A2,2))
Here the ROUND function is nested as much as twice, but this is not scary, you can use it in Excel formulas up to seven nesting levels functions.
It is worth noting that there are functions in Excel that do not have arguments. Examples of such functions are PI (returns the value of π, rounded to 15 digits) or TODAY (returns the current date). When using such functions, you should put in the formula bar immediately after the function name empty parentheses without arguments. In other words, to get the value of the number p or the current date in cells, you should enter formulas like this:
TODAY()
Types of Excel Functions
For the convenience of the user when building formulas, functions in Excel are divided into categories: database and list management functions, date and time functions, financial, statistical, text, mathematical, logical.
Text functions are used for text processing, namely: searching for the necessary characters, writing characters to a strictly defined place in the text, etc.
By using Date and time functions you can solve almost any problem related to taking into account calendar dates or times (for example, calculating the number of working days for any period of time).
Logic functions are used to create complex formulas that, depending on the fulfillment of certain conditions, will implement various types of data processing. They are especially interesting, and we will talk about them in a separate article.
Excel is widely represented Mathematical functions and I have already given some examples.
The user also has a library at his disposal Statistical functions, with which you can search for the average value, maximum and minimum elements, etc.
Date: December 27, 2015 Category:Hello, friends! This is an introductory article about Excel functions, where I will tell you what functions are, function arguments, how to insert a function into a formula. And in the following posts we will understand what Excel functions are and how to use them correctly.
Functions in Excel are statements that perform more complex calculations than mathematical operators.. Some calculations cannot be performed without functions, so knowing them perfectly is the key to your success.
You can “nest” one function into another, copy formulas with functions (don’t forget about ). The main thing is to clearly understand how the function works, otherwise it may give incorrect results or . You may not notice this. And to understand how the functions work, read my posts about various functions and Microsoft help.
With the advent of each new version of Excel, the list of functions is updated. Developers add new, popular ones, and remove functions that are no longer relevant. In this blog, I will describe the features of Microsoft Excel 2013, but I will answer any questions about the features in other versions of the program. As always, ask them in the comments.
Excel function arguments
Function Arguments– this is the initial data for calculating the function. For example, for the SUM function (summation) is a list of numbers, cells or ranges of cells to be summed. Arguments are indicated in parentheses after the function name and separated by a semicolon (comma in the English version). Depending on the number of arguments, functions can be:
- No arguments– do not need arguments for calculation. For example, =PI() returns the number 3.1428.
- With one argument– you only need to enter one argument. For example =LOW(A1) – will convert all characters in the cell to lowercase A1.
- With multiple arguments– you need to enter a certain number of arguments, more than one. For example, the function =PSTR(A1,1,10) will return the first 10 characters from the string in the cell A1.
- With optional arguments– the function has arguments that are optional. For example, =VPR(“Ivanov”,A1:B30,2,0) will search for the surname "Ivanov" in the range A1:B30 and will return information about it. The last argument here is "Time Lapse"— indicates the search method; it is optional.
- With variable number of arguments– the number of arguments can vary. For example, =AVERAGE(A1,B3:B15,C2:F2)– will calculate the average value of numbers in the specified ranges. By listing cells separated by semicolons, you can specify a different number of arguments.
How to insert a function in Excel
To insert a function into a formula, you can use one of the following methods:
If you don't know the function name, use the Insert Function window. To call it, try one of the following methods:
After performing any of these operations, the Insert Function window will open. In the Search for function field, briefly describe what you need to do and click Search. In field Select function search results will appear. Click on the functions in the list and read their descriptions. If a suitable function is not found, rephrase the request and repeat the search.
When you have found a function, double-click on it and the arguments window will open. After filling them out, click OK, the program will calculate the result.
Window "Function Arguments" Once again, I state that we have covered an important and simple topic. Still simple! Practice inserting functions into the worksheet yourself and make sure it's not too difficult. However, these are very important skills. Their successful application, brought to automation, is the basis of the foundations. Next I will describe the work based on the practice from this article.
That's all about inserting functions into a sheet, and in the next article we will start looking at . See you on the blog pages!
The IF function is the most popular of Excel's Boolean functions; it returns one value if the specified condition results in TRUE and another value if the condition results in FALSE.
Description of the IF function
The IF function returns one value if the specified condition evaluates to TRUE, and another value if the specified condition evaluates to FALSE. For example, the formula =IF(A1>10,"More than 10","10 or less")
returns the string "Greater than 10" if the value in cell A1 is greater than 10, and "10 or less" if it is less than or equal to 10.
Syntax
=IF(logical_expression; [value_if_true]; [value_if_false])Arguments
log_expression value_if_truevalue_if_false
Required argument. Any value or expression that results in TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is 100, this expression evaluates to TRUE; otherwise, it evaluates to FALSE. This argument can use any comparison operator.
Optional argument. The value that is returned if logical_expression evaluates to TRUE. For example, if the given argument is the string "Within Budget" and the Boolean argument evaluates to TRUE, the IF function returns the text "Within Budget". If the logical_expression argument evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma after the logical_expression argument), the value 0 is returned. To display the word TRUE , use the logical value TRUE as this argument.
Optional argument. The value that is returned if logical_expression evaluates to FALSE. For example, if the given argument is the string "Over Budget" and the Boolean argument evaluates to FALSE, the IF function returns the text "Over Budget". If logical_expression evaluates to FALSE and value_if_false is omitted (that is, there is no comma after value_if_true), the IF function returns the logical value FALSE. If logical_expression evaluates to FALSE and value_if_false is empty (that is, there is only a comma after value_if_true), the IF function returns 0 (zero).
Notes
- To create more complex tests, you can use up to 64 nested IF functions as the value_if_true and value_if_false arguments (see Example 3 for the use of nested IF functions). You can also use the LOOKUP, VLOOKUP, LOOKUP, and SELECT functions to test multiple conditions (see Example 4 for how to use the LOOKUP function).
- If any of the arguments to the IF function are an array, the IF function evaluates all elements of the array.
- Excel provides additional features that you can use to analyze data using conditions. For example, to count the number of occurrences of a text string or number in a range of cells, use the COUNTIFS and COUNTIFS worksheet functions. To calculate the sum of values that fall within a range specified by a text string or numbers, use the SUMIF and SUMIFS functions.
Laboratory work No. 4. MS EXCEL FUNCTIONS.
Excel's built-in functions make it easy and quick to perform the calculations you need. Functions are special, pre-created formulas that allow you to perform complex calculations quickly and easily. Functions are used not only for direct calculations, but also for converting numbers, for example, for rounding, searching for values, comparing, etc.
Excel has over three hundred built-in functions. In the event that none of the built-in functions are suitable for solving the task at hand, the user has the opportunity to create his own (custom) function.
For ease of use, the functions are grouped into categories:
mathematical;
statistical;
date and time;
financial;
text;
checking properties and values;
work with the database.
Function structure: function name, opening parenthesis, list of arguments separated by semicolons, closing parenthesis. The function argument can be a number, text, logical value, array, error value, cell reference. Constants, formulas, or functions are also used as arguments. In each specific case, it is necessary to use the appropriate argument type (with the exception of functions such as PI(), TRUE(), which do not have arguments). Function input is preceded by =. When specifying arguments, remember the following rules : arguments are enclosed in parentheses; arguments are separated from each other by semicolons; the number of arguments should be no more than 30; Links, numbers, text, arithmetic or logical expressions, range and function names can be used as arguments. Entering links and range names is carried out in the same way as in formulas: directly from the keyboard or by clicking on the corresponding cell. Entering range names can also be done either manually or using the formula bar field's drop-down list. If a function is used as an argument, the function is called a nested function. In this case, the functions “inside” the structure are performed first, and then the external functions.
Function syntax
The following example of the ROUND function, which rounds a number in cell A10, illustrates the syntax of the function:
Function structure
1. The structure of a function begins with an equal sign (=), followed by the function name, an open parenthesis, a list of arguments separated by a semicolon, and a closing parenthesis.
2. The name of the function describes the operation that this function performs. To display a list of available functions, click a cell and press Shift+F3.
3. Arguments specify the values or cells used by the function.
4. A tooltip with syntax and arguments appears after entering the function.
The function argument is enclosed in parentheses. The opening parenthesis marks the beginning of the argument and is placed immediately after the function name. If you enter a space or other character between the name and the opening parenthesis, the cell will display the erroneous value #NAME? Some functions have no arguments, for example the function PI() simply returns the value of the constant “3.1415...” to the cell, and the function TODAY() inserts the current date into a cell. but even then the function must contain parentheses. However, even if the function has no arguments, empty parentheses must be written, otherwise Excel will throw an error.
An example of a formula for calculating the circumference of a circle containing the PI() function:
When multiple arguments are used in a function, they are separated from each other by a semicolon. For example, the following formula indicates that you need to multiply the numbers in cells A1, A3, A6: =PRODUCT(A1,A3,A6).
You can use up to 30 arguments in a function, as long as the total length of the formula does not exceed 1024 characters. However, any argument can be a range containing any number of worksheet cells. For example: =SUM(A2:A5,B4:B8).
Argument types
In the previous examples, all arguments were cell or range references. But you can also use numeric, text, and Boolean values, range names, arrays, and error values as arguments. Some functions return values of these types, which can later be used as arguments in other functions.
Numeric values
Function arguments can be numeric. For example, the function =SUM(24,987,49) sums the numbers 24, 987, 49:
Text values
Text values can be used as function arguments. For example:
TEXT(TDATE();"D MMM YYYY")
In this formula, the second argument to the TEXT function is text and specifies a pattern for converting the decimal date value returned by the TDATE(NOW) function into a character string. The text argument can be a character string enclosed in double quotes, or a reference to a cell that contains text.
Boolean values
Arguments to some functions can only accept the logical values TRUE or FALSE. A Boolean expression returns TRUE or FALSE to the cell or formula that contains the expression. For example:
IF(A1=TRUE;"Increase";"Decrease")&"price"
You can specify the name of the range as an argument to the function. For example, if the cell range A1:A5 is named "Debit", then you can use the formula to calculate the sum of the numbers in cells A1 through A5 =SUM(Debit)
Using Different Argument Types
You can use arguments of different types in one function. For example: =AVERAGE(Debit,C5,2*8)
An erroneous value is the result of a calculation using a formula not written according to Excel rules. The first character of the error value is #. The erroneous meaning ends with signs! or?. Excel has identified seven erroneous values, a brief description of which is given in Table. 1.17.1.
Table 1.17.1
| Wrong value | Probable cause of the erroneous value |
| #DIV/0! | The formula contains an explicit division by zero (for example, =A1/0). A reference to an empty cell or a cell containing 0 as a divisor is used. |
| #NAME? | The formula uses the name of a cell or range that is not included in the list of names in the dialog box menu InsertÞNameÞAssign a name. There was an error in the spelling of the cell or range name. Error in writing the function name. A colon (:) is missing from a cell range reference Text that is not enclosed in double quotes has been entered into a formula |
| #VALUE! | Using an invalid argument or operand type: Text was entered into the formula instead of a number or Boolean value (TRUE or FALSE). For an operator or function that requires a single value, a range is specified. A mathematical formula has been introduced in which a text value is used instead of a number (for example, ROUND(A1,text)) |
| #LINK! | Cells or ranges of cells referenced by formulas have been deleted or the contents of other copied cells (ranges) have been placed in those cells (ranges) |
| #NUMBER! | A function with a numeric argument uses an invalid argument The numeric value of the formula result is too large or too small to be represented in Excel |
| #N/A | One or more required arguments to a standard or custom worksheet function are not supplied |
| #EMPTY! | The formula specifies the intersection of ranges that actually have no cells in common. |
Functions can be entered into the worksheet manually directly from the keyboard. In this case, you need to pay attention to the syntax of functions and parentheses. Errors often occur when entering functions manually.
To create formulas with functions, you usually use the group Function Library tabs Formulas(Fig. 6.2).
Rice. 6.2. Formulas tab
1. Select the cell in which you want to enter the formula.
2. Click the button for the desired function category in the group Function Library and select the desired function.
3. In the window Function Arguments In the appropriate field(s), enter the function arguments. Cell references can be entered using the keyboard, but it is more convenient to select cells with the mouse. To do this, place the cursor in the appropriate field and select the required cell or range of cells on the sheet. To enter cell references as arguments, click Collapse dialog box next to the argument you want to enter, the dialog box will be temporarily hidden), select the cells on the worksheet, and then click Expand Dialog Box Text, numbers, and logical expressions as arguments are usually entered from the keyboard. As a tooltip, the window displays the purpose of the function, and at the bottom of the window a description of the argument in whose field the cursor is currently located is displayed. Please note that some functions have no arguments.
4. In the window Function Arguments click the button<OK>.
For example, in the table in Fig. 6.5 in cell A6 you need to create a formula to round to two decimal places the value in cell A5:
1. select cell A6;
2. click on the category button Mathematical in Group Function Library and select the ROUND function (Fig. 6.3);
Rice. 6.3. Selecting a function
3. in the window Function Arguments(Fig. 6.4) place the cursor in the field Number and on the sheet, select cell A4, then place the cursor in the field Number of digits and enter the number 2 from the keyboard;
4. Press the button
Rice. 6.4. Entering Function Arguments
To insert a function, it is not necessary to use the function category buttons in the group Function Library. You can use the Function Wizard to select the desired function. Moreover, this can be done when working in any tab.
1. Click the button Insert function in the formula bar.
2. In the window Function Wizard: Step 1 of 2(Fig. 6.5) in the drop-down list Category select a function category, then in the list Select function select a function.
3. Press the button OK or double-click on the name of the selected function.
Rice. 1.18.1 Window Function Wizards to select the category and type of function
Second step Function Wizards calls up a dialog box (Fig. 1.18.2) intended for generating arguments. Arguments are entered into fields with the name of the argument to the left and the type of the argument to the right. The names of required arguments are written in bold. To the right of each argument field is its current value. The current value of the function is displayed at the bottom of the window. Also at the bottom of the dialog box is information explaining the meaning of the argument and the current value of the function being entered. The button on the right side of the argument input fields is intended to collapse the dialog box and free up space for specifying links using the mouse.
The formation of the function ends by pressing a key<Enter> or buttons<OK>.
Rice. 1.18.2. Example of a dialog box for specifying function arguments
Creating formulas using the Sum button
To quickly perform some actions using functions without opening the Function Wizard, you can use the button Sum. This button, in addition to the group Function Library tabs Formulas(it's called there Autosum), also available in the group Editing tabs home.
To calculate the sum of numbers in cells located continuously in one column or one row, just select the cell below or to the right of the summed range and click the Sum button. For example, to calculate the sum of values in cells B2:B13 in the table in Fig. 6.8, select cell B14 and press the button Autosum.
Rice. 6.8. Adding using the Sum button
To confirm entering the formula, press the key Enter or press the button again Sum.
To calculate the sum of randomly located cells, select the cell in which the sum should be calculated, click on the button Sum, and then select the cells and/or ranges of cells to be summed on the worksheet. To confirm entering the formula, press the key Enter or press the button again Sum.
In addition to calculating the amount, the button Sum can be used in calculating the average value, determining the number of numerical values, finding the maximum and minimum values. In this case, you need to click on the button arrow and select the required action:
Average – calculation of the arithmetic mean;
Number – determination of the number of numerical values;
Maximum – finding the maximum value;
Minimum – finding the minimum value.
For example, to calculate the average value in cells B2:B13 in the table in Fig. 6.9, select cell B14, then click on the arrow button Sum and select the Medium action.
Rice. 6.9. Calculating an average using the Sum button
