I am trying to convert a function from Microsoft Excel to Google Sheets. In Microsoft Excel, it is written as =-cellA*LOGZ(cellA;2). The Microsoft help page points out that the syntax for LOGZ is LOGZ(číslo, [základ]), so I hypothesize that Excel in English will have LOGB, where B would stay for base, so LOGB(number, [base]). If I look on Google Sheets help, it shows the following notation: LOG(value, base). So, I have constructed the following formula in Sheets: =-D34*LOG(D34, 2). It ends with an error that doesn't recognize LOG as a function. Note that D34 is also a formula. So I wonder, where is the problem? If I place a comma in the brackets, D34 becomes black, which looks like the Google parser doesn't recognize it anymore as a variable. If I exclude coma in the brackets, it recognizes both values but still ends with an error function not recognized. And this is just one of the tests I performed. E.g. I have replaced D34 with a value or placed more brackets into my formula. It kind of looks like my use of the log function is not correct, but what is the correct function then?
-
I have provided an answer that may help you to narrow it down. If not, I suggest sharing a demo with a minimally reproducible example using the "Blank Sheet Maker" to protect your privacy. More info here on Stack Exchange: How to share a Google Sheets demo spreadsheet– Blindspots ♦Commented Mar 3, 2023 at 16:58
-
1@BlindSpots, well. I think I can share it, nothing to hide at the moment.– JuandevCommented Mar 3, 2023 at 18:54
1 Answer
While it is the case that there are some locale-based differences between how logarithmic functions are named in Excel, a review of your Google sheet exposed the problem unrelated to the function names but still tied to your locale of Czechia.
EU vs. Non-EU Separators
This applies to most EU/Non-EU locales
| Locale | Decimal Separator |
Formula Arguments |
{ Horizontal } Array Separator |
{ Vertical } Array Separator |
|---|---|---|---|---|
| Non-EU | . period | , comma | , comma | ; semi-colon |
| EU | , comma | ; semi-colon | \ back-slash | ; semi-colon |
In my examples below, you need to replace the comma , which is the formula argument separator for most non-EU regions with a semi-colon ; which is the correct one for your region.
# NON-EU
=D34*LOG(D34, 2)
# EU
=D34*LOG(D34; 2)
Sample Sheets Formula (Similar to Your Example)
Referenced Cell D34 Contains a Formula (Similar to Your Example)
Function Names: Czech vs. English
1. Microsoft Excel
The syntax for logarithmic functions differs in Excel between Czech and English
Excel LOGZ (Czech)
Czech Syntax: LOGZ(number,[base])
English Syntax: LOG(number, [base])
Excel LOG (Czech)
Czech Syntax: LOG(number)
English Syntax: LOG10(number)
2. Google Sheets
Google Sheets uses the English versions LOG* and LOG10
for both English and Czech.
* Regardless of the locale/language settings, Sheets will allow LOGZ to be entered in place of LOG and will immediately replace it with LOG when committing/saving the formula.
Resources
Jak na Excel (How to Excel)
- LOGZ (LOG) - logaritmus - Excel
- LOG (LOG10) - dekadický logaritmus - Excel
Microsoft Office Support
- Funkce LOGZ (CZ) | LOG function (EN)
- Funkce LOG (CZ) | LOG10 function (EN)
Google Sheets Support
- LOG Function
- LOG10 Function
-
1And from your example, it looks like it doesn't matter whether there is a space or not behind the comma/semicolon. Right?– JuandevCommented Mar 3, 2023 at 22:19
-
Correct. Spaces are often included to make code more legible. It is a stylistic choice, except when it matters, which isn't in a list or arguments.– Blindspots ♦Commented Mar 3, 2023 at 23:38