Excel to Google Sheets - Error: Formula parse error

6
votes

I have created a simple league spreadsheet in Excel but when I try and use it in Google Sheets I am getting formula errors. This is the first time I have used Google Sheets and I would like to use it to make my league public.

An example of a invalid formula : 

=IF(C3=0,"",VLOOKUP(MIN(Calculations!$A$3:$A$12),Calculations!$A$3:$N$12,3,FALSE))

https://docs.google.com/spreadsheets/d/1gYOJcJPHstD1NPwIkKRr2lejbfidnqyFIRqXeQgVi-E/edit?usp=sharing

2
excelgoogle-sheetsexcel-formulags-vlookup
There is a sheet "Calculations". It is hidden. I suspect, the errors come from there. 2ne should make it visible and look there for errors. – Axel Richter
I have unhidden the other sheets docs.google.com/spreadsheets/d/… – 2ne
As pnuts mentioned: Google Sheets do not support structured references in Excel table formulas. See: productforums.google.com/forum/#!topic/docs/TvSO3tBF1i4 – Axel Richter
Could you kindly show me how to correct just the example invalid formula that I posted in an answer for me to accept? – 2ne

2 Answers

3
votes

There is nothing wrong with the formula in the example. The problem is that it is referring (directly or otherwise) to cells that themselves contain formulae that Google Sheets does not recognise - in particular those containing square brackets.

0
votes

Here's a free google-sheet formula parse and evaluate tool for untangling formulae:

https://docs.google.com/spreadsheets/d/1wwclmAMXGaFanVLlyvzv63fDx6JkwOcT6Dkisac-sXI/copy

It's free. It's tiny. It's just a simple shared google-sheet file (with no macros, add-ons or fancy stuff). Click the link to make your own copy. Have a try, and if you like it, bookmark it for future use while fighting formulae in google-sheet.

I made it to make up for the lack in google sheet of the Evaluate Formula (F9) parser native to Excel. It analyses any formula pasted into yellow cell G1, splitting and colouring them by depth or by chosen characters, and referencing them against the available formulae to see where brackets or commas are likely to have been omitted or put in the wrong place. A handy tool for simple checks, and for making or parsing complex or nested formulas, and for finding 'Parse Error', and diagnosing #REF!, #NAME?, #N/A, #NUM!, #VALUE! error codes.

Enjoy!