Workbook functions

What it is

A workbook function is a formula you have given a short name. You create it once in the Named ranges panel (or ask the agent), and then you write it in any cell of the workbook, like SUM: =МАРЖА(D2;E2).

Why. A price list has a hundred rows, and in each one you calculate the margin: =(D2-E2)/D2, =(D3-E3)/D3, and so on a hundred times. With a workbook function:

• each cell holds a short =МАРЖА(D2;E2), which is easy to read without decoding;
• the rule is written in one place. If you decide to calculate the margin differently, you fix the function, and all the cells where it is used recalculate;
• in the hundredth row you will not make a mistake with the brackets: brackets are only in the function itself.

How it works, in plain words

• A function has parameters, which are like boxes. МАРЖА has two boxes: “цена” and “себестоимость”.
• In the formula of the function you write what to do with the boxes: (цена-себестоимость)/цена. There are no cell addresses here, only the names of the boxes.
• When you write =МАРЖА(D2;E2) in a cell, the first box (“цена”) gets what stands first in the brackets (D2), and the second box (“себестоимость”) gets the second value (E2).
• The order matters. The values in the brackets go in the same order as the parameters. Price 100, cost 70: =МАРЖА(100;70) gives 0.3 (30%), and =МАРЖА(70;100) gives minus 0.43, because the price and the cost have swapped places.
• Inside the brackets you can write a cell address, a number or another formula: =МАРЖА(D2;E2), =МАРЖА(100;70), =МАРЖА(D2*1,2;E2). An empty cell counts as zero.
• The result is live: if you change the price in D2, the margin recalculates by itself. This is how a workbook function differs from a calculation done by code by the agent: there, ready numbers go into the cells, and those numbers do not recalculate by themselves.

How to use it

The Named ranges panel

1. Open the spreadsheet. On the toolbar, click Named ranges, the bookmark icon. If the browser window is narrow and the icon is not visible, it is hidden in the More tools menu. Another way is the Named ranges list arrow next to the address field, to the left of the formula bar; its last item is Named ranges….
2. A Named ranges panel opens to the right of the sheet, and the button stays pressed. The sheet does not close: while the panel is open, you can select cells and type formulas.
3. At the top of the panel is the Names list (in a new workbook it says No names yet) and the button + Add. Below it is the form; at the bottom are Cancel and Save. The cursor is already in the Name field.
4. The form is ready to name the area selected on the sheet: “Диапазон1”, “Диапазон2”… If the selected area already has a name, the form opens it.
5. The width of the panel can be changed by dragging its left edge.
6. To close the panel, click the Close button (×) in its header, press Escape, or click Named ranges again.
7. Only one workbook panel is open on the right at a time. If you open Comments, Version history, Notifications, Agent, the chart editor, Data validation, Conditional formatting or protection, the names panel closes, and vice versa.

Method 1. Create a function yourself

1. Open the Named ranges panel. If the form already has another name open, click + Add.
2. Under the list there is a switch with two options: Range (the bookmark icon) and Function (the f(x) icon). Choose Function: the fields Name, Parameters and Formula appear. In an empty field, a faint sample shows what to type: “цена; себестоимость” or “=(цена - себестоимость) / цена”.
3. Name: the field is empty, with the sample “МАРЖА” in it. Type МАРЖА.
4. Parameters: type цена; себестоимость. The separator can be “;” or “,”. The line In a cell under the fields shows at once how to write the function in a cell: =МАРЖА(A2; B2), one cell per parameter, in the same order.
5. Formula: type (цена-себестоимость)/цена. The “=” sign at the start is not required: it is added on saving.
6. Check the function before saving. Under the In a cell line is Try it, with one field per parameter, labelled with its name. Enter 100 in “цена” and 70 in “себестоимость”, and Result shows 0.3. While not all the fields are filled, or the formula is incomplete, there is a dash. If you change the formula or the values, the result changes at once.
7. Click Save. The panel stays open, and the chosen line appears in the Names list with the f(x) icon: “МАРЖА (цена; себестоимость)”. A bin, Remove name, appears at the bottom of the panel.
8. Go to cell F2 (you do not need to close the panel), type =МАРЖА(D2;E2), and press Enter.
9. To calculate all the rows, drag the small square in the bottom right corner of F2 down. F3 will contain =МАРЖА(D3;E3), F4 will contain =МАРЖА(D4;E4), and so on.
10. The margin is a fraction: 0.3 means 30%. To see percentages, select column F and choose Number format (the “123” button) → Percentage.

