Latitude and Longditude

[masplin]

I have location information in the format of decimal latitude and longditude in my data. I want to create a pivot with distances between stores and users. I have the formula that works in Excel, but it won't work in powerpivot as RADIANS and maybe other Trig functions aren't recognised.

0.621371*(RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(D71)-RADIANS(D72))/2)^2)+COS(RADIANS(D71))*COS(RADIANS(D72))*(SIN((RADIANS(E71)-RADIANS(E72))/2)^2))))))

Is there anyway round this please?

Mike

 

[dhExcel]

Has to be done "beside" the pivot table. No other workaround.

 

[masplin]

Ok worked out how to convert all the lat and long into a mile grid and then use pythagoras to work out distances.

cheers

 

[dhExcel]

All?

 

[masplin]

Well 20,00 users converted the first part of post code into Lat and Long and then into mile grid. Works approx over short distances.