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: 21,956
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, 12:18 PM
Snazzy Snazzy 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
Novice
 
Join Date: Jun 2020
Posts: 7
Snazzy is on a distinguished road
Default

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!
Reply With Quote
  #19  
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: 21,956
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:
=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
  #20  
Old 02-12-2021, 04:50 AM
Snazzy Snazzy 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
Novice
 
Join Date: Jun 2020
Posts: 7
Snazzy is on a distinguished road
Thumbs up

Quote:
Originally Posted by macropod View Post
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.
Thanks!!
It works perfectly for the desired purpose
Reply With Quote
  #21  
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
  #22  
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: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

There are so many things it might be, best attach a file where it's not working.
Reply With Quote
  #23  
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: 344
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
  #24  
Old 01-31-2022, 03:47 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer 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
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

This OLD thread has been marked as " solved", so there is a good chance nobody will open it ( hijacking threads is not the best way to get help in forums).
You should create a new thread and eventually add a link to this one
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #25  
Old 01-31-2022, 04:00 AM
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: 21,956
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 Pecoflyer View Post
This OLD thread has been marked as " solved", so there is a good chance nobody will open it.
Given that it's already had two constructive replies, that hardly applies here...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
find the closest, lat and long

Thread Tools
Display Modes


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 02:39 AM.


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