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?
=QUERY(L4:M;"select M where L='"&K1&"' ORDER BY M DESC LIMIT 1 label M ''")
N/A
. I even tried=QUERY(L4:M,"select M where L='119.7'")
(fixed string) it returns±
(the column header)