Aung Shine
We can perform various tasks in MS Excel. It provides many different built-in functions. These functions help us to undertake numerous mathematical operations. Sometimes, we may need to calculate ZIP to ZIP Mileage for a certain purpose. But, this calculation process is not easy. Because we have to consider multiple things. In this article, we’ll show you the effective ways to Create a ZIP to ZIP Mileage Calculator in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
2 Effective Ways to Create ZIP to ZIP Mileage Calculator in Excel
We’ll need the Latitudes and the Longitudes along with the ZIP codes to find out the ZIP to ZIP Mileage. To illustrate, we’ll use a sample dataset as an example. For instance, in the following dataset, we have ZIP codes. We also have Latitudes and Longitudes of different locations. Here, we’ll determine the Mileage in the H column.
1. Create Excel Formula to Get ZIP to ZIP Mileage Calculator
The given Latitudes and Longitudes are in Degree format. We have to convert them to Radian. We’ll use the RADIAN function to change the format. The SIN function gives the sine of an angle. The COS function generates the cosine of the angle. The ACOS function will generate the arccosine of a number. The arccosine is an angle. The cosine of the angle is a number. Therefore, follow the steps below to combine all these functions to create a formula to get ZIP to ZIP Mileage Calculator in Excel. Lastly, we’ll multiply by 3958.8, which is the Radius of the Earth in Mile.
STEPS:
- First, select cell H5.
- Then, type the formula:
=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-F5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-F5))*COS(RADIANS(D5-G5)))*3958.8- After that, press Enter. Thus, it’ll return the desired output.
- Subsequently, use the AutoFill tool.
- Hence, you’ll see all the results in the H column.
🔎 How Does the Formula Work?
- COS(RADIANS(90-C5))*COS(RADIANS(90-F5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-F5))*COS(RADIANS(D5-G5))
This part of the formula returns the output of the trigonometry operators.
- ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-F5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-F5))*COS(RADIANS(D5-G5)))
The ACOS function generates the inverse cosine value (arccosine).
- ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-F5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-F5))*COS(RADIANS(D5-G5)))*3958.8
Lastly, the multiplication of 3958.9 converts the output into Miles.
Read More: How to Calculate Mileage in Excel (Step-by-Step Guide)
2. Make ZIP to ZIP Mileage Calculator Using Power Query in Excel
Another method to create the ZIP to ZIP Mileage Calculator is through the Excel Power Query Editor. Power Query tool is available in MS Excel. It helps to import data from various sources. We can then transform or modify the data as per our requirements. So, learn the steps below to carry out the operation.
STEPS:
- Firstly, go to the Data tab.
- There, select From Table/Range.
- As a result, a dialog box will pop out.
- Now, choose your data range and press OK.
- Consequently, the Power Query Editor will appear.
- See the below picture to understand better.
- Next, under the Add Column tab, select Custom Column.
- Thus, the Custom Column dialog box will emerge.
- In the New column name field, type Lat1_Rad or anything you wish.
- Afterward, in the Custom column formula box, insert the below formula:
([Latitude] / 180) * Number.PI
- Then, press OK.
- Repeat the above steps 3 more times. In this way, it’ll return the Latitudes and the Longitudes in the Radian format.
- In the formula section, type the respective Column header’s name (Longitude, Latitude3, Longitude4) in Latitude’s place.
- In the following image, you’ll see the 4 new columns in the data range.
- Again, insert one last custom column.
- Type Mileage in the New column name field.
- After that, in the Custom column formula box, input the following formula:
Number.Acos(Number.Sin([Lat1_Rad]) * Number.Sin([Lat2_Rad]) + Number.Cos([Lat1_Rad]) * Number.Cos([Lat2_Rad]) * Number.Cos([Lon2_Rad]-[Lon1_Rad])) * 3959- Lastly, press OK.
- Thus, it’ll return the new column named Mileage with all the precise calculations.
- Now, select Close & Load under the Home tab.
- This will load the Power Query findings in the Excel worksheet.
- Hence, it’ll return a new worksheet with all the results we found.
- In this way, you can get the Mileage.
Read More: How to Make Daily Vehicle Mileage and Fuel Report in Excel
Conclusion
Henceforth, follow the above-described methods to Create a ZIP to ZIP Mileage Calculator in Excel. Keep using them. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