Second example: a number inside a function (VAT). Click + Add and repeat steps 2–7 with other values:

• Name НДС; Parameters сумма; Formula сумма*0,2.
• In a cell will show =НДС(A2). In Try it, enter 100 in “сумма”: the Result is 20.
• In a cell: =НДС(D2). With the amount 100, the result is 20.
• Fractional numbers in the formula are written with a comma (0,2), and the values in the brackets when the function is called are separated by “;”. Everything works as in ordinary formulas in a Russian interface.
• If the rate changes, click НДС in the list, change 0,2 to the new number, and click Save. All the cells with =НДС(…) recalculate.

Name a range

1. Select an area on the sheet, for example A2:A100 with prices, and open the panel. The switch is set to Range, and the Range field holds the address of the selection.
2. Name: instead of “Диапазон1”, type Цены. The In a cell line shows =SUM(Цены).
3. If you need another area, click the Range field and outline the cells on the sheet with the mouse: the address is entered by itself. While the field waits for a selection it has a coloured frame, and you can outline as many times as you like. The field stops listening to the sheet when you move to another field of the panel, or press Escape or Enter in it. Enter also saves the name. Escape in this field only stops listening to the sheet; a second Escape closes the panel. You can also type the address: A2:A100.
4. Click Save: a line with the bookmark icon appears in the list: “Цены · A2:A100”.
5. Now, when exactly this area is selected, the address field to the left of the formula bar shows “Цены”. Type цены in the field and press Enter, or choose “Цены” in the Named ranges list arrow next to the field: the area is selected. Workbook functions are not shown in this list.

Method 2. Ask the agent

1. Click Agent in the spreadsheet header.
2. Write in plain words, for example: “create a function МАРЖА with the parameters цена and себестоимость: (цена − себестоимость) / цена, and calculate the margin in column F”.
3. In its answer, the agent writes “defined the function МАРЖА(цена; себестоимость)” and fills column F. If a function with that name already existed, the agent replaces its formula and writes “redefined the function …”.
4. The function appears in the list of the Named ranges panel and in the f(x) list. From then on you can work with it by hand.
5. A function can also be removed in words: “remove the function МАРЖА”.
6. If you do not like the change, open Version history, click the mark Before agent edits, then Go to version, and then Go.

Method 3. Insert a ready function from the list

1. Stand in a cell.
2. Click f(x) to the left of the formula bar.
3. At the top is the group Your functions. Point at МАРЖА and wait half a second: its formula appears.
4. Click МАРЖА: =МАРЖА( appears in the cell. Type D2;E2) and press Enter.
5. You can search: type “мар” in the Search for a function field, and your function is found together with the built-in ones.
6. Even quicker: start typing the name in a cell, for example =МАРЖ. The hints show “МАРЖА(цена; себестоимость)”, and Tab or Enter inserts it with the bracket.

Change, rename, delete

• Change: click the name in the Names list. It opens in the form, and its range is selected on the sheet. The cursor stays in the list, so you can go straight to the next name. For a function, fix Parameters or Formula: In a cell and Try it update at once, and the Try it fields are empty when the function is opened. Click Save. All the cells with this function recalculate.
• Cancel discards unsaved changes: the form returns to the name of the area selected on the sheet, and if that area has no name, to a new name for it. The panel stays open.
• A function opened again is shown in the Formula field in the same way as formulas in cells: a fraction with a comma (0,2), and built-in functions with their English names (ОКРУГЛ becomes ROUND).
• Rename: change Name and click Save. The formulas in the cells rewrite themselves with the new name.
• Delete: select the name and click the bin, Remove name. The form starts with a new name, and the panel stays open. The cells where the function was show #NAME?.
• A line in the Names list shows the icon (bookmark for a range, f(x) for a function), the name, and, fainter next to it, the range address (“· A1:B10”) or the function’s parameters (“(цена; себестоимость)”).
• Names from other sheets are shown in the list with the sheet name after “·”, but they cannot be selected: open the sheet where the name was created.
• If you switch to another sheet while the panel is open, unsaved changes in the form are lost, and the form starts again from the selection on that sheet.

Good to know

