Calculating the distance customers live from a branch office

  • Thread starter Steve Cornelius
  • Start date
S

Steve Cornelius

I work for a bank in the Chicago area. We have a number of branch
offices and are interested in determining how far our customers live
from their bank branch.

My data is displayed in Excel similar to below:

BRANCH LATITUDE LONGITUDE CUSTOMER LATITUDE
LONGITUDE DISTANCE (IN MILES)
Main Office 41.9034371 -87.667583 John Doe
42.123456 -88.123456 ?????
South Office 41.6009371 -87.604676 Mary Jones
41.654321 -87.654321 ?????

If, for example, each branch had 500 customers, I want to be able to
calculate the distance in miles that each customer lives from their
branch ("as the crow flies").

I have found several websites that will calculate the distance of two
individual points, but my total dataset contains over 30,000 records!
I am hoping there is a formula I can use to reference the Latitude and
Longitude.

I have Excel 2003 at work and Excel 2007 at home.

Any suggestions will be very much appreciated.

Steve Cornelius
 
S

Spiky

I work for a bank in the Chicago area. We have a number of branch
offices and are interested in determining how far our customers live
from their bank branch.

My data is displayed in Excel similar to below:

BRANCH LATITUDE LONGITUDE CUSTOMER LATITUDE
LONGITUDE DISTANCE (IN MILES)
Main Office 41.9034371 -87.667583 John Doe
42.123456 -88.123456 ?????
South Office 41.6009371 -87.604676 Mary Jones
41.654321 -87.654321 ?????

If, for example, each branch had 500 customers, I want to be able to
calculate the distance in miles that each customer lives from their
branch ("as the crow flies").

I have found several websites that will calculate the distance of two
individual points, but my total dataset contains over 30,000 records!
I am hoping there is a formula I can use to reference the Latitude and
Longitude.

I have Excel 2003 at work and Excel 2007 at home.

Any suggestions will be very much appreciated.

Steve Cornelius
Click to expand...

Maybe you should've added "excel formula" to your website search. Try
this:

http://www.cpearson.com/excel/latlong.htm
 
R

Ron Rosenfeld

I work for a bank in the Chicago area. We have a number of branch
offices and are interested in determining how far our customers live
from their bank branch.

My data is displayed in Excel similar to below:

BRANCH LATITUDE LONGITUDE CUSTOMER LATITUDE
LONGITUDE DISTANCE (IN MILES)
Main Office 41.9034371 -87.667583 John Doe
42.123456 -88.123456 ?????
South Office 41.6009371 -87.604676 Mary Jones
41.654321 -87.654321 ?????

If, for example, each branch had 500 customers, I want to be able to
calculate the distance in miles that each customer lives from their
branch ("as the crow flies").

I have found several websites that will calculate the distance of two
individual points, but my total dataset contains over 30,000 records!
I am hoping there is a formula I can use to reference the Latitude and
Longitude.

I have Excel 2003 at work and Excel 2007 at home.

Any suggestions will be very much appreciated.

Steve Cornelius
Click to expand...

I think this will work:

=DEGREES(ACOS(SIN(RADIANS(B2))*SIN(RADIANS(E2))+
COS(RADIANS(B2))*COS(RADIANS(E2))*COS(RADIANS(C2)-
RADIANS(F2))))*60*1.15

B2: Latitude of position 1 in degrees
C2: Longitude of position 1 in degrees
E2: Latitude of position 2
F2: Longitude of position 2

Result in SM
--ron
 
S

Steve Cornelius

I think this will work:

=DEGREES(ACOS(SIN(RADIANS(B2))*SIN(RADIANS(E2))+
COS(RADIANS(B2))*COS(RADIANS(E2))*COS(RADIANS(C2)-
RADIANS(F2))))*60*1.15

B2:  Latitude of position 1 in degrees
C2:  Longitude of position 1 in degrees
E2:  Latitude of position 2
F2:  Longitude of position 2

Result in SM
--ron- Hide quoted text -

- Show quoted text -
Click to expand...

That did it!!! Thanks a bunch.

SC
 
Top