![]() |
#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
|
||||
|
||||
![]()
For (1), you could replace the MIN function in the formula with the SMALL function. For example:
Code:
=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 ,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] |
#19
|
|||
|
|||
![]()
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) |
#20
|
||||
|
||||
![]()
There are so many things it might be, best attach a file where it's not working.
|
#21
|
|||
|
|||
![]() 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). |
#22
|
|||
|
|||
![]()
Hello, the code also doesn't seem to be working for my data set.
Last edited by cwgitahi; 05-17-2025 at 06:19 AM. Reason: Wrong attachment |
#23
|
||||
|
||||
![]()
The attached is a bit of a guess.
1. You use SMALL which when met with cells that contain errors (eg. rows 218,799,1972 and 10 more in the ATC sheet) always returns an error. I have changed this for the equivalent AGGREGATE function adding the option to ignore errors. 2. I suspect you have your x/y coordinate references back to front. I have swapped (eg. in row 7) all instances of B7 for C7 and vice versa. This is only a guess and I haven't examined closely if the formula is still logically correct. You should check this for yourself and the results. The file was too big to attach so here's a link instead: Excel workbook Edit: I was too late to see that you updated your attached workbook but I see that you've swapped the x/y columns on one sheet. You also changed the SMALL to MIN but this still throws an error (even if you use the MIN equivalent AGGREGATE function) so I tried the AGGREGATE equivalent of SMALL (AGGREGATE(15,6…)) which seemed to work but gave different results - so do check! |
#24
|
||||
|
||||
![]()
It doesn't work for the simple reason you have dozens of cells with #N/A in columns A & B of your ATC sheet. Works just fine if you delete the #N/A entries or, better still, correct them or delete their rows.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
![]() |
Tags |
find the closest, lat and long |
|
![]() |
||||
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 |
![]() |
paulkaye | Word | 4 | 12-06-2011 11:05 PM |
![]() |
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 |