Cell Reference in Google Sheets' Query is unsuccessful

0
votes

When creating a query in Google Sheets, I'm finding that hardcoding is working fine but using several references isn't working correctly.

Cell A2 = 0.75 (from a formula =(mround(Estimator!$C$4/57.2958,0.25)), type = number)

Cell B2 = 0.9 (from a formula =(mround(Estimator!$C$5+100,0.1)-100) type = number)

Specifically, the query below works:

=query(Time_Data, "SELECT N, O, P WHERE A="0.75" AND B="0.9)

And the query below works:

=query(Time_Data, "SELECT N, O, P WHERE A="&$A$2&" AND B="0.9)

But this query does not work:

=query(Time_Data, "SELECT N, O, P WHERE A="&0.75&" AND B="&$B$2)

And most importantly, this query does not work:

=query(Time_Data, "SELECT N, O, P WHERE A="&$A$2&" AND B="&$B$2)

Any suggestions about how to get this reference to work?

3
google-sheetsgoogle-sheets-formula
@jpv See below (there's no great format in StackOverflow) Apple Bob Carrot Delta Epsilon Frank George Harriet Integer Juliet Kayak Lemon Marcus Nomad Olliver Penelope 0 0.3 0.9 -0.8 -0.6 -0.3 0 -0.8 -0.9 0 -0.6 0.6 0.2 0 0 0.95 0 0.3 0.9 -0.8 -0.4 -0.3 0 -0.8 -0.9 0 -0.4 0.6 0.4 0 0 0.97 0 0.3 0.9 -0.8 -0.2 -0.3 0 -0.8 -0.9 0 -0.2 0.6 0.6 0 0 1.09 0 0.3 0.9 -0.8 0 -0.3 0 -0.8 -0.9 0 0 0.6 0.8 0 0 1.16 0 0.3 0.9 -0.6 -0.6 -0.3 0 -0.6 -0.9 0 -0.6 0.6 0 0 0 0.92 0 0.3 0.9 -0.6 -0.4 -0.3 0 -0.6 -0.9 0 -0.4 0.6 0.2 0 0 0.9 0 0.3 0.9 -0.6 -0.2 -0.3 0 -0.6 -0.9 0 -0.2 0.6 0.4 0 0 0.96 - soytsauce
Hi, you can share your spreadsheet with editing rights and drop the link here. - JPV

3 Answers

0
votes

It would help if we could see your data. But maybe try FILTER() and see if that works?

=FILTER(N:P, A:A=A2, B:B=B2)
0
votes

this is the correct syntax:

=QUERY(Time_Data; "SELECT N, O, P WHERE A matches'"&$A$2&"' AND B matches '"&$B$2&"'")

and if by any chance it wouldn't work try:

=QUERY(Time_Data; "SELECT N, O, P WHERE A matches '"&INDIRECT("A2")&"' 
                                    AND B matches '"&INDIRECT("B2")&"'")
0
votes

I called google support and they advised as follows: the formula in cell B2 was edited from 

=(mround(Estimator!$C$5+100,0.1)-100)

to 

=Value((mround(Estimator!$C$5+100,0.1)-100))

This resolved the issue.