Number of function arguments if microsoft excel applications. What is a function in Excel? Function description if

Excel functions- these are special, pre-created formulas that allow you to easily and quickly perform complex calculations. They can be compared to special keys on calculators designed to calculate square roots, logarithms, etc. Excel has several hundred built-in functions that perform a wide range of different calculations. Some functions are the equivalent of long mathematical formulas that you can do yourself. And some functions cannot be implemented in the form of formulas. The functions consist of two parts: function name And one or more arguments. Function name , For example SUM, - describes the operation that this function performs. Arguments specify the values or cells used by the function. In the formula below: SUM is the name of the function; B1:B5 - argument. This formula sums the numbers in cells B1, B2, B3, B4, B5.

=SUM(B1:B5)

Equal sign at the beginning of the formula means that it is the formula that has been entered, and not the text. If the equal sign is missing, Excel will treat the input simply as text. Function argument enclosed in round brackets . 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. Even then, the function must contain parentheses:

=C5*PI()

When multiple arguments are used in a function, they are separated from each other 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 range containing an arbitrary number of sheet cells. For example:

=SUM(A2:A5,B4:B8)

In the previous examples, all arguments were cell or range references . But you can also use as arguments numeric, text and boolean values, range names, arrays and error values. Some functions return values of these types, which can later be used as arguments in other functions. Function arguments can be numeric. For example, the SUM function in the following formula adds the numbers 24, 987, 49:

=SUM(24,987,49)

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.

Arguments of some functions can only take logical values TRUE or LIE. Boolean expression returns value TRUE or LIE into the cell or formula that contains this 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 range of cells A1:A5 is named "Debit" (Insert - Name - Assign), then to calculate the sum of the numbers in cells A1 to A5 you can use the formula:

=SUM(Debit)

You can use arguments of different types in one function:

=AVERAGE(Debit,C5,2*8)

The logical IF operator in Excel is used to record certain conditions. Numbers and/or text, functions, formulas, etc. are compared. When the values meet the specified parameters, one entry appears. They don’t answer - another one.

Logical functions are a very simple and effective tool that is often used in practice. Let's look at it in detail using examples.

Syntax of the IF function with one condition

The syntax of an operator in Excel is the structure of a function and the data required for its operation.

IF (logical_expression;value_if_true;value_if_false)

Let's look at the syntax of the function:

Boolean_expression– WHAT the operator checks (text or numeric cell data).

Value_if_true– WHAT will appear in the cell when the text or number meets the specified condition (true).

Value if_false– WHAT will appear in the column when the text or number does NOT meet the specified condition (false).

Example:

The operator checks cell A1 and compares it with 20. This is a "logical_expression". When the contents of the column are greater than 20, the true inscription “greater than 20” appears. No – “less than or equal to 20”.

Attention! Words in the formula must be placed in quotation marks. So that Excel understands that it needs to output text values.

One more example. To gain admission to the exam, students in the group must successfully pass the test. We will enter the results into a table with columns: list of students, test, exam.

Please note: the IF statement must check not the digital data type, but the text one. Therefore, we wrote in the formula B2 = “credit.” We put it in quotes so that the program recognizes the text correctly.

IF function in Excel with multiple conditions

Often in practice, one condition for a logical function is not enough. When you need to take into account several decision-making options, we stack IF statements inside each other. Thus, we will have several IF functions in Excel.

The syntax will look like this:

IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, value_if_false))

Here the operator checks two parameters. If the first condition is true, then the formula returns the first argument - true. False – the operator checks the second condition.

Examples of several conditions of the IF function in Excel:

Table for analyzing academic performance. The student received 5 points – “excellent”. 4 – “good”. 3 – “satisfactory”. The IF operator checks 2 conditions: the value in cell 5 and 4 is equal.

Expanding functionality using the “AND” and “OR” operators

When you need to test multiple true conditions, the AND function is used. The gist is this: IF a = 1 AND a = 2 THEN the value in ELSE the value of c.

The OR function tests condition 1 or condition 2. As soon as at least one condition is true, then the result will be true. The gist is this: IF a = 1 OR a = 2 THEN the value in ELSE the value of c.

The AND and OR functions can test up to 30 conditions.

An example of using the AND operator:

Example of using the OR function:

How to compare data in two tables

Users often need to compare two tables in Excel to see if they match. Examples from “life”: compare the prices of goods in different deliveries, compare balances (accounting reports) for several months, the performance of pupils (students) of different classes, in different quarters, etc.

To compare 2 tables in Excel, you can use the COUNTIF operator. Let's look at how to use the function.

For example, let's take two tables with the technical characteristics of different food processors. We decided to highlight the differences with color. Conditional formatting solves this problem in Excel.

Initial data (tables with which we will work):

Select the first table. Conditional formatting – create a rule – use a formula to determine which cells to format:

In the formula bar we write: =COUNTIF (compared range; first cell of the first table) = 0. The range being compared is the second table.

To enter a range into a formula, simply select its first cell and the last one. "=0" means a command to search for exact (not approximate) values.

We select the format and set how the cells will change if the formula is followed. It's better to fill it with color.

Select the second table. Conditional formatting – create a rule – use a formula. We use the same operator (COUNTIF).

Here, instead of the first and last cells of the range, we inserted the column name that we assigned to it in advance. You can fill out the formula in any of the following ways. But with a name it’s easier.

Today we will look at the function IF.

The IF function is often used in Excel to solve many problems. It is very useful to know her. In this article we will try to tell you about its work using simple examples; it is enough to understand the construction of the IF function once and you will be able to use it in the most complex cases.

IF function checks whether a condition is true and returns one value if it is true and another value if it is not.

