Entering a function in Excel - Informatics for the Humanities

Entering a function in Excel

Excel means a strictly defined sequence of mathematical operations performed on a certain type of quantity, called arguments of this function. Arguments - are data that the user specifies functions to get results. Functions allow you to perform both simple and complex calculations. Functions are a very convenient tool for the Excel, tabular processor, since they allow you to save time significantly when defining formulas and avoid accidental errors when entering them. For example, the function = SUMM is a function that allows you to calculate the amount of cell contents of a certain range that must be specified as an argument to the function. The function = SUM (A1: A12) can be replaced by the manually entered formula = A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + + A9 + A10 + A11 + A12, but it is much more convenient to use the function than writing a long formula. Note that the function that allows to perform the summation operation is often framed as a separate button on the toolbar and is indicated by a Greek letter or Auto Sum.

Example

Function Action. If the cells in the range A2: E2 contain the numbers 5, 15, 30, 40 and 50, then the entry in the formula line: "= SUM (A2: C2)" enters the number 50 in the active cell; and the record "= SUM (B2: E2; 15)" is 150.

When the function is completed, the calculation result is displayed in the cell, and the formula for the active cell is displayed in the Formula Bar. The function in Excel has a given name (name), it can directly enter the cell (before the function name there is a = sign), it can be part of the formula or be an argument of another function. The arguments of the function are numbers, texts, dates, data from other cells in the form of cell references or range names, as well as other functions of Excel.

The functions are written according to certain rules (they have the specified syntax), for example:

FUNCTION NAME (argument1; argument2; ...; arguments).

Arguments are separated from each other by a semicolon (in the English version - by a comma), text arguments are enclosed in quotes, spaces are not allowed. Function arguments must always be enclosed in parentheses.

Functions can be entered from the keyboard (for example, by simultaneously pressing Shift + F3 after selecting the cell into which the function is inserted, the context menu of the list of functions is opened), however it is much more convenient to insert the function into the table cell using the Function Wizard. To use the function wizard to insert a function into the selected (selected) cell, select the Function command in the Insert menu, and the context menu of the Function Wizard opens. Note that if some function has already been entered in this cell, then to change it, simply click on the drop-down list of functions next to the formula row. To access the Function Wizard, you can also click the fx button located on the toolbar (depending on the selected settings for the menu tools, it can also be in the formula bar).

For reference

Working with the Function Wizard (English Function Wizard) . The function wizard is a program that creates active cell a certain function. Working with the Function Wizard includes two steps. In the first step, in the Category row, the section with the desired function is selected with the mouse, and then the function itself is selected in the Select function window by its name. If you do not know which category the desired function belongs to, then in the Category list, select the Full alphabetical list. In this case, a list of all the functions will be displayed. In the dialog box there is also a short information about the purpose of the function, a button for recalling help about the selected function, the switch to the second window and other useful options.

In the second step after selecting a function, the window type depends on the number and type of arguments of this function. Each argument has its own input field, the transition from one field to another is done by clicking the mouse, while the cursor is flashing in the active field.

Usually the function has arguments mandatory (field name is highlighted in bold) and optional (not highlighted). Numeric and text arguments are entered from the keyboard. Entering the references to cells or their ranges should be done with the mouse, selecting them in the table in the standard way (ie by clicking the left mouse button in the desired cell or selecting the range). If the dialog box blocks the desired cells, you can move it away by clicking the title of the window. You do not need to monitor the syntax, the Function Wizard will do everything automatically. After registering the function, click on the OK button and the selected function will be inserted into the cell of the table that was originally defined to accommodate this function.

The Excel program contains more than 300 functions, divided into the following categories:

• for working with databases (... DMAX, DMIN, DREZNACH ...);

• category Date and time (... YEAR, DATE, DAY ...);

• Engineering category (... DELTA, DEV.VOSM ...);

• Financial category (... BET, NPV ...);

• Information category (... ELOHICH, EOSHIBKA ...);

• Logical category (IF, AND, OR, TRUE, LIES, PET);

• References and arrays (... ADDRESS, INDEX, LINE ...);

• The mathematical and trigonometric category (... ROOT, ROUND, BALANCE, SUM ...);

• Statistical (... PROBABILITY, ... MOST ...);

• Text (DLLP, ... RUBLE, ... TEXT ...).

After the function name, parentheses are always followed, for example, SUM (...). The arguments are written in the parentheses, if they have a dive function. Functions differ in how they use arguments, namely:

• functions without arguments;

• with one argument;

• a fixed number of arguments;

• an undefined number of arguments;

• Optional arguments.

The simplest and most frequently used functions of the table processor Excel include functions: summation (SUM), calculating the average (AVERAGE), finding the maximum (minimum) value from the list the input data (MAX/MIN), the estimation of the degree of dispersion of the data - variance (DISP), etc.

Example

The SUMIF function summarizes the values ​​of numeric cells that satisfy a certain condition. For example, in the table Excel you can determine the total contribution of an employee Ivanov to the organization of a corporate holiday, if you know that Ivanov paid money three times (1000 rubles, 2000 rubles, 1000 rubles .), while the employees of Petrov and Sidorov contributed only once (Table 12.2).

Table 12.2

Data for determining the total contribution of an employee Ivanov

Last name

Contribution, RUR.

Ivanov

1000

Ivanov

2000

Petrov

500

Ivanov

1000

Sidorov

500

The function SUMMER requires the specification of two ranges of cells and one criterion for selection. The first range contains the cells that are used to select the cells to be aggregated (that is, all the cells in the table that contain the information being processed - names and amounts). The criterion for selection of cells (selection condition) can be specified in the form of text or conditional expression. The second range includes directly summable cells. To find the sum of numeric values ​​that satisfy a given condition, you need:

• Click the cell where the result will be placed;

• Call up the Function Wizard and select the MIXED function;

• In the dialog box that appears, in the Range box, enter a reference to the range that will be used to select the cells (in this example, these are the lines with the names and amounts of contributions):

• go to the Criteria field and enter a criterion for selecting the counting of values ​​(in this example, it's Ivanov);

• in the Sum_range field, enter a reference to the range of summable cells (in this example, these are numbers);

• Press the Enter button.

As a result, the number 4000 appears in the cell chosen for placing the counting results, which reflects the total contribution of the employee Ivanov. In Fig. 12.2 shows the worksheet Excel with the data and calculations from Table. 12.2. Pay attention to the text in the formula line, which shows the design of the function SUMMER to solve the task - to determine the total contribution of an employee Ivanov to organize a corporate holiday.

Calculating the sum by a formula that includes the condition

Fig. 12.2. Calculating the sum of a formula that includes a condition

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)