Search code examples
google-sheetsgoogle-sheets-formulanumber-formattinggoogle-sheets-querygoogle-query-language

Decimals in Query formula in Google Sheets

I'm trying to make a query formula. 

=QUERY(namedRange;"SELECT F WHERE 2.6 > D AND 2.6 < E")

This works, but when I'm using a column reference like E7 it doesn't work. The value in E7 is 2,6. I know the comma breaks the query..

=QUERY(namedRange;"SELECT F WHERE '"&E7&"' > D AND '"&E7&"' < E")

So i replace the comma with =SUBSTITUTE(E7;",";".";1) 

=QUERY(namedRange;"SELECT F WHERE '"&SUBSTITUTE(E7;",";".";1)&"' > D AND '"&SUBSTITUTE(E7; ","; "."; 1)&"' < E")

Still no luck...

I hope some with more understanding of this can help me out...

Solution

  • wrapping stuff in QUERY into single quotes converts it into a text string so it's the same as you would try to math out if "abc > 123" - it does not make any sense...

    try:

    =QUERY(namedRange; 
 "select F 
  where "&SUBSTITUTE(E7; ","; "."; 1)&" > D 
    and "&SUBSTITUTE(E7; ","; "."; 1)&" < E"; 0)