• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

calculate the distance between two points

C

claudia80

Member
Good morning.
I'm having trouble with a formula for calculating the distance between two points in km and meters.
In the first sheet you will find the formulas that I have tried but which do not work well.
Thank you
 

Attachments

  • 1.xlsx
    12.9 KB · Views: 17
vletm said:

claudia80​

What did not work well?Immagine 2023-09-14 131101.png
#1 Could You select eg cell K2?
#2 Select end of that formula
#3 Press <ENTER>
Click to expand...
I seem to have understood that at the coordinates I have to replace the period with the comma. Then the formulas in columns "k", "l" and cell O3 appear as if there were no "=" before the formula or I find "#NOME?"
 

claudia80​

If there is no "=" before (in the beginning of ) the formula then ... it'll act as a text.
Did You try to do those three steps which I wrote?
It's same as edit that cell K2 without any changes.
For me, that helps at once.
 
... tried?
Try this way:
#1 Select cell K2
Screenshot 2023-09-14 at 18.22.43.png
#2 Select end of formula in cell K2
Screenshot 2023-09-14 at 18.22.52.png
#3 Press <ENT>
Screenshot 2023-09-14 at 18.23.01.png
# Cell K2 should look like above.
 

claudia80​

One more try (for me above works) ... hmm?
You expect to get numbers to those cells (K2:L3)
... then make sure that those cells are Formatted as General or Number ( in Your file those are Texts )
Sometimes it helps it add in front of formula ' >> press <ENT> >> take away that ' >> press <ENT>
 

Attachments

  • 1.xlsx
    13.2 KB · Views: 2
I didn't post this first time round for the fear of creating more confusion. It is based upon the layout of sheet 2 in which way-points are used to define a multi-stage route.
1694960089648.png
 

Attachments

  • distances.xlsx
    32 KB · Views: 7
In addition to our Vletm's explanation.

1] Maybe your Excel do not support this @ sign

2] @ sign is popularly knows as Implicit intersection operator to support Dynamic Array formula method. It can be found in Excel 2019 & Office 365.

So,

In your K2 formula:
=6371*@ACOS(@SIN(DEGREES(F2))*@SIN(DEGREES(H2))+COS(DEGREES(F2))*COS(DEGREES(H2))*COS(DEGREES(G2-I2)))

Just remove the @ sign, and become >>

=6371*ACOS(SIN(DEGREES(F2))*SIN(DEGREES(H2))+COS(DEGREES(F2))*COS(DEGREES(H2))*COS(DEGREES(G2-I2)))

Good luck

Regards
 

bosco_yip​

Do You see those @ somewhere?
With my Excel ( 2019 Mac ) - I cannot see those as well as I cannot see those in #4 reply.
Those formulas seems to show results after do my named steps - one-by-one.
 
vletm said:

bosco_yip​

Do You see those @ somewhere?
With my Excel ( 2019 Mac ) - I cannot see those as well as I cannot see those in #4 reply.
Those formulas seems to show results after do my named steps - one-by-one.
Click to expand...

@vletm​

Yes, with my MS Excel 2021, I can see the @ sign

It also work after do you named step by step.

I think the formula copy/paste from web by the OP and resulted "#NOME?" , because his Excel version does not support the @ sign .
 
HI.
I opened your file and the distance appears even if the coordinates are expressed with a ".". To obtain the same value, however, I would have to replace the dot."" with the comma",".
Then the only formula that works for me is that from 440 m it becomes 11000 (if the "." is used).
The forest formula on this PC doesn't work either with @ or without.
Instead for peter's file all "#NAME?"
 

claudia80​

Have You copy and pasted those Your files texts (which look like numbers) from somewhere?
Hai copiato e incollato i testi dei tuoi file (che sembrano numeri) da qualche parte?
 
@vletm I believe both the latitude and longitudes on 'Foglio1' are text.
I re-entered the values on 'foglio2' to convert them to numbers but I suspect the OP does not use 365 and hence cannot access LAMBDA.
 
on the geonames website you can find files on the cities and geographical coordinates for each state. For the links below I downloaded the IT files.
I did the copy and paste as text otherwise the coordinates in the excel file appear as numbers.

LINK:

GeoNames

download.geonames.org download.geonames.org

GeoNames

download.geonames.org download.geonames.org
 
the excel file is large for uploading. I paste the colon headers for each file below.

country codepostal codeplace nameadmin name1admin code1admin name2admin code2admin name3admin code3latitudelongitudeaccuracy

geonameidnameasciinamealternatenameslatitudelongitudefeature classfeature codecountry codecc2admin1 codeadmin2 codeadmin3 codeadmin4 codepopulationelevationdemtimezonemodification date
 

claudia80​

Your I did the copy and paste as text otherwise the coordinates in the excel file appear as numbers.
Do You mean that You would like to calculate ... texts?
 
vletm said:

claudia80​

Your I did the copy and paste as text otherwise the coordinates in the excel file appear as numbers.
Do You mean that You would like to calculate ... texts?
Click to expand...
if I put a comma instead of a period, the calculation works even if the formatting occurs as text.
The problem is the formula. Why do they sometimes work and sometimes they don't?
If I don't paste as text the data is recognized as a number and not as coordinates
 
You must log in or register to reply here.
Back
Top