A formula may contain functions and mathematical operators, the order of calculation of which corresponds to the standard rules of mathematics. The result of formulas including arithmetic operators is a numeric value, while in the case of comparison operators the result is a logical value — TRUE or FALSE.
Table 1. Mathematical Operators in Excel Formulas
| Operator | Meaning | Operator | Meaning |
| ( | Open parenthesis | = | Equal |
| ) | Close parenthesis | < | Less than |
| * | Multiplication | <= | Less than or equal |
| / | Division | > | Greater than |
| + | Addition | >= | Greater or equal |
| – | Subtraction | <> | Not equal |
| ^ | Exponentiation | % | Percentage |
NOTE
The percent symbol is an operator which, in MS Excel formulas, divides the preceding number by 100. For example:
- Formula =5% results in 0.05,
- Formula =12781193%%% results in 12.781193.
MS Excel can process not only arithmetic formulas, but also perform operations with text, compare values, and relate various ranges and cells in a workbook.
Operations with Text and Dates
Concatenation — combining text, numbers, and dates within a single cell.
The concatenation operator is the & symbol, which joins text, numbers, and dates into one long text string.
Example. Combine in one cell the data located in different cells of an Excel worksheet.
In Fig, the following formula is entered in cell A3:
=A1 & TEXT(B1," D MMM YYYY ") & C1 & TEXT(D1," # ##0р.")
Here the TEXT() function applies a new date format and a currency format to the contents of cells B1 and D1, converting them to text.
Text, dates, and time are entered into formulas using quotes. For example, the formula:
="Total " & TOTALS
will return the text:
Total 1 500 000 р.
if the named cell TOTALS contains the number 1500000.
Explicit date operations (dates written directly in formulas):
="15/02/11" - "11/02/11" ="24 February 2011" - "26 May 2010"
These formulas return the number of days between the two dates.
Comparison and Reference Operations
Examples of comparison operations in formulas:
- =A1<10 → TRUE if the content of cell A1 is less than 10; FALSE otherwise.
- =B7>=15 → TRUE if the content of cell B7 is greater than or equal to 15; FALSE if less than 15.
Table 2. Reference Operators in Excel
| Operator | Example | Operation | Result |
| : | SUM(A1:A7) | Range | Reference to all cells in the rectangular range between two corners |
| , | SUM(A1:A7,B8) | Union | Union of two ranges: all cells from both ranges |
| (space) | SUM(A1:A7 A16:B300) | Intersection | Intersection of two ranges: all cells common to both ranges (if none, returns #NULL!) |
| (space) | =Y78 Credit | Intersection | Content of the cell at the intersection of the column named Y78 and the row named Credit |
Table of Operation Precedence in Excel (Descending)
Table 3. Operation Precedence in MS Excel
| Operator | Operation | Operator | Operation |
| (space) | Intersection | * and / | Multiplication, Division |
| , | Union | + and – | Addition, Subtraction |
| – | Negation | & | Text concatenation |
| % | Percentage | =, <, <= | Comparisons |
| ^ | Exponentiation |
Automatic Calculation
MS Excel provides the ability to automatically perform the most common calculations for a selected data range (average value, count of values, count of numbers, maximum, minimum, sum).
To do this, in the status bar under the AutoCalculate area, you can select the required function from the context menu (by right-clicking).