0

I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE.

Now after I =query and select A, the query gives me the right values, but the type has changed. Now if I do =isnumber on the column it gives me FALSE

I even tried to * 1 to column A (i.e., select A * 1) the error is the following:

Error: Unable to parse query string for Function QUERY parameter 2: Can't perform the function product on values that are not numbers

Well clearly 1 is a number and column A is a number; I just used =isnumber on it, and the return is TRUE.

My query is pretty big, but I don't think it should affect anything. I'm going out to column CZ and there are about 1000 rows.

  • 3
    It would be a lot easier to help you if you could share a spreadsheet demonstrating the problem. – Vidar S. Ramdal Feb 5 '15 at 8:56
2

The fix I used for this issue was to coerce the strings into a number by using ARRAYFORMULA(A1:A+0) in a new column adjacent to the original query (let's say column B).

I then used another query on the resulting data set and was able to sum(B) and avg(B) and format them as percentages successfully, as the type of column B was recognized as numeric.

1

Sorry I can't share the spreadsheet. And I probably can't replicate the error. But I found a solution and I wanted to share it for people who also have big spreadsheets that encounter this error. The problem is that some of the columns start with a number as the first observation and some of them don't. The ones that don't some are blank for 100 rows before a number shows up. It seems that Google does not treat the column as a number if the first row is not a number for very large spreadsheets.

So the solution is to sort it, this way the numbers will show up at the time and Google will treat the column as values.

Don't ask me why this works. It just does.

0

0

  • cell D1 - (basic query):

    =QUERY(A1:A20, "select A")


  • cell G1 - (converted to numbers):

    =ARRAYFORMULA(QUERY(VALUE(A1:A20), "select Col1"))


  • cell I1: - (converted to numbers - shorter approach)

    =ARRAYFORMULA(QUERY(A1:A20*1, "select Col1"))


  • cell K1: - (as it should be)

    =ARRAYFORMULA(QUERY(IF((ISNUMBER(A1:A20*1))*(A1:A20<>""), A1:A20*1, A1:A20), 
     "select Col1"))


  • cell M1: - (selecting all 1 numbers)

    =QUERY(A1:A20, "select A where A = 1")


  • cell N1: - (selecting all 1 numbers - outside number reference)

    =QUERY(A1:A20, "select A where A = "&1)


  • cell O1: - (selecting all 1 text strings - query is confused)

    =QUERY(A1:A20, "select A where A = '1'")


  • cell P1: - (selecting all 1 text strings - outside number reference - query is confused)

    =QUERY(A1:A20, "select A where A = '"&"1"&"'")


  • cell Q1: - (conversion to text strings and selecting all 1 text strings + header row)

    =ARRAYFORMULA(QUERY(TO_TEXT(A1:A20), "select Col1 where Col1 = '1'", 1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.