EXCEL formulas with examples - Instructions for use. Differences between absolute, relative and mixed references

30.10.2019 Adviсe

In order to understand how to use this program, you need to consider EXCEL formulas with examples.

If you place the mouse cursor on any cell and click on the “select function” item, the function wizard appears.

With its help, you can find the required formula as quickly as possible. To do this, you can enter its name and use the category.

Excel is very convenient and easy to use. All functions are divided into categories. If the category of the required function is known, then its selection is carried out according to it.

If the function is unknown to the user, he can set the category “full alphabetical list”.

For example, the task is given to find the SUMIFS function. To do this, you need to go to the category of mathematical functions and find the one you need there.

VLOOKUP function

Using the VLOOKUP function, you can extract the necessary information from tables. The essence of vertical scanning is to find the value in the leftmost column of a given range.

After which the total value is returned from the cell, which is located at the intersection of the selected row and column.

The calculation of the VLOOKUP can be traced using an example that provides a list of surnames. The task is to find the last name using the given number.

Using the VLOOKUP function

The formula shows that the first argument of the function is cell C1.

The second argument A1:B10 is the range in which the search is carried out.

The third argument is the ordinal number of the column from which the result should be returned.

Calculate a given last name using the VLOOKUP function

In addition, you can search for a surname even if some serial numbers are missing.

If you try to find a last name from a non-existent number, the formula will not give an error, but will give the correct result.

Searching for a last name with missing numbers

This phenomenon is explained by the fact that the VLOOKUP function has a fourth argument, with which you can set interval viewing.

It has only two meanings - “false” or “true”. If the argument is not specified, it is set to true by default.

Rounding numbers using functions

The program's functions allow you to accurately round any fractional number up or down.

And the resulting value can be used in calculations in other formulas.

Numbers are rounded using the ROUNDUP formula. To do this, you need to fill in the cell.

The first argument is 76.375 and the second is 0.

Round a number using a formula

In this case, the number was rounded up. To round the value down, select the ROUND DOWN function.

Rounding occurs to the nearest whole number. In our case, up to 77 or 76.

Excel helps simplify any calculations. Using a spreadsheet you can complete higher mathematics assignments.

The program is most actively used by designers, entrepreneurs, and students.

The whole truth about Microsoft Excel 2007 formulas

EXCEL formulas with examples - Instructions for use

3. The result of calculations in cell C1 will be:

4. What command should you use to insert numbers from 1 to 10500 into a column? 1) the "Fill" command in the "Edit" menu

5. What formatting is applicable to cells in Excel 4) all options are correct

6. Which operator is not included in the group of arithmetic operators? 3)&

7. Which of the following is not a characteristic of a cell? Size 3

8. What value can a cell take? 4)all of the above

9. What can be a function argument? 4) all options are correct

10. Specifying a cell address in a formula is called 1)link

11. Excel is used to 2)creating spreadsheets

12. What character does a formula begin with in Excel? 1)=

13. What is any diagram based on? 4)table data

14. Which option correctly indicates the sequence of execution of operators in the formula? 3) reference operators then comparison operators

15. The minimum component of the table is 1) cell

16. What is the SUM function used for? 2) to obtain the sum of the specified numbers

17. How many types of cell addressing are there in Excel? 2) two

18. What does Excel do if there is an error in the formula? 2) displays a message about the type of error as the cell value

19. What is the "Form..." command window used for? 1) to fill in table entries

20. Which link is absolute? 3)$A$5

21. Arranging the values of a range of cells in a specific sequence is called 4) Sorting

22. Addressing cells in spreadsheets, which preserves a link to a specific cell or area, is called 3)absolute

26. The range of cells A1:D3 of the MS EXCEL spreadsheet is selected. Range contains 4)12 cells

27. Range of criteria is used in MS Excel when 1)using an advanced filter

2). 1, 2, 4

29. To solve an equation with one unknown in MS Escel, you can use the option 3) parameter selection

