0

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)
Improve this question
6
  • 1
    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.
    – Tedinoz
    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.
    – Tedinoz
    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
    – mondo
    Dec 31, 2021 at 1:29
  • 1
    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.
    – Tedinoz
    Dec 31, 2021 at 1:38
  • 1
    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.
    – Tedinoz
    Dec 31, 2021 at 2:02

1 Answer 1

Reset to default
1

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.

The correct formula is:

=QUERY($I$19:$I$30,"select I where I <= " & (C$27) & " order by I DESC limit 1",0)
Improve this answer
2
  • 1
    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?
    – Tedinoz
    Dec 31, 2021 at 2:10
  • Updated, and got rid of INDEX. I think it was needed at some point because I had something else wrong.
    – mondo
    Dec 31, 2021 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.