Formula Summary - Microsoft Support (2023)

Types of operators

There are four different types of arithmetic operators: arithmetic, comparison, text concatenation, and reference.

The order in which Excel for the web performs operations on formulas

In some cases, the order in which a calculation is performed can affect the formula's return value, so it's important to understand how the order is determined and how you can change the order to get the results you want.

The syntax of the functions

The following example forRONDAThe function that rounds a number in cell A10 illustrates the syntax of a 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. Role name. For a list of available functions, click in a cell and press SHIFT+F3.

3. Arguments. Arguments can be numbers, text, logical values ​​like TRUE or FALSE, arrays, error values ​​like #N/A, or cell references. The specified argument must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

4. Plot Tooltip. As you type the function, a tooltip appears showing the syntax and arguments. For example, type=REDONDO(and the tooltip appears. Tooltips are only displayed for built-in functions.

feature introduction

If you create a formula that contains a function, you can use thatinsert functionDialog box that helps you enter worksheet functions. When you insert a function into the formula, theinsert functionThe dialog box displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.

Use formula autocompletion to simplify creating and editing formulas and reduce syntax and typos. After typing an = (equals sign) and initial letters or a display trigger, Excel for the web displays a dynamic drop-down list below the cell with valid functions, arguments, and names that match the letters or trigger. You can insert a dropdown list entry into the formula.

nesting functions

In certain cases, it may be necessary to use a function as one of the arguments to another function. For example, the following formula uses a nestedAVERAGEand compare the result with the value 50.

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

valid returnsWhen a nested function is used as an argument, the nested function must return the same type of value that the argument takes. For example, if the argument returns TRUE or FALSE, the nested function must return TRUE or FALSE. If the function fails, Excel for the web shows a #VALUE! error value

Nesting Level ConstraintsA formula can contain up to seven levels of nested functions. When a function (let's call it function B) is used as an argument to another function (let's call it function A), function B acts like a second-level function. for example heAVERAGEfunction and theADDITIVEare second-level functions when used as arguments toEOccupation. A nested function within the nestedAVERAGEthe function is then a third-level function, and so on.

The A1 reference style

The default reference styleBy default, Excel for the web uses the A1 referencing style, which references columns of letters (A through XFD, for a total of 16,384 columns) and rows of numbers (1 through XFD). or 1 048 576). These letters and numbers are called row and column headings. To refer to a cell, type the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

Reference another tableIn the example below, theAVERAGEThe worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

1. Refers to the worksheet called Marketing

2. Refers to the range of cells between B1 and B10 inclusive

3. Separate the table reference from the cell range reference

The difference between absolute, relative and mixed references

relative referencesA relative cell reference in a formula, such as B.A1, is based on the relative position of the cell containing the formula and the cell the reference refers to. If you change the position of the cell containing the formula, the reference will change. If you copy or complete the formula across rows or columns, the reference will automatically adjust. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it will automatically adjust from =A1 to =A2.

absolute referencesAn absolute cell reference in a formula, such as B.$A$1, always refers to a cell at a particular position. If you change the position of the cell containing the formula, the absolute reference remains the same. If you copy or complete the formula across rows or columns, the absolute reference will not wrap. By default, new formulas use relative references, so you may need to change them to absolute references. For example, if you copy or fill an absolute reference from cell B2 to cell B3, it will remain the same in both cells: =$A$1.

mixed referencesA mixed reference has an absolute column and a relative row, or an absolute row and a relative column. An absolute column reference has the form $A1, $B1, etc. An absolute row reference has the form A$1, B$1, etc. If you change the position of the cell containing the formula, the relative reference changes and the absolute reference changes No. As you copy or complete the formula across rows or columns, the relative reference is automatically adjusted and the absolute reference is not adjusted. For example, if you copy or fill a mixed reference from cell A2 to cell B3, it will change from =A$1 to =B$1.

The 3D reference style

How to reference multiple worksheetsIf you want to analyze data in the same cell or range of cells across multiple worksheets in a workbook, use a 3D reference. A 3D reference contains the cell or range reference preceded by a range of worksheet names. Excel for the web uses whatever worksheet is stored between the start and end names of the reference. Example: =SUM(Sheet2:Sheet13!B5) sums all values ​​contained in cell B5 across all worksheets between Sheet 2 and Sheet 13 inclusive.

