Calculating the distance customers live from a branch office
 

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

Calculating the distance customers live from a branch office
 

On Sep 5, 11:25 am, Steve Cornelius wrote:
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

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

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

Calculating the distance customers live from a branch office
 

On Fri, 5 Sep 2008 09:25:52 -0700 (PDT), Steve Cornelius
wrote:

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

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

Calculating the distance customers live from a branch office
 

On Sep 5, 12:34*pm, Spiky wrote:
On Sep 5, 11:25 am, Steve *Cornelius wrote:





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

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

http://www.cpearson.com/excel/latlong.htm- Hide quoted text -

- Show quoted text -

Thanks. Actually, I DID see that page doing a Google search, but my
data is not formatted that way.

SC

Calculating the distance customers live from a branch office
 

On Sep 5, 12:51*pm, Ron Rosenfeld wrote:
On Fri, 5 Sep 2008 09:25:52 -0700 (PDT), Steve *Cornelius





wrote:
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

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 -

That did it!!! Thanks a bunch.

SC

Calculating the distance customers live from a branch office
 

On Fri, 5 Sep 2008 13:13:55 -0700 (PDT), Steve Cornelius
wrote:

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 -

That did it!!! Thanks a bunch.

SC

You're welcome. Glad to help.
--ron