# calculate the distance between two points

#### 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: 16
Could someone help me?

#### claudia80​

What did not work well?
#1 Could You select eg cell K2?
#2 Select end of that formula
#3 Press <ENTER>

#### claudia80​

What did not work well?
#1 Could You select eg cell K2?
#2 Select end of that formula
#3 Press <ENTER>
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.

I tried to do as you wrote but nothing changes

... tried?
Try this way:
#1 Select cell K2

#2 Select end of formula in cell K2

#3 Press <ENT>

# Cell K2 should look like above.

#### claudia80​

Above steps works with cell K2.
If You want to do same with K3 then do same steps by changing cell K2 to K3.

#### claudia80​

Above steps works with cell K2.
If You want to do same with K3 then do same steps by changing cell K2 to K3.
I have already tried several times and it does not work otherwise I would not have asked for help

#### 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.

#### 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,

=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.

#### 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​

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.

Peter Bartholomew
All of those cells are texts ... traditional copy & paste case.
It's a challenge to get answers.

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.

 geonameid name asciiname alternatenames latitude longitude feature class feature code country code cc2 admin1 code admin2 code admin3 code admin4 code population elevation dem timezone modification 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?

#### 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?
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