# Google Sheets "Query" issue with rounding

I am using a formula of:

``````=INDEX(QUERY(\$I\$19:\$I\$30,"select I where I >= " & rounddown(D\$27,2) & " order by I limit 1",0),1)
``````

In `D27` I have a value of: `0.8190`

In `I19:I30` I have a series of values: `0.7400`, `0.7500`, `0.7800`, `0.8000`, `0.8200`, `0.8400`, `0.8700`, `0.9000`, `0.9300`, `0.9500`, `0.9800`, `1.0000`

I am trying to select the row in I that is closest, but not greater than.

Expected outcome would be: `0.8000` since `rounddown(0.8190,2)` is `0.81`, which is less than `0.8200`, but greater than `0.8000`.

However, it keeps returning `0.8200`.

I've tried a bunch of numbers in D27, and it keeps acting like the `>=` evaluation is rounding up. Is there a logic/syntax issue in my formula, or is this some quirk in evaluation on Google Sheets?

Basing this solution from another post: `https://webapps.stackexchange.com/questions/69743/find-the-closest-value-equal-or-greater-in-a-range-in-google-spreadsheets`

----- [UPDATE] -----

I indeed put the logic backwards based on false-near-positive, as well as an unclear goal in original post. The correct formula is:

``````=QUERY(\$I\$19:\$I\$30,"select I where I <= " & (C\$27) & " order by I DESC limit 1",0)
``````
• Hi and welcome to WebApps. Without commenting on the rest of your question, I just tried `=rounddown(0.819,4)`. My result = 0.8190 (which is kind of what I expected since there are 4 decimal places), yet you get 0.81. Would you care to double check your formula? One of us (quite possibly me) is wrong and this will affect your query outcome. Dec 31, 2021 at 1:08
• I've tried your query, and I also get a value of 0.82 which is, I think, the correct result. Are you trying to return the row number (or sequence number of the result (as was the case in the referred question)? If yes then your use of index is failing; if no, then there is no need for index in your formula. Dec 31, 2021 at 1:27
• Sorry, I went through permutations while I was crafting this post even. I have rounddown(D27,2). I've updated in the original post now Dec 31, 2021 at 1:29
• No worries. Expected outcome would be: 0.8000 since rounddown(0.8190,2) is 0.81 But the query is designed to return values `>=` (greater than or equal to). 0.80 is less than 0.81 so it is excluded; the next value is 0.82, which is greater than 0.81, so the result is 0.82. Dec 31, 2021 at 1:38
• The problem is that you are setting a limit of 1. So when you use `<=`, the query finds that the first value (0.74) will satisfy the condition. I agree, you need to sort `desc` in the query condition. Well done. Dec 31, 2021 at 2:02

Thanks @Tedinoz for challenging the logic. It turned out to be that `ORDER BY` was defaulting to ASC, and I really wanted it to be `DESC` and correcting the `>=` to `<=`. I only went with >= because it got me "closer" to the right answer after frustrated trial-and-error.
``````=QUERY(\$I\$19:\$I\$30,"select I where I <= " & (C\$27) & " order by I DESC limit 1",0)
• OK, let's clarify. Your goal was to "find-the-closest-value-equal-or-greater-in-a-range". Your question doesn't actually mention this, except in a passing reference to another post. May I suggest that your edit your question so that your goal is clearly described. Then insert the new (successful) formula into your answer, so that it is complete. On a tangent, are you still keeping `INDEX` or have you got rid of that? Dec 31, 2021 at 2:10