#16
|
||||
|
||||
Two things - you would have to change the '151' references in the formula to 766 and you must enter the formula as an array formula (Ctrl-Shift-Enter).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#17
|
|||
|
|||
Quote:
Thanks Macropod |
#18
|
|||
|
|||
The formula works perfectly, but I wonder if is possible to arrange it in such a way that:
1. It gives the nearest result, the second, the third and so on. 2. Given a max distance, it shows the closest result without going over this max distance given. Thanks in advance! |
#19
|
||||
|
||||
For (1), you could replace the MIN function in the formula with the SMALL function. For example:
=INDEX(Well!C:C,MATCH(SMALL(ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A $151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well! A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),2),ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well! A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well !A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),0)+1,0) SMALL ,1 gives the minimum SMALL ,2 gives the second smallest, and so on. To get the corresponding distances, you'd make the same changes to the distance formula. As for (2), you'd probably need to create a lookup table for the distances and use a formula to retrieve the one you're after.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#20
|
|||
|
|||
Quote:
It works perfectly for the desired purpose |
#21
|
|||
|
|||
This formula is perfect for what I am looking for but it is not working, I keep getting the #VALUE error.
I did CTRL+SHIFT+ENTER, made sure that all the numbers were numbers and not texts. Any other thoughts? Below is the formula I did for mine. CODE: =INDEX(COLI!I:I,MATCH(MIN(ACOS(SIN(RADIANS(O2))*SI N(RADIANS(COLI!$E$5:COLI!$E$372))+COS(RADIANS(O2)) *COS(RADIANS(COLI!$E$5:COLI!$E$372))*COS(RADIANS(P 2-COLI!$F$5:COLI!$F$372)))),ACOS(SIN(RADIANS(O2))*SI N(RADIANS(COLI!$E$5:COLI!$E$372))+COS(RADIANS(O2)) *COS(RADIANS(COLI!$E$5:$E$372))*COS(RADIANS(P2-COLI!$F$5:COLI!$F$372))),0)+1,0) |
#22
|
||||
|
||||
There are so many things it might be, best attach a file where it's not working.
|
#23
|
|||
|
|||
Quote:
Already bookmarked this thread, because I work with engineers who do use lat/long coordinates (and occasionally get to convert northing/easting to lat/long for them). |
Tags |
find the closest, lat and long |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Show two alternating slides as long as music plays | Magic | PowerPoint | 2 | 11-23-2014 09:02 AM |
Bad view when using Find and Find & Replace - Word places found string on top line | paulkaye | Word | 4 | 12-06-2011 11:05 PM |
Highlight and put bookmark on the closest field to the cursor | b0x4it | Word VBA | 11 | 05-19-2011 11:15 PM |
Question: Hyperlink to other show, but close the current show | habibfikri | PowerPoint | 0 | 12-26-2010 02:37 AM |
PP 2010 .avi file plays during slide show, but causes the slide show to loop to begin | VictorS | PowerPoint | 0 | 10-16-2010 10:23 AM |