If I write a formula using query and limit it to one result, I can then use it as a number, for instance
=1+QUERY(B13:F18,"SELECT C WHERE (B='Banana') LIMIT 1")
But if I want to use the maximum value the same way, it gives me an error:
=1+QUERY(B13:F18,"SELECT MAX(C) WHERE (B='Banana')",0)
Function ADD parameter 2 expects number values. But 'max' is a text and cannot be coerced to a number.
Column C only contains numbers! If I exclude the addition I see that it is adding an unwanted label "max" above the max value. I am putting 0 for the third parameter to query, which I thought meant I wouldn't have any header lines. I also tried "" and that didn't work either.
I thought maybe it was treating the result as an array, so I wrapped the whole thing with:
=INDEX(QUERY(...), 0, 1)
Which I expected to skip over the row containing max. But I got the exact same output!
How do I extract just the number so I can use it in the formula?