Word processor

1. If in the "Page Settings" dialog you set the page scale to "no more than 1 page wide and 1 page high" then when printing, if the sheet is larger than this size, ...

1). the page will be cropped to these dimensions

2). the page will be reduced to this size

3). the page will not print

4). the page will be enlarged to this size

2. Microsoft Word is: 3) text editor

3. Open Microsoft Word: 3) Start - Programs - Microsoft Word

4. In a text editor, the main parameters when setting a font are 1) typeface, size, style

5. During the formatting process, the text changes 2) paragraph parameters

6. In a text editor, the main parameters when setting paragraph parameters are 2) indentation, interval

7. In a text editor, a necessary condition for performing the Copy operation is 4)selecting a piece of text

8. In a text editor, when setting page parameters, 3)margins, orientation

9. During the editing process, the text changes 3) sequence of characters, words, paragraphs

10. The minimum object used in a text editor is 4) familiarity (symbol)

11. In a text editor, the Copy operation becomes possible after 4) highlighting a piece of text

12. To enable menu customization mode in the MS Word text editor, you must run the command 4)Service-Settings

2)2 - b

4)2, 4, 5

17. The “Review” toolbar control group includes elements for 1) creating, viewing and deleting notes

18. Cyclic switching between insertion and replacement modes when entering characters from the keyboard is carried out by pressing the 4)Insert key

19. Create a document: 1)File - Create

20. Open a document 4) Start – Documents

22. Documents are usually saved: 2) In the folder "" My Documents ""

23. Select the document viewing mode that is used specifically for typing text: 1)regular

24. Select the correct document printing algorithm: 3)Make a preview, File - Print - Select printer - Specify the number of copies - Ok

25. Which list is called “bulleted”: 2). each line begins with a marker - a specific character

26. Which toolbar is designed for working with tables: 2)Tables and borders

If you haven't worked with Excel Online before, you'll soon learn that it's not just a spreadsheet for entering numbers. Yes, Excel Online can be used to look up column or row totals, but you can also calculate a mortgage payment, solve math or engineering problems, and find best-case scenarios based on specified variable values.

In Excel Online, all this is implemented using formulas in cells. A formula performs calculations or other actions on data in a worksheet. A formula always begins with an equal sign (=), which can be followed by numbers, mathematical operators (such as a plus or minus sign), and functions that greatly enhance the formula's capabilities.

Below is an example of a formula that multiplies 2 by 3 and adds 5 to the result to get 11.

The following formula uses the function PMT to calculate the mortgage payment ($1,073.64) at 5% interest (5% divided by 12 months equals monthly interest) for a period of 30 years (360 months) with a $200,000 loan:

PMT(0.05/12;360;200000)

Below are examples of formulas that can be used on the worksheets.

=A1+A2+A3 Calculates the sum of the values in cells A1, A2, and A3.

=ROOT(A1) Uses the function ROOT to return the square root of the number in cell A1.

=TODAY() Returns the current date.

=UPPERCAP("hello") Converts the text "hello" to "HELLO" using the function CAPITAL.

= If (A1>0) Analyzes cell A1 and checks whether its value is greater than zero.

Elements of the formula

A formula can also contain one or more of the following elements: functions, references, operators, and constants.

1. Functions. The PI() function returns the value of Pi: 3.142...

3. Constants. Numbers or text values entered directly into a formula, such as 2.

4. Operators. The ^ ("cap") operator is used to raise a number to a power, and the * ("asterisk") operator is used to multiply.

Using Constants in Formulas

A constant is a ready-made (not calculated) value that always remains unchanged. For example, the date 10/09/2008, the number 210 and the text “Profit for the quarter” are constants. an expression or its value are not constants. If a formula in a cell contains constants, but not references to other cells (for example, it looks like =30+70+110), the value in such a cell changes only after changing the formula.

Using Operators in Formulas

