Numeric before query but query results are not a numbers

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

• 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))

Answer 4

I was having this issue also, where my query results were not numeric. It was because some cells had text instead of a number.

As you mentioned, one solution is to create a new column which only contains the numeric values. Otherwise you can do it "on the fly" using the =FILTER() function. See example below:

=QUERY(FILTER(A1:D10, ISNUMBER(D1:D10)), "SELECT Col1, Col4")

The FILTER() function first excludes all rows that do not have a number in column D, and then passes this filtered array to the QUERY() function, where you can do further operations on it. QUERY() should then treat that column as numeric since all values are numeric.

This is a useful technique to learn, since the FILTER() function can actually use almost any sheets function and logic in the criteria, whereas the QUERY() function has only a very limited number of comparisons and functions that can be used.