Google Sheets calculates some values other with the same formula as Excel

Question

I have an Excel formula (in German, for germam Excel), which calculates in Google Sheets some values other than in Excel.

This is the Excel formula:

=(WENNFEHLER(((SVERWEIS(A2;sv!$A:$B;2;0))/B2)+(WENN(B2=1;"33,9";WENN(B2=2;"16,28";WENN(B2=3;"10,36";WENN(B2=4;"7";WENN(B2=5;"5,64")))))+WENN(B2=6;"4,13";WENN(B2=7;"3,27";WENN(B2=8;"2,61";WENN(B2=9;"2,18";WENN(B2=10;"1,82")))))+WENN(B2=11;"1,77";WENN(B2=12;"1,81";WENN(B2=13;"1,85";WENN(B2=14;"1,9";WENN(B2=15;"2,04")))))+WENN(B2=16;"1,68";WENN(B2=17;"1,61";WENN(B2=18;"1,65";WENN(B2=19;"1,62";WENN(B2=20;"1,59";"0"))))));0))/55

This is this formula in Google Sheets:

=(IFERROR(((VLOOKUP(A2,sv!$A:$B,2,0))/B2)+(IF(B2=1,"33,9",IF(B2=2,"16,28",IF(B2=3,"10,36",IF(B2=4,"7",IF(B2=5,"5,64")))))+IF(B2=6,"4,13",IF(B2=7,"3,27",IF(B2=8,"2,61",IF(B2=9,"2,18",IF(B2=10,"1,82")))))+IF(B2=11,"1,77",IF(B2=12,"1,81",IF(B2=13,"1,85",IF(B2=14,"1,9",IF(B2=15,"2,04")))))+IF(B2=16,"1,68",IF(B2=17,"1,61",IF(B2=18,"1,65",IF(B2=19,"1,62",IF(B2=20,"1,59","0")))))),0))/55

The difference in calculation is visible in cells D6 and D9: yo'll see, while results of Excel calculation are numbers, those of Google Sheet are zeros. All other cells are calculated equally.

What do i do wrong in Google Sheets? How can i achive correct calculation in Google Sheets?

Calculation in Excel:

+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| Keyword                                       | Number | URL                                                               | Calculation |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| arbeitsrechtsschutz   sofort                  | 27     | https://www.example-koeln.com/example-verkehrsrechtsschutz-sofort | 0,114478114 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| das komfort rechtsschutz für selbstständige   | 23     | https://www.example-koeln.com/firmen/webaktiv                     | 0           |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmenrechtsschutz   mit vertragsrechtsschutz | 49     | https://www.example-koeln.com/firmen/webaktiv                     | 0,003710575 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmenrechtsschutz   was ist versichert       | 75     | https://www.example-koeln.com/firmen/webaktiv                     | 0,004848485 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmenrechtsschutz wikipedia                  | 10     | https://www.example-koeln.com/firmen/webaktiv                     | 0,051272727 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmen   vertrags rechtsschutz                | 50     | https://www.example-koeln.com/firmen/webaktiv                     | 0,032727273 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmenvertragsrechtsschutz   beispiele        | 50     | https://www.example-koeln.com/firmen/webaktiv                     | 0,003636364 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| firmen rechtsschutz rückwirkend               | 17     | https://www.example-koeln.com/example-verkehrsrechtsschutz-sofort | 0,029272727 |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+
| forderungsmanagement rechtsschutz             | 93     | https://www.example-koeln.com/firmen/webaktiv                     | 0           |
+-----------------------------------------------+--------+-------------------------------------------------------------------+-------------+

Calculation in Google Sheets

+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| Keyword                                     | Number | URL                                                               | SI             |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| arbeitsrechtsschutz sofort                  | 27     | https://www.example-koeln.com/example-verkehrsrechtsschutz-sofort | 0.1144781145   |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| das komfort rechtsschutz für selbstständige | 23     | https://www.example-koeln.com/firmen/webaktiv                     | 0              |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmenrechtsschutz mit vertragsrechtsschutz | 49     | https://www.example-koeln.com/firmen/webaktiv                     | 0.003710575139 |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmenrechtsschutz was ist versichert       | 75     | https://www.example-koeln.com/firmen/webaktiv                     | 0.004848484848 |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmenrechtsschutz wikipedia                | 10     | https://www.example-koeln.com/firmen/webaktiv                     | 0              |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmen vertrags rechtsschutz                | 50     | https://www.example-koeln.com/firmen/webaktiv                     | 0.03272727273  |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmenvertragsrechtsschutz beispiele        | 50     | https://www.example-koeln.com/firmen/webaktiv                     | 0.003636363636 |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| firmen rechtsschutz rückwirkend             | 17     | https://www.example-koeln.com/example-verkehrsrechtsschutz-sofort | 0              |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+
| forderungsmanagement rechtsschutz           | 93     | https://www.example-koeln.com/firmen/webaktiv                     | 0              |
+---------------------------------------------+--------+-------------------------------------------------------------------+----------------+

The SVerweis and VLOOKUP part of the formula reads same numbers:

These are these numbers again as data:

+-----------------------------------------------+-------------------------+
| Keyword                                       | Monatliches Suchvolumen |
+-----------------------------------------------+-------------------------+
| arbeitsrechtsschutz sofort                    | 170                     |
+-----------------------------------------------+-------------------------+
| das komfort rechtsschutz für selbstständige   | 0                       |
+-----------------------------------------------+-------------------------+
| firmenrechtsschutz mit   vertragsrechtsschutz | 10                      |
+-----------------------------------------------+-------------------------+
| firmenrechtsschutz was ist versichert         | 20                      |
+-----------------------------------------------+-------------------------+
| firmenrechtsschutz wikipedia                  | 10                      |
+-----------------------------------------------+-------------------------+
| firmen vertrags rechtsschutz                  | 90                      |
+-----------------------------------------------+-------------------------+
| firmenvertragsrechtsschutz beispiele          | 10                      |
+-----------------------------------------------+-------------------------+
| firmen rechtsschutz rückwirkend               | 0                       |
+-----------------------------------------------+-------------------------+
| forderungsmanagement rechtsschutz             | 0                       |
+-----------------------------------------------+-------------------------+

yes, sure, multiple times, but i can't find any :( Other thing: i've tried to calculate without VLOOKUP in the formula, but firstly bringing values from both tables into single one - same effect :( — Evgeniy

teylyn teylyn · Accepted Answer · 2020-11-15T22:54:01

If you put numbers into double quotes, they will be interpreted as text. In that case, the conversion of regional setting for decimal point vs decimal comma will likely fail. It will only work if the number does not have a decimal symbol, so the text can be converted to a number regardless of regional settings.

Keep in mind that Google Sheets is a US based system and runs in the cloud. Your regional settings of using a decimal comma are used in the user interface, but not in the calculation engine.

Remove the double quotes in the IF (WENN) statements and use numbers instead. Then the calculations can be performed properly.

Google Sheets calculates some values other with the same formula as Excel

1 Answers