• A function works on all sheets of the workbook.
• Capital and small letters do not matter: =маржа(D2;E2) is the same as =МАРЖА(D2;E2).
• There are functions without parameters too: Name КУРС, Parameters empty, Formula 90. It is called with brackets: =КУРС().
• A workbook function’s formula can refer to a cell, for example =сумма*$B$1, if the rate is in B1. Such a reference always points to the sheet where the function was created, even if the function is called from another sheet.
• A function can call another workbook function, or itself.

Limits

• Name: letters (Russian letters too), digits, “” and “.”, without spaces. It starts with a letter or “”, and can be up to 255 characters long.
• A function cannot be named like a cell address (A1, D2), like ИСТИНА, ЛОЖЬ, TRUE or FALSE, or like a built-in function (SUM, СУММ, ОКРУГЛ).
• A name is unique across the whole workbook. If a range or a function with that name already exists on any sheet, a second one cannot be created.
• A sheet can have up to 500 names, ranges and functions together. A function can have up to 253 parameters. A formula can be up to 8 192 characters long.
• Functions that call each other, or themselves, can nest up to 128 levels.
• The calculation limits are the same as for ordinary formulas (see the Limits section of the “Formulas and functions” document).
• Try it is a trial calculation: nothing is written into the spreadsheet. The numbers in its fields are written as in the formulas of your language (in a Russian interface, 0,2). A non-number is treated as text.
• When downloaded to XLSX, workbook functions are kept and work in Excel 365. When an XLSX with such functions is uploaded, the functions appear in the workbook.
• Only those who edit the spreadsheet can create and change functions. In a spreadsheet opened for viewing only, there is neither the Named ranges button nor the f(x) list, but formulas that use workbook functions are calculated.
• On a summary sheet and in a version view there is neither the button nor the Named ranges panel. An open panel closes and does not come back by itself.
• On a phone, the panel stands under the sheet at full width and takes up to 45% of the screen height; the Names list in it takes up to 20%. The list and the fields scroll together, and Save is always visible. Outlining cells for the Range field is awkward on a phone: type the address on the keyboard, for example A2:A100.

If something goes wrong

What you see on the screen	Why	What to do
A built-in function has this name	The name is taken by a built-in function (SUM, СУММ)	Choose another name, for example МОЯ_СУММА
This name already exists	A range or a function with this name already exists in this workbook, on any sheet	Choose another name, or open and fix the existing one
Check the name	The name has a space or an extra character, or it looks like a cell address (A1), or it is ИСТИНА/ЛОЖЬ	Write it without spaces, for example ЦЕНА_С_НДС
Check the formula	The Formula field is empty, or a bracket or quotation mark is not closed	Complete the formula, and check the brackets and quotes
Check the name and parameters	The parameters repeat (цена; Цена), or are unsuitable as names (with a space, or like A1)	Give the parameters different names without spaces
Check the name and range	The sheet already has 500 names	Delete unneeded names
Check the range	The address in the Range field was not understood	Outline the cells on the sheet, or type an address of the form A1:B10
The address in the Range field does not change when you select cells	The field stopped listening to the sheet: you moved to another field of the panel, or pressed Escape or Enter in it	Click the Range field again, and outline the cells
The names panel closed by itself	Another workbook panel opened (Comments, Version history, Notifications, Agent, the chart editor, Data validation, Conditional formatting or protection), you moved to a summary sheet, or you opened a version	Click Named ranges again. From a summary sheet, go back to a normal sheet first
#VALUE! in a cell	There are more or fewer values in the brackets than parameters, or text instead of a number	Check how many values there are, and in what order
#NAME? in a cell	A typo in the name, the function was deleted, or a function without parameters is called without brackets (=КУРС)	Check the name in the f(x) list. Write it with brackets: =КУРС()
#NAME? for =МАРЖА(D2,E2)	In a Russian interface, the values in brackets are separated by “;”, not by a comma	Write =МАРЖА(D2;E2)
#DIV/0! in МАРЖА	The price is zero, or the price cell is empty	Fill in the price
A negative margin	The order of the values in the brackets is mixed up	Put the price first and the cost second
A dash in Result	Not all the Try it fields are filled, Name or Formula is empty, or the formula cannot be parsed	Fill in all the fields, and check the brackets and quotes
#DIV/0!, #NAME? or #VALUE! in Result	The formula gives an error on these values: division by zero, an unknown name, or text instead of a number	Check the formula and the trial values