Oct 07 2021 08:47 AM
| I want to search the array for a value, have the array evaluate the number and return a value equal to or greater than the searched value and get the row and column information of the intersection point. See the array below. E.G. input 26, get 85 & 109 as the response. Thanks in advance for any help you can provide. | |||||
| ||||||
88 | 85 | 80 | 75 | 70 | 65 | |
68 | 36.7 |
|
|
|
|
|
70 | 36.1 |
|
|
|
|
|
80 | 33.1 |
|
|
|
|
|
84 | 31.8 | 34.1 |
|
|
|
|
90 | 29.8 | 32.1 |
|
|
|
|
95 | 27.9 | 30.6 |
|
|
|
|
100 | 25.9 | 29.0 |
|
|
|
|
109 | 22.3 | 26.5 | 29.5 |
|
|
|
110 | 21.9 | 26.2 | 29.3 |
|
|
|
115 | 20.3 | 24.6 | 27.9 |
|
|
|
120 | 18.6 | 23.0 | 26.5 |
|
|
|
125 | 17.3 | 21.3 | 25.2 |
|
|
|
130 | 16.0 | 19.5 | 23.9 |
|
|
|
134 | 15.1 | 18.3 | 22.9 | 25.5 |
|
|
140 | 13.8 | 16.6 | 21.5 | 24.0 |
|
|
145 | 13.0 | 15.4 | 20.3 | 22.9 |
|
|
150 | 12.1 | 14.2 | 19.0 | 21.7 |
|
|
158 | 11.0 | 12.7 | 16.4 | 19.9 | 20.3 |
|
160 | 10.7 | 12.3 | 15.7 | 19.5 | 20.0 |
|
165 | 10.2 | 11.5 | 14.4 | 18.5 | 19.2 |
|
170 | 9.7 | 10.7 | 13.1 | 17.4 | 18.3 |
|
175 | 9.1 | 10.0 | 12.1 | 15.8 | 17.6 |
|
180 | 8.4 | 9.3 | 11.0 | 14.2 | 16.8 |
|
181 | 8.3 | 9.2 | 10.8 | 13.9 | 16.7 | 15.4 |
190 | 7.3 | 8.3 | 9.3 | 11.6 | 15.5 | 14.2 |
195 | 6.7 | 8.0 | 8.7 | 10.6 | 14.1 | 13.7 |
200 |
| 7.6 | 8.0 | 9.6 | 12.6 | 13.1 |
202 |
| 6.5 | 7.8 | 9.3 | 12.1 | 12.9 |
210 |
|
| 7.1 | 8.0 | 10.1 | 12.0 |
213 |
|
| 6.2 | 7.6 | 9.5 | 11.7 |
220 |
|
|
| 6.7 | 8.1 | 10.9 |
224 |
|
|
| 6.0 | 7.5 | 9.9 |
230 |
|
|
|
| 6.6 | 8.5 |
235 |
|
|
|
| 5.8 | 7.5 |
240 |
|
|
|
|
| 6.5 |
245 |
|
|
|
|
| 5.5 |
|
|
|
|
|
|
|
Oct 07 2021 10:11 AM
Could you please explain bit more your example. Greater or equal to 26 is 26.2. It gives 80 and 110. Please see attached.
Oct 11 2021 05:57 AM
This is a crane capacity chart. In many occasions the weight if a lifted load is known. The "idea" would require inputting the weight then searching multiple capacity charts to assist in selecting a crane.
Thanks
Oct 13 2021 04:42 AM
The example you sent is essentially what I'm trying to accomplish.
Thanks for your time.
Oct 13 2021 07:01 AM
As variant that could be
=INDEX(rowData, INDEX( SUMPRODUCT((data=MINIFS(data,data,">=" & M6))*ROW(data))-ROW(data)+1, 1))
for the value in left row and
=INDEX(columnData, INDEX( SUMPRODUCT((data=MINIFS(data,data,">=" & M6))*COLUMN(data))-COLUMN(data)+1, 1))
for one in columns:
Oct 13 2021 07:18 AM