# How to calculate geographic area in Google Sheets?

I have three columns of cells with geographic coordinates. For example:

``````40.614362,-73.974187 | 40.618654,-73.989636 | 40.629675,-73.979092
``````

I want to calculate an area between these coordinates to get similar results as I would get from http://www.daftlogic.com/projects-google-maps-area-calculator-tool.htm It seems like a regular "planar" formula for a triangle area as seen here https://math.stackexchange.com/a/516223/195545 does not produce accurate results.

Note that latitude and longitude coordinates are in the same cell. I figured out that I can use the following formula to extract latitude and longitude values:

`````` =REGEXEXTRACT(A1,"(.*?),")  # for latitude
=REGEXEXTRACT(A1,",(.*?)\$") # for longitude
``````

How can I reproduce area calculations in Google Sheets?

• Those coordinates look pretty close together, but note that the shoelace formula only applies for a plane and shouldn't be used if the points on Earth are far enough that the planet's curvature matters. Nov 26, 2014 at 6:25
• @ShaneDiDona, you are right. I rephrased the question to address this concern.
– Mxx
Nov 26, 2014 at 19:25

With a help from a friend, he was able to create the following JavaScript which was imported into Google Sheets' `Script Editor` and then referenced as `=PlanarPolygonAreaMeters2(A1,B1,C1)/1000000`

``````var radiansPerDegree = Math.PI / 180.0;
var degreesPerRadian = 180.0 / Math.PI;
var metersPerDegree = 2.0 * Math.PI * earthRadiusMeters / 360.0;
var metersPerKm = 1000.0;
var meters2PerHectare = 10000.0;
var feetPerMeter = 3.2808399;
var feetPerMile = 5280.0;
var acresPerMile2 = 640;

function PlanarPolygonAreaMeters2(p1, p2, p3) {
var points = [p1, p2, p3];
Utilities.sleep(1500);
var a = 0.0;
for (var i = 0; i < points.length; ++i) {
var j = (i + 1) % points.length;

var thisPointRaw = points[i];
var nextPointRaw = points[j];
var thisPointLat = thisPointRaw.split(/,/);
var thisPointLng = thisPointRaw.split(/,/);

var nextPointLat = nextPointRaw.split(/,/);
var nextPointLng = nextPointRaw.split(/,/);

var xi = thisPointLng * metersPerDegree * Math.cos(thisPointLat * radiansPerDegree);
var yi = thisPointLat * metersPerDegree;
var xj = nextPointLng * metersPerDegree * Math.cos(nextPointLat * radiansPerDegree);
var yj = nextPointLat * metersPerDegree;
a += xi * yj - xj * yi;
}
return Math.abs(a / 2.0);

}
``````

The raw output of this script is in `m²`.