0

I'm trying to use a QUERY function to get a maximum value of a range where value in another column equals to a specified value with

=QUERY(L$4:M,"select max(M) where L="&K1&" label max(M)''")

I tried K1=99.75, 109.725 or 114.7125 every values returns its M values until L goes above 114.7125 it returns N/A

I tried query in L80:L100 with 119.7 which exists in row 88 also returns N/A

I tried making up values with 110.7225 111.7200 112.7175 113.7150 114.7125 115.7100 116.7075 117.7050 118.7025 119.7000 it stops working with 115.7100

I even tried =QUERY(L$4:M,"select M where L='119.7'") it returns ± (the column header) and when I change to =QUERY(L5:M... it returns N/A

Here are L4:M data. All are actual values, not a function result. :

    | L            | M
----+--------------+-----------
  4 | Balance      |  ±
  5 |  99.75000000 |  -6023.86
  6 |  94.76250000 |  -1776.75
  7 |  89.77500000 |   2468.11
  8 |  85.60030690 |   6062.98
  9 |  84.78750000 |   6762.90
 10 |  79.80000000 |  11107.56
 11 |  74.81250000 |  15501.55
 12 |  77.11415433 |  13444.10
 13 |  79.80000000 |  11029.79
 14 |  75.81250000 |  14543.18
 15 |  74.81250000 |  15423.28
 16 |  75.43038050 |  14873.37
 17 |  79.80000000 |  10984.45
 18 |  84.78750000 |   6570.56
 19 |  89.77500000 |   2136.72
 20 |  99.75000000 |  -6789.91
 21 | 104.07461484 | -10461.51
 22 | 104.73750000 | -11024.30
 23 |  99.75000000 |  -6904.62
 24 | 104.73750000 | -11144.00
 25 |  99.75000000 |  -7099.14
 26 |  89.77500000 |   1190.09
 27 |  84.78750000 |   5434.45
 28 |  85.83562716 |   4543.36
 29 |  80.84812716 |   8787.72
 30 |  79.85062716 |   9670.51
 31 |  79.80000000 |   9714.86
 32 |  84.78750000 |   5002.17
 33 |  89.77500000 |    201.70
 34 |  94.76250000 |  -3783.31
 35 |  99.75000000 |  -7696.00
 36 |  94.76250000 |  -3950.39
 37 |  99.75000000 |  -7835.65
 38 | 104.73750000 |  11611.35
 39 |  99.75000000 |  -8264.74
 40 |  95.00454252 |  -4890.72
 41 |  94.76250000 |  -4718.63
 42 |  89.77500000 |  -1120.15
 43 |  90.74750000 |  -1838.83
 44 |  89.75000000 |  -1105.87
 45 |  84.76250000 |   2539.99
 46 |  79.77500000 |   6205.65
 47 |  89.77500000 |  -1284.35
 48 |  84.78750000 |   2162.01
 49 |  79.80000000 |   5708.12
 50 |  77.06182232 |   7696.04
 51 |  75.34427695 |   8942.98
 52 |  74.81250000 |   9329.05
 53 |  79.80000000 |   5543.54
 54 |  84.78750000 |   1828.35
 55 |  87.68533294 |   -234.88
 56 |  87.84530696 |   -348.78
 57 |  89.77500000 |  -1722.70
 58 |  94.76250000 |  -5208.96
 59 |  99.75000000 |  -8645.35
 60 | 104.73750000 |  11587.98
 61 | 109.72500000 |  14555.04
 62 | 104.73750000 |  11906.68
 63 |  99.75000000 |  -9205.95
 64 | 101.52622830 |  10092.29
 65 | 104.51872830 |  11585.55
 66 | 109.72500000 |  14183.48
 67 |  99.75000000 |  -9186.00
 68 |  89.77500000 |  -3689.77
 69 |  95.90606936 |  -6871.79
 70 |  99.75000000 |  -8866.79
 71 |  89.77500000 |  -3370.56
 72 |  79.80000000 |   2375.04
 73 |  69.82500000 |   8370.02
 74 |  74.81250000 |   5307.69
 75 |  79.80000000 |   2320.18
 76 |  74.81250000 |   5068.29
 77 |  69.82500000 |   8267.77
 78 |  74.81250000 |   4947.69
 79 |  79.80000000 |   1511.30
 80 |  89.77500000 |  -4363.98
 81 |  94.76250000 |  -6982.42
 82 |  99.75000000 |  -9725.05
 83 | 104.73750000 | -12093.61
 84 | 109.68750000 | -14269.09
 85 | 114.71250000 | -16416.54
 86 | 119.68750000 | -18540.99
 87 | 124.68750000 | -20641.54
 88 | 119.70000000 | -18890.88
 89 | 124.68750000 | -20781.14
 90 | 129.67500000 | -22471.90
 91 | 134.66250000 | -24112.79
 92 | 129.67500000 | -22711.30
 93 | 124.68750000 | -21210.06
 94 | 119.70000000 | -19634.01
 95 | 128.07469046 | -22064.35
 96 | 129.67500000 | -22531.64
 97 | 134.66250000 | -23978.76
 98 | 139.65003446 | -25444.60
 99 | 134.66253446 | -24092.99
100 | 139.65000000 | -25536.86

Am I doing anything wrong? Or is there any limitations on the QUERY function?

2
  • Try this query: =QUERY(L4:M;"select M where L='"&K1&"' ORDER BY M DESC LIMIT 1 label M ''")
    – Kos
    Jan 14, 2018 at 16:39
  • Thank you, I forgot the order-limit technique. But your query still N/A. I even tried =QUERY(L4:M,"select M where L='119.7'") (fixed string) it returns ± (the column header)
    – anuith
    Jan 14, 2018 at 18:18

2 Answers 2

0

... it stops working with 115.7100

The formula

=QUERY(L$4:M,"select max(M) where L="&K1&" label max(M)''")

returns N/A because the L column doesn't contain 115.7100.

I even tried =QUERY(L$4:M,"select M where L='119.7'") it returns ± (the column header)

The above formula returns only ± because '119.7' is a text value and L column contains numbers.

1
  • Thanks @Rubén I think it's about the data type of the cells. For 115.71 I made up values in another set of data and it's not in the posted one, sorry for the confusion.
    – anuith
    Jan 19, 2018 at 10:28
0

After I stumbled on this Stack Overflow thread I think I know the cause of my problem.

It should be about the data type of the cells. As the most of data is numbers, some of them are texts.

The data was converted values from formula results. The conversion was made in scripts. Sorry for not posted in the original question.

I think some of them are text because of my manual edits after the code runs.

All I have to do is change data formats for those cells. And to make sure, I did include the function TEXT(L5, "0.00000000") in data cells' and query formula.

So my query is now

=QUERY(L$4:M,"select max(M) where L="&TEXT(K1, "0.00000000")&" label max(M)''")

And I did change to use order-limit query as @Kos's comment. Now my final query is like this

=QUERY(L$4:M;"select M where L='"&TEXT(K1, "0.00000000")&"' order by M desc limit 1 label M ''")

I don't know which one is better. But I prefer the former one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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