In lesson No. 4 we already made our first acquaintance with Excel functions. Now it's time to take a closer look at this powerful spreadsheet tool.
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.
Function syntax
Functions consist of two parts: the function name and one or more arguments. A function name, such as SUM, describes the operation that the 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)
An equal sign at the beginning of a formula means that it is the formula that has been entered, not the text. If the equal sign is missing, Excel will treat the input simply as text.
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. Even then, the function must contain parentheses:
=C5*PI()
Using Arguments
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 SUM function in the following formula adds the numbers 24, 987, 49:
=SUM(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. Boolean expression returns TRUE or FALSE to the cell or formula containing 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 cell range A1:A5 is named "Debit" (Insert-Name-Assign), then you can use the formula to calculate the sum of the numbers in cells A1 through A5
=SUM(Debit)
Usage various types arguments
You can use arguments of different types in one function. For example:
=AVERAGE(Debit,C5,2*8)
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 should be located is called function syntax. All functions use same rules syntax. 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.
Let's look at 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 that’s not a big deal, 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 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.
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 my training, 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.
Excel argument. 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;)
Where “date_in_numeric_format” must be filled in, but it can be omitted, 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 splitting each function into new line using Alt + Enter - also helps a lot - more details here.
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.
From this tooltip you can go to any of the arguments by clicking on it
You can also go to help by clicking on the name of the formula itself, in this case VLOOKUP.
Good luck analyzing your own and other people’s formulas! Write your comments.
(Visited 523 times, 1 visits today)
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 different types links). The main thing is to clearly understand how the function works, otherwise it may give an incorrect result or an error. 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 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 functions Microsoft Excel 2013, but I will answer all questions about functions 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 are separated by a semicolon (in the English version - a comma). 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 cell A1 to lowercase form.
- 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 cell A1.
- With optional arguments– the function has arguments that are optional. For example, =VLOOKUP("Ivanov";A1:B30;2;0) will search for the surname "Ivanov" in the range A1:B30 and return information about it. The last argument here - “Interval View” - indicates the search method; it is not necessary to specify it.
- 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. The search results appear in the Select a function field. 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 worksheet, but in the next article we'll start looking at text functions. See you on the pages of the OfficeLEAST.com blog!
Excel contains many built-in functions that can be used for engineering, statistical, financial, analytical and other calculations. Sometimes the conditions of the tasks require a more flexible tool to find a solution, then macros and user functions come to the rescue.
An example of creating your own custom function in Excel
Similar to macros, custom functions can be created using VBA language. To implement this task, you must perform the following steps:
- Open the VBA language editor using the ALT+F11 key combination.
- In the window that opens, select the Insert item and the Module sub-item, as shown in the figure:
- A new module will be created automatically, and a window for entering code will appear in the main part of the editor window:
- If necessary, you can change the name of the module.
- Unlike macros, whose code must appear between the Sub and End Sub statements, user-defined functions are denoted by Function and End Function statements, respectively. A user-defined function includes a name (an arbitrary name that reflects its essence), a list of parameters (arguments) with a declaration of their types, if required (some may not accept arguments), a return value type, a function body (code reflecting the logic of its operation) , as well as the End Function statement. An example of a simple user-defined function that returns the names of the day of the week depending on specified number, is shown in the figure below:
- After entering the code presented above, you must press the key combination Ctrl+S or the special icon in the upper left corner of the code editor to save.
- To use the created function, you need to return to the Excel spreadsheet editor, place the cursor in any cell and enter the name of the custom function after the “=” symbol:
Excel's built-in functions include explanations of both the result they return and the arguments they take. This can be seen in the example of any function by pressing the hotkey combination SHIFT+F3. But our function doesn't have a form yet.
To document custom function, you must perform the following steps:
- Create a new macro (press the key combination Alt+F8), in the window that appears, enter an arbitrary name for the new macro, click the Create button:
- As a result, a new module will be created with a template limited by the Sub and End Sub operators.
- Enter the code as shown in the image below, specifying the required number of variables (depending on the number of arguments to the user-defined function):
- A text string with the name of the user-defined function should be passed as “Macro”, a variable of type String with the text describing the return value should be passed as “Description”, an array should be passed as “ArgumentDescriptions” type variables String with texts describing the arguments of the user-defined function.
- To create a custom function description, you only need to run the module created above once. Now, when calling a user-defined function (or SHIFT+F3), a description of the returned result and variable is displayed:
It is not necessary to create function descriptions. They are necessary in cases where custom functions will be frequently used by other users.
Examples of using custom functions that are not in Excel
View of the original data table:
Each employee is entitled to 24 days off with payment S=N*24/(365-n), where:
- N – total salary for the year;
- n – number holidays per year.
Let's create a custom function for calculation based on this formula:
Example code:
Public Function Otpusknye(summZp As Long, holidays As Long) As Long
If IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then
Otpusknye = "Non-numeric data entered"
Exit Function
ElseIf holidays
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 Excel, you should enter it as a formula (the nuances are described here) 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 The following describes the rules that must be followed to correctly and optimally construct a formula using one or more 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.Excel function 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.
Excel function arguments Let's consider the operation of the ROUND(arg1;arg2) function, which returns a number rounded to a specified number of decimal places and has two arguments: arg1 – the address of the cell with the number (or the number itself) that needs 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, because Excel formulas can be used 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 empty parentheses without arguments in the formula bar immediately after the function name. In other words, to get the value of the number p or the current date in the cells, you should enter formulas of the following form: =PI() =TODAY() Types of Excel functions For the convenience of the user when building formulas, functions in Excel are divided into categories: database management functions and lists, 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. Using the 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). Logical 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 has a wide range of Mathematical functions, and I have already given some examples. The user also has at his disposal a library of Statistical functions, with which you can search for the average value, maximum and minimum elements, etc.
Function IF (IF) in Excel this is great tool to test conditions for TRUE or FALSE. If the values of your calculations are equal to the specified parameters of the function as TRUE, then it returns one value, if FALSE, then another.
What does the function return?
The value you specify when two conditions are true is TRUE or FALSE.
Syntax
=IF(logical_test, , )– English version
=IF(logical_expression; [value_if_true]; [value_if_false]) – Russian version
Function Arguments
- logical_test (logical_expression) is the condition you want to test. This function argument must be logical and evaluate to FALSE or TRUE. The argument can be either a static value or the result of a function or calculation;
- ([value_if_true])– (optional) – this is the value that the function returns. It will be displayed if the value you are testing meets the TRUE condition;
- ([value_if_false])– (optional) – this is the value that the function returns. It will be displayed if the condition you are testing matches the FALSE condition.
Additional information
If function in Excel examples with multiple conditions
Example 1: Testing a simple numeric condition using the IF function
When using the function IF (IF) in Excel, you can use various operators to check the status. Here is a list of operators you can use:
Below is a simple example of using the function to calculate student grades. If the sum of points is greater than or equal to “35”, then the formula returns “Passed”, otherwise it returns “Failed”.
Example 2: Using a Nested IF Function to Test the Condition of an Expression
The function can accept up to 64 conditions at a time. Although it is not practical to create long nested functions, in rare cases you can create a formula that has multiple conditions sequentially.
In the example below we are checking two conditions.
- The first condition checks whether the sum of points is less than 35 points. If this is TRUE, then the function will return “Failed”;
- If the first condition is FALSE and the sum of points is greater than 35, then the function checks the second condition. If the total score is greater than or equal to 75. If this is true, then the function returns the value “Excellent”, in other cases the function returns “Pass”.
Example 3: Calculate the amount of sales commission using the IF function in Excel
The function allows you to perform calculations with numbers. Good example usage – calculation of sales commission for a sales representative.
In the example below, the sales representative:
- does not receive commissions if sales volume is less than 50 thousand;
- receives a commission of 2% if sales are between 50-100 thousand
- receives 4% commission if sales volume exceeds 100 thousand.
=IF(B2<50,0,IF(B2<100,B2*2%,B2*4%)) – English version
=IF(B2<50;0;ЕСЛИ(B2<100;B2*2%;B2*4%)) – Russian version
In the formula used in the example above, the calculation of the commission amount is performed in the function itself IF. If the sales volume is between 50-100K, then the formula returns B2 * 2%, which is a 2% commission based on the sales volume.
Example 4. Using logical operators (AND/OR) in the IF function in Excel
You can use Boolean operators (AND/OR) inside a function to test multiple conditions at the same time.
For example, let's say you have to select students for scholarships based on grades and attendance. In the example below, a student is only eligible if they have scored more than 80 points and have more than 80% attendance.
You can use a function together with a function IF (IF) to first check whether both of these conditions are met or not. If the conditions are met, the function returns “Eligible”, otherwise it returns “Not Eligible”.
The formula for this calculation is:
=IF(AND(B2>80,C2>80%),”Yes”,”No”)– English version
=IF(AND(B2>80;C2>80%);”Yes”;”No”) – Russian version
Example 5. Convert errors to “0” values using the IF function (IF)
Using this function, you can also remove cells containing errors. You can convert error values to spaces or zeros or any other value.
The formula for converting errors in cells is as follows:
=IF(ISERROR(A1),0,A1)– English version
IF(ISERROR(A1),0,A1)– Russian version
The formula returns “0” if there is an error in the cell, otherwise it returns the value of the cell.
NOTE. If you are using Excel 2007 or versions after it, you can also use the IFERROR function for this.
You can handle empty cells in the same way. In case of empty cells, use the ISBLANK function, in the example below:
=IF(ISBLANK(A1),0,A1)– English version
=IF(EMPLANTY(A1),0,A1)– Russian version
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 talk about her work on 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. It 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.
