6

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
2ne
  • 5,766
  • 9
  • 30
  • 51
  • 1
    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 Sep 17 '14 at 12:19
  • I have unhidden the other sheets https://docs.google.com/spreadsheets/d/1gYOJcJPHstD1NPwIkKRr2lejbfidnqyFIRqXeQgVi-E/edit?usp=sharing – 2ne Sep 17 '14 at 12:39
  • 2
    As pnuts mentioned: Google Sheets do not support structured references in Excel table formulas. See: https://productforums.google.com/forum/#!topic/docs/TvSO3tBF1i4 – Axel Richter Sep 17 '14 at 12:48
  • Could you kindly show me how to correct just the example invalid formula that I posted in an answer for me to accept? – 2ne Sep 17 '14 at 13:24

2 Answers2

3

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
1

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!

thegurumonkey
  • 71
  • 1
  • 1