Google sheets QUERY with reference to another cell with number

Question

I want to query a column based on the value of a cell I am referencing in the formula.

partial screenshot of sheet

I'd expect the formula in F3 to retrieve all values of column D below the value defined in F6. Instead I get a #VALUE! error.

Is anyone able to help me with this? Thanks in advance!

Answer 1

The problem lies than in your locale you use the comma as a separator for decimals, but QUERY uses the dot to do that. So when you pass that cell as an argument it returns an error.

Maybe there's a more elegant way (I've tried TEXT(F6;"0.0") but has the same issue), you can replace your cell reference with a string created with the rounded part before the comma, a dot, and the remaining part:

ROUND(F6;0)&"."&ROUND(MOD(F6;1)*100000)

PS: replace 100000 with as 0s as decimals you may need

Then, the whole Query would look:

=QUERY(D:D;"SELECT D WHERE D > "&ROUND(F6;0)&"."&ROUND(MOD(F6;1)*100000))