IF Function Syntax very simple:

IF(log_expression ; [ value_if_true]; [value_if_false])

log_expression is any value or expression that, when evaluated, evaluates to TRUE or FALSE.

What does it mean? An expression evaluates to TRUE if the expression is true.

In this part, you need to check the expression for consistency.

For example:

IF(A1=10; [value_if_true]; [value_if_false]) - if A1 is equal to 10, then the expression A1=10 will give the value TRUE, and if not equal to 10, then FALSE

Another example

IF(A1>30; [value_if_true]; [value_if_false]) - if the number in cells A1 is greater than 30, then A1>30 will return TRUE, and if less, then FALSE

Another example

IF(C1=”Yes” ; [value_if_true]; [value_if_false]) - if cells C1 contain the word “Yes”, then the expression will return the value TRUE, and if not, then C1=”Yes” will return FALSE

IF(log_expression ; [ value_if_true]; [value_if_false])

value_if_true, value_if_false– as their name suggests, this is what needs to be done depending on what the expression log returned: TRUE and FALSE

Example of using the IF function in Excel

Let's look at the use of the IF function using a practical example. We have an orders table that we used when reviewing the job. We need to fill out the column for Bucket orders (the picture incorrectly says “Table Orders”), that is, we need to select only orders with Buckets. This can be done in various ways, but we will use the IF function to show how it works with an example. (see picture)

To solve the problem, we will write a formula using the IF function

IF(A3="Bucket";D3,"-")

As you may have noticed, the arguments of the IF function are separated by semicolons.

So, the first argument (log expression) A3="Bucket" checks whether cell A3 contains the word "Bucket", if it does, then the second argument of the IF function is executed ( value_if_true), in our case this is D3 (i.e. the cost of the order), if cell A3 is not equal to the word “Bucket”, then the third argument of the IF function is executed ( value_if_false), in our case it is “-” (i.e. a dash will be written).

Thus, the value D3, i.e. the number 240, will appear in cells E3.

The purpose of the logical “if” function in the spreadsheet editor Microsoft Office Excel is to check the truth of the expression passed to it. Depending on the result of this check, the function returns one of the two values passed to it for this purpose. Each of the three parameters - the condition and the two results returned - can also be comparison functions, which allows you to compare any number of arguments.

You will need

Basic skills in working with Excel functions.

Instructions

Use the logical and operator to increase the number of arguments compared using the if function. It will allow you to use more comparison operations in cases where the truth of all comparison operations listed in the arguments is necessary. For example, if this function should return one if the value in cell A1 is greater than the value in cell A5, and the value of B1 is the same as the value of B3, then the “if” function can be written as follows: IF(AND(A1>A5;B1=B3 ;1;2). The number of arguments to the “and” function cannot be more than 30, but each of them can itself contain the “and” function, so you have the opportunity to create a nesting doll from functions of any reasonable nesting level.

Sometimes, instead of a necessary condition, you need to check a sufficient condition. In such cases, instead of the "and" function, expand the number of arguments using the "or" function. Let's say you want the if function to return one when either the value in cell A1 is greater than the value in cell A5, or the value of B1 is the same as the value of B3, or the value of A4 is a negative number. If none of the conditions are met, the function should return zero. This construction of three compared and two returned arguments of the “if” function can be written as follows: IF(OR(A1>A5;B1=B3;A4

Combine “and”, “or” and “if” functions at different levels of nesting to get the optimal algorithm for comparing the required number of arguments. For example: IF(OR(A1>A5,IF(AND(A7>A5,B1

Use the second and third arguments of the if function (return values) to increase the number of parameters to compare. Each of them can contain seven levels of nesting with “and”, “or” and “if” functions. At the same time, do not forget that the comparison operations you put in the second argument will be checked only if the comparison operation in the first argument “if” returns the value “true”. Otherwise, functions written to the position of the third argument will be checked.

Excel function is a predefined formula that returns a result based on predefined arguments (values).

Excel has many interesting functions that can significantly save time when calculating sums; maximum, average and minimum values; data counting, etc. For the functions to work correctly, you must follow the recording rules - FUNCTION SYNTAX.

Syntax of functions (formulas):

Standard function syntax has an equal sign (=), a function name (such as SUM, IF, VLOOKUP, etc.), and required arguments. The arguments contain information that is needed for the calculation. For example, the “SUM” function below adds up the values in the range B1:B10.

How to work with function arguments in Excel

Excel has many useful functions with their own unique set of arguments. There are even those that do not have a single argument, such as the functions “TODAY()” (Returns the current date) and “PI()” (returns the number 3.14159265358979 - the mathematical constant “pi” with an accuracy of 15 digits).

You can use the dialog box to insert a function Function Wizard. You can open this window in one of the following ways:

the Insert Function button located in the Formulas group Function Library.
Click the Insert Function button to the left of the formula bar.
Click Shift+F3.

Frequently used functions are remembered quite quickly and using them is not difficult, but if you have forgotten or do not know its name, you can use the search field and click on the “Find” button. Once the function has been found, click on “OK” and the “Window” will open. Function Arguments”, in which you can familiarize yourself with the arguments of this function.

When entering a function manually (without using the window Function Wizard) to open the dialog box “ Function Arguments” use the keyboard shortcut Ctrl+A. It should be noted that this combination does not work after entering any arguments to this function.

Keyboard shortcut Ctrl+Shift+A fills the function with dummy arguments. This can be useful if you don't yet know what data to fill the function with. The formula will return an error and the arguments should be replaced later. For example, when entering the function “=SUMIF”, hold down Ctrl+Shift+A, then we get this result: =SUMMIF(range,criteria,sum_range)