Operators define the operations that must be performed on the elements of a formula. The calculations are carried out in a standard order (corresponding to the basic rules of arithmetic), but this can be changed using parentheses.

Operator types

Microsoft Excel supports four types of operators: arithmetic, text, comparison, and reference operators.

Arithmetic operators

Arithmetic operators are used to perform basic arithmetic operations such as adding, subtracting, multiplying, dividing, or combining numbers. The results of operations are numbers. The arithmetic operators are given below.

Comparison Operators

Comparison operators are used to compare two values. The result of the comparison is a logical value: TRUE or FALSE.

Text concatenation operator

Ampersand ( & ) is used to concatenate (concatenate) one or more text strings into one.

Link operators

You can use the following operators to define references to ranges of cells:

Order of actions in formulas in Excel Online

In some cases, the order of calculation can affect the value returned by a formula, so it is important to understand the standard order of calculations and how you can change it to get the results you want.

Calculation order

Formulas calculate values in a specific order. The formula always starts with an equal sign ( = ). Excel Online interprets the signs that follow the equals sign in a formula. After the equal sign, you can evaluate elements (operands) such as constants or cell references. They are separated by calculation operators. Excel Online evaluates the formula from left to right according to a specific order for each operator in the formula.

Operator precedence

When multiple operators are used in a single formula, Excel Online performs the operations in the order shown in the table below. If a formula contains operators that have the same precedence, such as division and multiplication operators, Excel Online runs them from left to right.

Using parentheses

To change the order in which a formula is evaluated, enclose the part of the formula that must be executed first in parentheses. For example, the following formula would result in the number 11 because Excel Online does multiplication before addition. In this formula, the number 2 is multiplied by 3, and then the number 5 is added to the result.

If you change the syntax using parentheses, Excel Online adds 5 and 2 and then multiplies the result by 3; the result of these actions will be the number 21.

In the example below, the brackets that enclose the first part of the formula specify the following calculation order for Excel Online: the value B4 + 25 is determined, and the resulting result is divided by the sum of the values in cells D5, E5 and F5.

=(B4+25)/SUM(D5:F5)

Using functions and nested functions in formulas

Functions are predefined formulas that perform calculations on given values, called arguments, and in a specified order. These functions allow you to perform both simple and complex calculations.

Function syntax

Below is an example function ROUND rounding a number in cell A10 demonstrates the syntax of the function.

1. Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the function arguments separated by commas, and a closing parenthesis.

2. Function name. To display a list of available functions, click any cell and press SHIFT+F3.

