Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 04-24-2018, 04:03 PM
macropod's Avatar
macropod macropod is offline trying to find a way to show a closest to using lat and long coordinates Windows 7 64bit trying to find a way to show a closest to using lat and long coordinates Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #17  
Old 04-24-2018, 04:11 PM
Leaffy Leaffy is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2010 64bit
Novice
 
Join Date: Apr 2018
Posts: 2
Leaffy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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).
Forgot the CTRL-Shift for an array formula,

Thanks Macropod
Reply With Quote
  #18  
Old 02-08-2021, 03:25 PM
macropod's Avatar
macropod macropod is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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 ,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]
Reply With Quote
  #19  
Old 01-27-2022, 02:33 PM
mquinn mquinn is offline trying to find a way to show a closest to using lat and long coordinates Windows 11 trying to find a way to show a closest to using lat and long coordinates Office 2021
Novice
 
Join Date: Jan 2022
Posts: 1
mquinn is on a distinguished road
Exclamation

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)
Reply With Quote
  #20  
Old 01-28-2022, 11:57 AM
p45cal's Avatar
p45cal p45cal is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2019
Expert
 
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

There are so many things it might be, best attach a file where it's not working.
Reply With Quote
  #21  
Old 01-28-2022, 12:48 PM
kilroyscarnival kilroyscarnival is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2019
Expert
 
Join Date: May 2019
Posts: 361
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by mquinn View Post
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)
This might be a red herring, but when I copied out your original posted code, it looked like there was a space between the P and the 2 in one of the cell references. When I quoted it above, I now don't see it, so it may just have been a display mirage.

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).
Reply With Quote
  #22  
Old 05-17-2025, 05:42 AM
cwgitahi cwgitahi is offline trying to find a way to show a closest to using lat and long coordinates Windows 11 trying to find a way to show a closest to using lat and long coordinates Office 2021
Novice
 
Join Date: May 2025
Posts: 1
cwgitahi is on a distinguished road
Default

Hello, the code also doesn't seem to be working for my data set.
Attached Files
File Type: xlsx Demo_2.xlsx (201.9 KB, 2 views)

Last edited by cwgitahi; 05-17-2025 at 06:19 AM. Reason: Wrong attachment
Reply With Quote
  #23  
Old 05-17-2025, 07:06 AM
p45cal's Avatar
p45cal p45cal is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2021
Expert
 
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

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!
Reply With Quote
  #24  
Old 05-17-2025, 03:22 PM
macropod's Avatar
macropod macropod is offline trying to find a way to show a closest to using lat and long coordinates Windows 10 trying to find a way to show a closest to using lat and long coordinates Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by cwgitahi View Post
Hello, the code also doesn't seem to be working for my data set.
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]
Reply With Quote
Reply

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
trying to find a way to show a closest to using lat and long coordinates Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
trying to find a way to show a closest to using lat and long coordinates 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft