Excel is efficient in conducting any type of calculation. Therefore, users trying to calculate coordinates from bearing and distance Excel is not absurd. A simple understanding of geographic Northing and Easting in respective bearing and distance can result in coordinates.
Let’s say we have a reference point (i.e., New York City coordinate). From the reference point, we have multiple points maintaining intervals of certain distances. We want to calculate those points’ coordinates in Excel.
In this article, we demonstrate the step-by-step process of the Latitude and Departure method to calculate coordinates from bearing and distance Excel.
What Is Coordinate (Northing and Easting)?
The geographic Cartesian Coordinates of a point on earth are referred to as Easting (the x value), and Northing (the Y value). The distance (L) is referred to as the Orthogonal Coordinate measured from a horizontal datum. Here, we use the method of Latitude and Departure to find the Northing and Easting thus calculating the Coordinate.
How to Calculate Coordinates from Bearing and Distance in Excel: Step-by-Steps
Before commencing the calculation, users need to gather their source data such as the bearing and distance of lines connecting two or multiple points. As Excel only takes bearings in Radians, it’s convenient that users first convert the bearing readings into radians. Follow the below steps to calculate the coordinates from bearing and distance in Excel.
🔄 Setting Up Bearings and Distances
Arrange the given bearing (i.e., Degrees, Minutes, Seconds) and distance readings in an organized manner in Excel Worksheet. Also, provide a clarified naming along with their units (i.e., Meter or Degree, Minute, Second) of the given data as shown in the image below.
🔄 Bearing Conversion in Radian
As we mentioned earlier, Excel only takes bearings in Radian; users need to convert all the bearing readings into radians.
➤ Use the below formula to convert Degrees, Minutes, and Seconds into Radians.
=RADIANS(E7+(G7/60)+(I7/3600))
In the formula, G7/60 converts the Minutes into Degrees, I7/3600 the Seconds into Degrees, and at last the RADIANS function converts the Degree values into Radians.
➤ Drag the Fill Handle to apply the formula in other cells as depicted in the below image.
🔄 Finding Latitude and Departure
Additional LatitudeÂ
The Latitude is the product of the Cosine value of the Bearing and the Distance (L).
➤ Type the following COS function in cell L7.
=D7*COS(K7)
➤ Afterward, drag the Fill Handle to execute the formula in other cells.
Thus, you get the additional Latitude from your reference point (i.e., New York City).
Additional Departure
Departure is the horizontal distance from a reference point. We can find the additional Coordinate value of random points or connecting lines to our reference Coordinate.
Alternative to the Latitude, Departure is the product of the Sine value and Distance.
➤ Write the below formula in any blank cell (i.e., M7)
=D7*SIN(K7)
➤ Now, apply the Fill Handle to display all the Departure values in the cells as depicted in the below picture.
🔄 Calculating Coordinates from Bearing and Distance
Users need a reference point (i.e., New York City Coordinates) to measure the coordinates of regular intervals and bearings. There is a reference Coordinates in the dataset and users have to add each additional Latitude and Departure to find respective Northing and Easting.
Point Northing
➤ Add the additional Latitude to the reference Northing to find the final Northing of any point.
=L7+N6
➤ After that, drag the Fill Handle to make appear all the Northing Coordinate values similar to the image below.
Point Easting
➤ Again, use the Addition Operator to find the final Easting of any points.
=M7+O6
➤ To execute the addition operator in other cells, use the Fill Handle.
In the end, you get the Coordinates from bearing and distance.
Read More: How to Calculate Distance between Two GPS Coordinates in Excel
Practice Section
You can practice the coordinate calculation using the attached Dataset. We provide the raw data in the Practice Worksheet as depicted in the below image.
Conclusion
In this article, we demonstrate the concept of Coordinates and its components such as Northing and Easting. Also, we calculate coordinates from bearing and distance Excel. Hope you find this described method helpful in your case. Comment, if you have further inquiries or have anything to add.
Related Articles
- How to Calculate Distance Between Two Addresses in Excel
- How to Calculate Miles between Two Addresses in Excel
- How to Find Distance Between Zip Codes in Excel Using Formula
- How to Calculate Distance Between Two Cities in Excel
- How to Calculate Distance in Excel with Google Maps
- How to Make a Mileage Log in Excel
- Excel VBA: Calculate Distance Between Two Addresses or Coordinates
Thanks! Very helpful
Welcome @Janice
How do you factor in the East or West of the bearing – does this need to be accounted for? For example, should 19 degrees East be positive radians whereas if it were West, it would be negative Radians – or am I not understanding something. I am trying to use this process on a set of legal descriptions, and so far, it’s not aligning with a known map.
Hello Hannah!
Thanks for reaching out! You are correct. The direction (East or West) of the bearing is crucial, and it needs to be accounted for in the calculation of radians.
In navigation and mapping, the convention is usually to measure bearings clockwise from north, meaning that eastward directions are considered positive, and westward directions are considered negative. Bearings can be given in degrees, minutes, and seconds or in radians. To account for this, you should modify the formula for converting bearings to radians as follows:
For East Bearings: Simply convert degrees, minutes, and seconds to radians as previously described, and the result will be positive.
=RADIANS(Degrees + (Minutes / 60) + (Seconds / 3600))
For West Bearings: Convert degrees, minutes, and seconds to radians as previously described, but make the result negative.
=-RADIANS(Degrees + (Minutes / 60) + (Seconds / 3600))
By introducing the negative sign for West bearings, you account for the direction correctly. Positive radians represent East bearings, and negative radians represent West bearings.
Please keep in mind,
Bearings measured clockwise from true north: East is positive. West is negative.
Bearings measured in radians: Counterclockwise is positive. Clockwise is negative.
If you have any further queries, please inform us in the reply section. Thanks.
Best Regards,
ExcelDemy Team