3. Arguments. There are different types of arguments: numbers, text, boolean values (TRUE and FALSE), arrays, error values (such as #N/A), or cell references. The argument used must return a value that is valid for the given argument. Constants, formulas, and other functions are also used as arguments.

4. Argument tooltip. When you enter a function, a tooltip appears with the syntax and arguments. For example, a tooltip appears after you enter the expression =ROUND(. Tooltips only appear for built-in functions.

Entering functions

Dialog window Insert function makes it easier to enter functions when creating formulas that contain them. When you enter a function into a formula in the dialog box Insert function The name of the function, all its arguments, a description of the function and each of the arguments, the current result of the function and the entire formula are displayed.

To make it easier to create and edit formulas and minimize the number of typos and syntax errors, use formula autocompletion. After you enter an equal sign (=) and the initial letters or display trigger in Excel Online, a dynamic drop-down list of available functions, arguments, and names that match those letters or trigger appears below the cell. The item from the drop-down list can then be inserted into the formula.

In some cases, you may need to use a function as one of the arguments to another function. For example, the formula below uses a nested function to compare the result with the value 50 AVERAGE.

1. The AVERAGE and SUM functions are nested within the IF function.

Valid calculated value types A nested function used as an argument must return its corresponding data type. For example, if the argument must be a Boolean, that is, TRUE or FALSE, the nested function must also return a Boolean value (TRUE or FALSE). Otherwise, TE102825393 will generate the error “#VALUE!”

Limit number of function nesting levels. You can use up to seven levels of nested functions in formulas. If function B is an argument to function A, function B is at the second nesting level. For example, in the function example above AVERAGE And SUM are second level functions since they are both function arguments IF. A function nested as an argument in a function AVERAGE, will be a third level function, etc.

Using references in formulas

The reference points to a cell or range of cells on a worksheet and tells Excel Online where the values or data you want to use in the formula are. Using references in one formula, you can use data that is in different parts of the worksheet, as well as the value of one cell in several formulas. You can also set references to cells from different sheets of one workbook or to cells from other workbooks. Links to cells in other workbooks are called links or external links.

Link style A1

Default link style By default, Excel Online uses the A1 reference style, in which columns are identified by letters (A through XFD, for a maximum of 16,384 columns) and rows are identified by numbers (from 1 to 1,048,576). These letters and numbers are called row and column headings. To reference a cell, enter the column letter and then the row number. For example, link B2 points to the cell located at the intersection of column B and row 2.

Differences between absolute, relative and mixed references

Relative links. A relative reference in a formula, such as A1, is based on the relative position of the cell containing the formula and the cell being referenced. When you change the position of the cell containing the formula, the reference also changes. When you copy or fill a formula along the rows and along the columns, the link is automatically adjusted. By default, new formulas use relative references. For example, when you copy or fill a relative reference from cell B2 to cell B3, it automatically changes from =A1 to =A2.

Absolute links. An absolute cell reference in a formula, such as $A$1, always refers to a cell located at a specific location. When you change the position of a cell containing a formula, the absolute reference does not change. When copying or filling a formula by rows and columns, the absolute reference is not adjusted. By default, new formulas use relative references, but to use absolute references you must enable the corresponding option. For example, when you copy or fill an absolute reference from cell B2 to cell B3, it remains the same in both cells: =$A$1.

Mixed links A mixed reference contains an absolute column and a relative row, and an absolute row and a relative column. An absolute column reference is of the form $A 1, $B 1, etc. An absolute row reference is of the form $1, B $1, etc. If you change the position of the cell containing the formula, the relative reference will change, but the absolute reference will not will change. If you copy or fill a formula in rows or columns, the relative reference is automatically adjusted, but the absolute reference does not change. For example, when you copy and fill a mixed reference from cell a2 to cell B3, it is adjusted from =A$1 to =B$1.

3D Link Style

Convenient way to link to multiple sheets 3D references are used to analyze data from the same cell or range of cells across multiple sheets in the same workbook. A 3D reference contains a cell or range reference preceded by sheet names. Excel Online uses all the sheets specified between the start and end names in the link. For example, the formula =SUM(Sheet2:Sheet13!B5) sums all the values contained in cell B5 across all sheets from Sheet 2 to Sheet 13, inclusive.

Using 3D references, you can reference cells on other sheets, define names, and create formulas using the following functions: SUM, AVERAGE, AVERAGE, COUNT, COUNTA, MAX, MAX, MIN, MIN, PRODUCT, STDEV.Y, STDEV.V , STDEV, STDEV, DISP, DISP.V, DISPA and DISP.

What happens when you move, copy, paste, or delete sheets. The following examples explain what changes happen to 3D references when you move, copy, paste, or delete the sheets that the references point to. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to sum the values in cells A2 through A5 in sheets two through six.

Paste or copy If you insert sheets between sheets 2 and 6, Excel Online will add the contents of cells A2 through A5 in the added sheets to the total.

Removal If you delete sheets between sheets 2 and 6, Excel Online won't use their values in calculations.

Moving If you move the sheets between Sheet 2 and Sheet 6 so that they end up in front of Sheet 2 or behind Sheet 6, Excel Online will subtract the contents of the cells in the moved sheets from the total.

Move End Sheet If you move sheet 2 or 6 to another location in the workbook, Excel Online will adjust the amount to reflect the change in sheet range.

Removing an End Sheet If you delete sheet 2 or 6, Excel Online will adjust the amount to reflect the change in sheet range.

R1C1 link style

You can use a referencing style in which both rows and columns are numbered. The R1C1 reference style is useful for calculating the position of columns and rows in macros. When using this style, the position of a cell in Excel Online is indicated by the letter R followed by the row number and the letter C followed by the column number.

When you record a macro in Excel Online, some commands use the R1C1 link style. For example, if the command to click an element is written Autosum To add a formula that sums a range of cells, Excel Online will use the R1C1 reference style rather than A1 when writing the formula.

Using names in formulas

You can create specific names to identify cells, cell ranges, formulas, constants, and tables in Excel Online. A name is a meaningful shorthand that explains the purpose of a reference to a cell, constant, formula or table, since it can be difficult to understand their essence at first glance. Below are examples of names and how using them makes formulas easier to understand.

Types of names

There are several types of names that can be created and used.

Specific name The name used to represent a cell, range of cells, formula, or constant. You can create your own defined names. Sometimes (for example, when setting the print area), Excel Online automatically creates a specific name.

Table name The name of an Excel Online table that is a collection of data about a specific topic, stored in records (rows) and fields (columns). Excel Online creates a default Excel Online table name for "Table1", "Table2", etc., every time you insert an Excel Online table, but you can change these names to make them more meaningful.

Creating and entering names

The name is created using the command create a name from the selection. You can conveniently create names from existing row and column names using a selection in a worksheet.

Note: By default, names use absolute cell references.

You can enter your name using the following methods.

Keys Enter a name, for example, as a formula argument.

Auto-completion of formulas. Use the Formula AutoComplete drop-down list, which automatically displays valid names.

Using array formulas and array constants

Excel Online does not support array formulas. You can view the results of array formulas created in Excel desktop, but you won't be able to edit or recalculate them. If you have the Excel desktop app installed on your computer, click Open in Excel to move on to working with arrays.

The example array formula below calculates the total stock prices; cell rows are not used when calculating and displaying individual values for each stock.

When you enter the formula "=(SUM(B2:D2*B3:D3))" as an array formula, the Stock and Price value for each exchange is first calculated, and then the sum of all results is calculated.

Calculating Multiple Values. Some functions return arrays of values or require an array of values as an argument. To calculate multiple values using an array formula, you must enter the array into a range of cells that has the same number of rows or columns as the array arguments.

For example, given a series of three sales values (in column B) for three months (in column A), the function TREND determines the continuation of the linear series of sales volumes. To be able to display all the results of the formula, it is entered into three cells in column C (C1:C3).

The formula "=TREND(B1:B3;A1:A3)", entered as an array formula, returns three values (22,196, 17,079, and 11,962) calculated from three sales volumes over three months.

Using Array Constants

In a regular formula, you can enter a reference to a cell with a value or to the value itself, also called a constant. Similarly, in an array formula, you can enter a reference to an array or an array of values contained in cells (sometimes called an array constant). Array formulas accept constants just like other formulas, but array constants must be entered in a specific format.

Array constants can contain numbers, text, Boolean values such as TRUE or FALSE, or error values such as "#N/A". One array constant can contain values of different types, for example (1,3,4;TRUE,FALSE,TRUE). Numbers in array constants can be integers, decimals, or exponential format. The text must be enclosed in double quotation marks, for example "Tuesday".

When formatting array constants, ensure that the following requirements are met:

Constants are enclosed in curly braces ( { } ).

Columns separated by commas ( , ). For example, to represent the values 10, 20, 30, and 40, enter (10,20,30,40). This array constant is a 1-by-4 matrix and corresponds to a one-row, four-column reference.

Cell values from different rows are separated by semicolons ( ; ). For example, to represent the values 10, 20, 30, 40 and 50, 60, 70, 80 in stacked cells, you could create a 2-by-4 array constant: (10,20,30,40,50, 60,70,80).

Formulas

Calculations in program tables excel carried out using formulas A formula can contain numeric constants, cell references, and Exc functionsel connected by symbols of mathematical operations. Parentheses allow you to change the standard order of actions. If a cell contains a formula, the worksheet displays the current result of that formula. If you make a cell current, the formula itself is displayed in the formula bar.

Rules for using formulas in a program excel is that if the cell value really depends on other table cells, Always a formula should be used even if the operation can be easily performed in the mind. This ensures that subsequent editing of the table will not violate its integrity and the correctness of the calculations performed in it.

The formula may contain links, that is, the addresses of cells whose contents are used in calculations. This means that the result of the formula depends on the number in another cell. The cell containing the formula is therefore dependent. The value displayed in a formula cell is recalculated when the value of the referenced cell changes.

A cell reference can be specified in different ways. First, the cell address can be entered manually. Another way is to click on the desired cell or select the range whose address you want to enter. The cell or range is highlighted with a dotted frame.

All program dialog boxes excel, that require cell numbers or ranges to be specified contain buttons attached to the corresponding fields. When you click on such a button, the dialog box is minimized to the smallest possible size, which makes it easier to select the desired cell (range) by clicking or dragging (Fig. 5.3).

Rice. 5.3. Dialog box expanded and collapsed

To edit a formula, double-click on the corresponding cell. In this case, the cells (ranges) on which the value of the formula depends are highlighted on the worksheet with colored frames, and the links themselves are displayed in the cell and in the formula bar in the same color. This makes it easier to edit and check the correctness of formulas.

By default, cell references in formulas are treated as relative. This means that when you copy a formula, the addresses in the references automatically change according to the relative location of the original cell and the copy being created.

For example, let's say cell B2 contains a link to cell A3. In relative view, we can say that the reference points to a cell that is one column to the left and one row below the given one. If the formula is copied to another cell, then this relative reference indication will remain. For example, when you copy a formula into cell EA27, the link will continue to point to the cell to the left and below, in this case cell DZ28.

At absolute addressing link addresses do not change when copied, so the cell to which the link points is treated as non-tabular. To change the addressing method when editing a formula, select the cell reference and press F4. Cell number elements that use absolute addressing are preceded by a $ character. For example, when you press the F4 key successively, the cell number A1 will be written as A1, $A$ 1, A$ 1 and $A1. In the last two cases, one of the cell number components is treated as absolute and the other as relative.

Copying cell contents

Copying and moving cells in a program excel can be done using the drag-and-drop method or via the clipboard. When working with a small number of cells, it is convenient to use the first method; when working with large ranges, it is convenient to use the second.

Drag and drop method. To copy or move the current cell (selected range) along with its contents using drag-and-drop, move the mouse pointer over the frame of the current cell (it will take the form of an arrow with additional arrows). Now the cell can be dragged anywhere on the worksheet (the insertion point is marked with a tooltip).

To select the method for performing this operation, as well as for more reliable control over it, it is recommended to use special drag using the right mouse button. In this case, when you release the mouse button, a special menu appears in which you can select the specific operation to be performed.

Using the clipboard. Transferring information via the clipboard has in the program excel certain features associated with the complexity of control over this operation. First, you need to select the range to be copied (cut) and give the command to place it on the clipboard: Edit Copy or Edit Cut. Pasting data into a worksheet is only possible immediately after it is placed on the clipboard. Attempt performing any other operation cancels the started copying or moving process. However, there is no data loss because the cut data is removed from its original location only when the insertion occurs.

The insertion location is determined by selecting a cell that corresponds to the upper left corner of the range placed on the clipboard, or by selecting a range that is exactly the same size as the one being copied (moved). Insertion is performed using the Edit Paste command. You can use the Edit Paste Special command to control how you paste. In this case, the rules for inserting data from the clipboard are set in the dialog box that opens.

Automation of input

Since tables often contain duplicate or similar data, the program excel contains input automation tools. Funds provided include: auto-completion,autocomplete numbers And autocompleteformulas.

Auto-completion. To automate the entry of text data, the method is used auto-completion. It is used when entering text strings, some of which are repeated, into the cells of one column of a worksheet. While entering text data into the next cell, the program excel checks whether the entered characters match the strings in this column above. If a clear match is found, the entered text is automatically completed. Pressing the ENTER key confirms the autocomplete operation, otherwise you can continue entering without paying attention to the proposed option.

You can interrupt AutoComplete by leaving an empty cell in a column. Conversely, to use AutoComplete features, the filled cells must be consecutive, with no gaps between them.

Autofill with numbers. When working with numbers, the method is used autofill. There is a black square in the lower right corner of the current cell frame - fill marker. When you hover over it, the mouse pointer (it usually looks like a thick white cross) takes the form of a thin black cross. Dragging a fill handle is considered an operation to "multiply" the contents of a cell either horizontally or vertically.

If a cell contains a number (including a date, a monetary amount), then dragging the marker copies the cells or fills them with an arithmetic progression. To select the autofill method, you must perform a special drag and drop using the right mouse button.

For example, let's say cell A1 contains the number 1. Hover your mouse over the fill handle, right-click and drag the fill handle so that the frame covers cells A1, B1, and C1, and release the mouse button. If you now select Copy Cells in the menu that opens, all cells will contain the number 1. If you select Fill, the cells will contain the numbers 1, 2 and 3.

To accurately formulate the conditions for filling cells, you should give the Edit Fill Progression command. In the Progression dialog box that opens, select the progression type, step size, and limit value. After clicking the OK button, the program excel automatically fills cells in accordance with 1 specified rules.

Autofill formulas. This operation is performed in the same way as autofilling numbers. Its peculiarity is the need to copy references to other cells. During autocomplete, the nature of the links in the formula is taken into account: relative links change in accordance with the relative location of the copy and the original, absolute links remain unchanged.

For example, assume that the values in the third column of the worksheet (column C) are calculated as the sum of the values in the corresponding cells of columns A and B. Enter the formula =A1+B1 in cell C1. Now let's copy this formula using the autofill method into all cells of the third column of the table. Relative addressing allows the formula to be correct for all cells in a given column.

IN
Table 5.1 shows the rules for updating links when autofilling along a row or along a column.

Table 5.1. Rules for updating links during autocomplete

Using Standard Features

Standard functions are used in the program excel only in formulas. Callfunctions consists in indicating in the formula function name, after which it is indicated in parentheses list of parameters. Individual parameters are separated in the list by semicolons. The parameter can be a number, a cell address, or an arbitrary expression, which can also be calculated using functions.

In formula entry mode, a drop-down list of functions appears on the left side of the formula bar, where the current cell number used to be located. It contains the ten most recently used functions, as well as More Functions.

Using the Function Wizard. When you select Other Functions, the Function Wizard starts, making it easier to select the desired function. In the Category drop-down list, select the category to which the function belongs (if it is difficult to determine the category, use the Full alphabetical list item), and in the Select a function list - a specific function of this category. After clicking the OK button, the function name is entered in the formula bar along with parentheses limiting the list of parameters. The text cursor is positioned between these brackets. You can call the Function Wizard more easily by clicking the Insert Function button in the formula bar.

A
function arguments. Once a function name is selected, the Function Arguments dialog box appears on the screen (in previous versions ExelThis the window was seen as formula palette). This window, in particular, contains the value that will be obtained if you immediately finish entering the formula (Fig. 5.4).

Rice. 5.4. Formula Bar and Function Arguments Dialog Box

The rules for calculating formulas containing functions do not differ from the rules for calculating simpler formulas. Cell references used as function parameters can also be relative or absolute, which is taken into account when copying formulas using the AutoComplete method.