Pulling my hair out trying to get a formula to work (or do ANYTHING)

[nigelbradley]

Hi all,
I'm trying to work out the distance between two sets of lat/long coords.

After much searching I found the formula at the bottom
When I use it with either numbers instead of lat/longs or the cell reference my PC does absolutely nothing. It doesn't post anything in the cell at all, not even an error.
I've tried breaking it down into the 5 different component calculations and they work individually.

<tbody>
[TD="class: xl63"]lat1[/TD]
[TD="class: xl63, width: 64"]long1[/TD]
[TD="class: xl63, width: 64"]lat2[/TD]
[TD="class: xl63, width: 64"]long2[/TD]

[TD="class: xl64"]54.99894[/TD]
[TD="class: xl64"]-1.49062[/TD]
[TD="class: xl64"]55.00885[/TD]
[TD="class: xl64"]-1.5185
[/TD]

</tbody>

=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-C2)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-C2)) *COS(RADIANS(B2-D2)))





=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

 

[BrianJN1]

I don't know what you are doing but when I copy your data,
and then paste your formula in a cell I get the value
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]0.423551
[/TD]
[/TR]
</tbody>[/TABLE]


Is that the value which you expect?

 

[footoo]

This might help : http://hrsatl.com/

 

[nigelbradley]

That is what I'm expecting although mine does nothing at all

 

[BrianJN1]

Hm. I just repeated that and got a different result, 0.000328. I didn't save my first attempt at this so I can't be sure if I entered the correct values at that time.

 

[FormR]

It doesn't post anything in the cell at all, not even an error.

Hi, welcome to the board - a few things that you could check that might help:

1. Check that you can see the formula in the formula bar.
2. Check that the font colour is not set to either white or the same as the background colour of the cell.
3. Check that the number format of the cell is not hiding the result (temporarily change to general to see if it resolves the problem)
4. Check that there is no conditional formatting that might be hiding the result (temporarily remove any CF to see if it resolves the problem)

 

[wideboydixon]

I found an implementation of haversine which is slightly different. I also included your original formula (although I changed the layout a bit). Both give the same result for your data but I think you need to multiple by the radius of the earth (in km or miles) to get the actual distance.

Book1
	A	B	C	D	E
1		Latitude	Longitude	Latitude Radians	Longitude Radians
2	Point 1	54.99894	-1.49062	0.959912588	-0.026016227
3	Point 2	55.00885	-1.5185	0.96008555	-0.026502825
4	Difference			0.000172962	-0.000486598
5	a, c			2.69495E-08	0.000328326
6	Radius (km, miles)			6371	3959
7	Distance (km, miles)			2.091766817	1.299843796
8	Original calculation				0.000328326
9	Ditance (km, miles)			2.091766817	1.299843797
Sheet1

Cell Formulas
Range	Formula
D2	=RADIANS(B2)
D3	=RADIANS(B3)
D4	=D3-D2
D5	=SIN(D4/2)^2+COS(D2)*COS(D3)*SIN(E4/2)^2
D7	=D6*E5
D9	=D6*E8
E2	=RADIANS(C2)
E3	=RADIANS(C3)
E4	=E3-E2
E5	=2*ATAN2(SQRT(1-D5),SQRT(D5))
E7	=E6*E5
E8	=ACOS(COS(RADIANS(90-B2)) *COS(RADIANS(90-B3)) +SIN(RADIANS(90-B2)) *SIN(RADIANS(90-B3)) *COS(RADIANS(C2-C3)))
E9	=E6*E8

WBD

 

[nigelbradley]

Ha ha, thanks very much but I'm probably past this stage now

 

[nigelbradley]

All sorted now thank you.
It was a schoolboy error on my behalf.
I had downloaded a spreadsheet from the net and hadn't realised the column I was using had a 'style' applied to it meaning it wasn't been shown (but not a white font on white background which I had checked)