Formulas and functions in Excel

• Entering a formula
• Editing formulas
• Operation Priority
• Copy/Paste formula
• Inserting a function

A formula is an expression that calculates the value of a cell. Functions are predefined formulas and are already built into Excel.

For example, in the figure below, the cell A3 contains a formula that adds cell values A2 и A1.

One more example. Cell A3 contains a function SUM (SUM), which calculates the sum of a range A1: A2.

=SUM(A1:A2)

=СУММ(A1:A2)

Entering a formula

To enter the formula, follow the instructions below:

1. Select a cell.
2. To let Excel know that you want to enter a formula, use the equal sign (=).
3. For example, in the figure below, a formula is entered that sums the cells A1 и A2.

   

Tip: Instead of manually typing A1 и A2just click on the cells A1 и A2.

4. Change cell value A1 on 3.

   

   Excel automatically recalculates cell value A3. This is one of the most powerful features of Excel.

Editing formulas

When you select a cell, Excel displays the value or formula in the cell in the formula bar.

1. 1. To edit a formula, click on the formula bar and edit the formula.

1. Press Enter.

   

Operation Priority

Excel uses a built-in order in which calculations are made. If part of the formula is in parentheses, it will be evaluated first. Then the multiplication or division is performed. Excel will then add and subtract. See example below:

First, Excel multiplies (A1*A2), then adds the value of the cell A3 to this result.

Another example:

Excel first calculates the value in parentheses (A2 + A3), then multiplies the result by the size of the cell A1.

Copy/paste formula

When you copy a formula, Excel automatically adjusts the references for each new cell into which the formula is copied. To understand this, follow these steps:

1. Enter the formula shown below into a cell A4.

   

2. Highlight a cell A4, right-click on it and select command Copy (Copy) or press keyboard shortcut Ctrl + C.

   

3. Next, select a cell B4, right-click on it and select command Insertion (Insert) in section Paste Options (Paste Options) or press the keyboard shortcut Ctrl + V.

   

4. You can also copy the formula from a cell A4 в B4 stretching. Highlight a cell A4, hold down its lower right corner and drag it to the cell V4. It’s much easier and gives the same result!

   

   Result: Formula in a cell B4 refers to values ​​in a column B.

   

Inserting a function

All functions have the same structure. For example:

SUM(A1:A4)

СУММ(A1:A4)

The name of this function is SUM (SUM). The expression between brackets (arguments) means that we have given a range A1: A4 as input. This function adds the values ​​in the cells A1, A2, A3 и A4. Remembering which functions and arguments to use for each specific task is not easy. Luckily, Excel has a command Insert Function (Insert function).

To insert a function, do the following:

1. Select a cell.
2. Нажмите кнопку Insert Function (Insert function).

   

   The dialog box of the same name will appear.

3. Search for the desired function or select it from the category. For example, you can choose the function COUNTIF (COUNTIF) from category statistical (Statistical).

   

4. Press OK. A dialog box will appear Function Arguments (Function arguments).
5. Click the button to the right of the field — (Range) and select a range A1: C2.
6. Click in the field Criteria (Criterion) and enter “>5”.
7. Press OK.

   

   Result: Excel counts the number of cells whose value is greater than 5.

   =COUNTIF(A1:C2;">5")

   =СЧЁТЕСЛИ(A1:C2;">5")

   

Note: Instead of using the “Insert function“, just type =COUNTIF(A1:C2,”>5”). When you type »=COUNTIF(«, instead of manually typing “A1:C2”, select this range with the mouse.