| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time from Speed and Distance? | Excel Worksheet Functions | |||
| Write a branch to another macro | Excel Worksheet Functions | |||
| My office 2007 live preview function is not working. Know why?? | Excel Discussion (Misc queries) | |||
| How can I contact branch Office in Tokyo, Japan? | New Users to Excel | |||
| Establish a Branch of Clayton College here in Malaysia | Excel Worksheet Functions | |||
Linear Mode
