Chapter 4 · 4.1
Formulas from Excel: function names, separators and error messages
Formulas from an Excel file simply work in LibreOffice Calc and Euro-Office. The file stores functions in a fixed way. Only what you see and type depends on the language of your program.
Function names and separators
In the English version of Calc and in Euro-Office, you type the English function names with a comma between the arguments, just as in the English version of Excel:
| Function | What it does | Example |
|---|---|---|
| SUM | Adds up a range | =SUM(B2:B20) |
| IF | Returns one of two results depending on a condition | =IF(B5>0,"profit","loss") |
| SUMIF, COUNTIF | Adds up or counts cells that meet a condition | =COUNTIF(C2:C50,"Utrecht") |
| VLOOKUP, XLOOKUP | Looks up a value in a table | see Vertical lookup with VLOOKUP and XLOOKUP |
| IFERROR | Shows an alternative result if there is an error | =IFERROR(A2/B2,0) |
| TRIM | Removes extra spaces | =TRIM(A2) |
You can copy an English formula from the internet as it is. Formulas from other language versions use different function names and often a semicolon between the arguments. In that case, translate the function names and replace the semicolons with commas. The decimal separator is a full stop: 1.21.
In LibreOffice Calc
- Using Calc in another language but prefer English function names? Choose Tools > Options... > LibreOffice Calc > Formula and tick Use English function names. You'll also find the Separators there.
- Calc writes a reference to another sheet with a full stop:
Invoices.B2. Excel uses an exclamation mark:Invoices!B2. Calc converts this automatically when you open and save an xlsx file. - Help with a function: Insert > Function... (Ctrl + F2) opens the wizard with an explanation of each function.
Error messages in Calc
| Message | Meaning |
|---|---|
| #NAME? | Calc doesn't recognise the function name. Often a name from another language version or a custom VBA function from Excel. |
| #N/A | A lookup function couldn't find the value (see Vertical lookup with VLOOKUP and XLOOKUP). |
| #VALUE! | There is text where a number should be. |
| #DIV/0! | The formula divides by zero or by an empty cell. |
| #REF! | The formula refers to a cell that no longer exists. |
| Err: followed by a number | The formula isn't built correctly, for example with a bracket or separator too many or too few. Click the cell: the status bar at the bottom tells you what is wrong. |
In Euro-Office
In your WebOké Nextcloud, Euro-Office uses the function names and separators of your language by default. You can check and change this via File > Advanced settings, under Formula language and Region.
A list of commonly used function names is available in Copying formulas from Excel: function names and separators. You can read the basics of formulas in Formulas and functions: SUM, AVERAGE, IF and VLOOKUP.