• You can use 3D references to refer to cells in other worksheets, define names, and create formulas with the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINE, PRODUCT, STDEV.P , STDEV. S, STADWA, STADWPA, VAR.P, VAR.S, VARA and VARPA.

• Three-dimensional references cannot be used in array formulas.

• Three-dimensional references cannot be used with the intersection operator (single space) or in formulas that use implicit intersections.

What happens when you move, copy, paste, or delete worksheets?The following examples explain what happens when you move, copy, paste, or delete sheets contained in a 3D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to sum cells A2 through A5 on sheets 2 through 6.

• paste or copyIf you paste or copy worksheets between Sheet2 and Sheet6 (the endpoints in this example), Excel for the web will include all values ​​in cells A2 through A5 of the added worksheets in the calculations.

• extinguishIf you delete sheets between Sheet2 and Sheet6, Excel for the web will remove their values ​​from the calculation.

• moveIf you move sheets between Sheet 2 and Sheet 6 outside the reference sheet range, Excel for the web will remove your values ​​from the calculation.

• move a full stopIf you move Sheet2 or Sheet6 to a different location in the same workbook, Excel for the web will adjust the calculation to accommodate the new sheet area between them.

• Delete an endpointIf you exclude Sheet2 or Sheet6, Excel for the web adjusts the calculation to include the sheet range between them.

The R1C1 reference style

You can also use a reference style where the worksheet's rows and columns are numbered. The R1C1 reference style is useful for calculating row and column positions in macros. In R1C1 style, Excel for the web indicates a cell's position with an "R" followed by a row number and a "C" followed by a column number.

When you record a macro, Excel for the web records some commands using the F1C1 referencing style. For example, if you record a command, e.g. B. By clicking onit is meTo enter a formula that adds a range of cells, Excel for the web records the formula using F1C1 style references, not A1 style references.

types of names

There are several types of names you can create and use.

Distinguished NameA name that represents a cell, cell range, formula, or constant value. You can create your own distinguished name. Also, Excel for the web sometimes creates a distinguished name for you, such as B. when you specify a printable area.

table nameA name for an Excel for the web spreadsheet, which is a collection of data about a specific topic, stored in records (rows) and fields (columns). Excel for the web creates a default Excel web table name "Table1", "Table2", etc. whenever you insert a table from Excel for the web, but you can change these names to make them more descriptive.

Create and enter names

You create a name withCreate a name from the selection. You can conveniently create names from existing row and column labels using a selection of worksheet cells.

Use:By default, names use absolute cell references.

You can enter a name by typing:

• typewriterFor example, write the name as an argument in a formula.

• Using Formula AutoCompleteUse the AutoComplete Formula drop-down list, which automatically lists valid names.

Using array constants

In a common formula, you can enter a reference to a cell that contains a value or the value itself, also called a constant. Similarly, in an array formula, you can enter a reference to an array or enter the array of values ​​contained in the cells, also known as an array constant. Array formulas accept constants in the same way as non-array formulas, but you must enter array constants in a specific format.

Array constants can contain numbers, text, logical values ​​like TRUE or FALSE, or error values ​​like #N/A. Different types of values ​​can be in the same array constant, for example {1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format. The text must be enclosed in double quotes, for example B. "Tuesday".

Array constants cannot contain cell, column, or row references of unequal length, formulas, or special characters $ (dollar sign), parentheses, or % (percent sign).

When formatting array constants, check the following:

• Enclose them in braces ({}).

• Separate the values ​​in different columns with commas (,). For example, to represent the values ​​10, 20, 30, and 40, enter {10,20,30,40}. This matrix constant is called a 1-by-4 matrix and corresponds to a 1-row-by-4-column reference.

• Separate the values ​​on different lines with a semicolon (;). For example, to represent the values ​​10, 20, 30, and 40 on one row and 50, 60, 70, and 80 on the row immediately below, enter a 2-by-4 matrix constant: {10,20, 30, 40;50, 60,70,80}.