Google-sheets – Formula parse error message

google sheets

I've uploaded an Excel doc which worked perfectly in Excel but it's showing a parse error on a formula in Sheets. The formula is:
=IF(E9<1,C9,IF(AND(E9<=35,E9>=32),C9,IF(AND(E9<32,E9>=1),C9+0.1,IF(E9>35,C9-0.5*(E9-35),not metʺ))))

Can anyone see why I'm getting an error message?

Best Answer

Try: =if(E9<1,C9,IF(AND(E9<=35,E9>=32),C9,IF(AND(E9<32,E9>=1),C9+0.1,IF(E9>35,C9-0.5*(E9-35),"not met"))))

Two things:

as noted by Ruben, not met should be in quotes
as often happens on moving from one platform to another, sometimes single and double quotation marks are converted as similar-in-appearance (though invalid) characters. In this case, the character following not met is a "Unicode Character “ʺ” (U+02BA)" (Modifier Letter Double Prime). Just replacing this (and the missing opening quote) with conventional double quote marks solves the problem.

Related Solutions

Google-sheets – Query(IMPORTRANGE) throws “Formula Parse Error”

You probably want 1 instead of 16 as the last parameter of the query; this parameter means the number of header rows, not of headers.

You also have an extra pair of parentheses in your formula. Otherwise the formula is correct (tested in my spreadsheet), although you could shorten it by replacing select Col1,Col2,..., Col16 with select *

=QUERY(IMPORTRANGE("URL","Sheet Name!A2:P1000"),"SELECT * WHERE Col6 CONTAINS 'Abstract & TO'",1)

A possible source of your errors is that your spreadsheets are not connected for the purpose of IMPORTRANGE:

In the new version of Google Sheets, spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission.

Unfortunately, you will not see this prompt if your IMPORTRANGE command is within a QUERY; the parse error is thrown instead as there is nothing for QUERY to parse. To connect the spreadsheet, import something first, e.g.,

 =IMPORTRANGE("URL","Sheet Name!A1")

You will see #REF error; hovering over it shows an explanation and "connect" button.

Google-sheets – Variable GoogleFinance formula (Google Spreadsheets)

Short answer

Use

=GOOGLEFINANCE("CURRENCY:"&A2&"EUR")*B2

Explanation

Google Sheets have three ways for concatenating strings.

CONCATENATE() function
CONCAT() function
& operator

Instead of "CURRENCY:(A2)EUR", use

CONCATENATE("CURRENCY:",A2,"EUR")
CONCAT("CURRENCY:",CONCAT(A2,"EUR"))
"CURRENCY:"&A2&"EUR"