Entering a formula in Excel - Computer science for the humanities

Enter a formula in Excel

All computational operations with the data of the table Excel are performed using formulas. A formula can contain any of the following elements:

• mathematical operators, for example indicated by the signs "+" (addition), "/" (division), (subtraction), "*" (multiplication); brackets are used to determine the order in which mathematical operations are performed;

• cell references (including named cells and ranges);

• numbers and text;

• Worksheet functions, such as SUM or AVERAGE (respectively Sum and Average).

The length of the formula should not exceed 1024 characters. If you enter the formula in the cell, the result of calculation by this formula will be displayed in it, while the formula itself appears in the formula bar when this cell is activated. When you enter a formula before it, you must enter an equal sign ("="). The formulas are dynamic, so when you change some values ​​in the cells on which calculations are based, the result of these calculations will also change.

For reference

The easiest way to enter a formula is as follows:

1) select the cell to which the calculation result will be placed;

2) enter the equal sign ("="), check that it is reflected in the formula bar;

3) click on the first cell containing the values ​​to be calculated using this formula;

4) enter the sign of the mathematical operation (addition, subtraction, multiplication, division);

5) click on the next cell containing the values ​​to be calculated;

6) repeat these steps step by step until the whole formula is entered;

7) the completion of the entry is made by pressing the Enter key.

If no errors were made when entering the formula, then immediately after pressing the Enter key, the value calculated by this formula appears in the corresponding cell. If, on entering the formula, an error appears, for example, instead of the letter of the Latin alphabet, a Cyrillic alphabet was inserted, then after pressing the Enter key, the text indicating the error: # name? Appears in the cell. In order to detect an error, you must double-click on the cell in which the error formula is located, as a result of which the formula itself appears in the table cell. In this case, the wrong cell addresses in the formula will be marked (font or color separations), and the cell itself, which was incorrectly represented in the formula, will also be marked with a colored border.

In addition to the four basic algebraic actions, you can also use other mathematical and logical operators in the Excel formulas (Table 12.1).

Table 12.1

The mathematical and logical operators used in the formulas Excel

Operator

Title

^

Exponentiation

& amp;

Concatenation, or text join statement

=

Boolean comparison is equal to

& gt;

Boolean comparison more than

& lt;

Boolean comparison less than

& gt; =

Boolean comparison greater than or equal to

& lt; =

Boolean comparison less than or equal to

& lt; & gt;

Boolean comparison not equal to

%

Percentage operator

:

Range operator

;

The union operator of ranges (in the English version - a comma)

Arithmetic operations have different precedence, so when calculating a value by the formula, first multiply and divide, then add and subtract. In general, the hierarchy of operators corresponds to the usual rules of mathematics: percentages - exponentiation - multiplication and division - addition and subtraction - text operator - comparison. In order to specify a different (user-defined) order of actions, parentheses must be used. If the formula has parentheses, then the operations that are in parentheses are executed first, then all other mathematical operations. For example, in the formula with brackets: = (A2 + B3 + D4)/3, the numbers in the cells with addresses A2, B3, D4 will be summed first, and then the sum will be divided by 3.

Note that some operators are entered from the keyboard only in the switch to English (En) mode. Addresses of cells are entered from the keyboard in both uppercase and lowercase Latin letters or by a simple mouse click on the cell.

When using comparison operations in a formula, the result will be a logical value: TRUE or FALSE.

A colon is used to define the range, which is called the Range operator. The entry in the formula in the form of "A1: A8" is understood in Excel as the need to calculate values ​​located in cells A1 through A8 inclusive. Semicolon in formulas is the Union of ranges operator (in the English version of the program it is denoted by the comma ) sign. Recording type A6; A7; A9 indicates that the calculations will take values ​​from these individual cells. When calculating in Excel , very often not specific cell values ​​are used, but a reference to the cell address, so when you change the value in a particular cell, the result of calculations in the formula also changes.

Examples of Excel formulas:

• = SUMM (A1: A12) - adds the values ​​of cells from the range A1: A12 (that is, the numbers that are in cells A1, A2, A3, A4, A5, A6, A7, A8, A9, A10 , All, A12);

• = Income - Expenses - subtracts from the number that is in the cell with the name Revenue & quot ;, the number contained in the cell named Expenses & quot ;.

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)