Jeroen89
09-17-2019, 01:28 AM
Dear
I want to look up the closest value to a given set of azimuths.
The given azimuths (B2:B5) change per project and in some cases a 4'th sector will be given. The formula should thus work with both 3 and 4 given azimuths. (azimuths from different sectors will not be identical).
The goal is to establish for each 'checkpoint's' azimuth value (column F) the closest sector azimuth. In the example, this 30°, 160° or 250°.
Amount of checkpoints are unknown.
Currently i'm using the following formula, this works great, except that the formula doesn't return after 360°.
For example, checkpoint 6 is located on azimuth 345,4° --> this is closest to sector 1's 30°. But my formula returns S2 (250°) since the formula doesnt 'return' after 360°.
This is my current formula: =ALS(OF($B$2="";$B$3="";$B$4="");"Missing AGL data!";ALS(F6="";"";ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX($B$2:$B$5;VERGELIJKEN(MIN(ABS($B$2:$B$5-F6));ABS($B$2:$B$5-F6);0));$B$2:$B$5;2;ONWAAR));ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX($B$2:$B$4;VERGELIJKEN(MIN(ABS($B$2:$B$4-F6));ABS($B$2:$B$4-F6);0));$B$2:$B$4;1;ONWAAR));"Missing AGL data!"))))
Formula is also present in the attached example sheet, incorrect fields are marked in Red.
Thanks for your assistance.
Kind regards
Jeroen
I want to look up the closest value to a given set of azimuths.
The given azimuths (B2:B5) change per project and in some cases a 4'th sector will be given. The formula should thus work with both 3 and 4 given azimuths. (azimuths from different sectors will not be identical).
The goal is to establish for each 'checkpoint's' azimuth value (column F) the closest sector azimuth. In the example, this 30°, 160° or 250°.
Amount of checkpoints are unknown.
Currently i'm using the following formula, this works great, except that the formula doesn't return after 360°.
For example, checkpoint 6 is located on azimuth 345,4° --> this is closest to sector 1's 30°. But my formula returns S2 (250°) since the formula doesnt 'return' after 360°.
This is my current formula: =ALS(OF($B$2="";$B$3="";$B$4="");"Missing AGL data!";ALS(F6="";"";ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX($B$2:$B$5;VERGELIJKEN(MIN(ABS($B$2:$B$5-F6));ABS($B$2:$B$5-F6);0));$B$2:$B$5;2;ONWAAR));ALS.FOUT(ALS(F6="";"";VERT.ZOEKEN(INDEX($B$2:$B$4;VERGELIJKEN(MIN(ABS($B$2:$B$4-F6));ABS($B$2:$B$4-F6);0));$B$2:$B$4;1;ONWAAR));"Missing AGL data!"))))
Formula is also present in the attached example sheet, incorrect fields are marked in Red.
Thanks for your assistance.
Kind regards
Jeroen