
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 12, 2021 09:04 AM
Hello community, I’m trying to average geographic coordinates.
First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039
- In Google Sheets I’m using average() with correct result
- In Airtable the rollup with average() fails with NaN
Does anyone know another way of averaging coordinates?
PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 13, 2021 03:12 AM
Solution and final setup:
- Coordinates / text field: 40.096975653171484, 19.753827595322253
- Lat / number field: IF(Coordinates,VALUE(TRIM(LEFT(Coordinates,FIND(",",Coordinates)-1))))
- Lon / number field: IF(Coordinates,VALUE(TRIM(RIGHT(Coordinates,LEN(Coordinates)-FIND(",",Coordinates)))))
This enable the rollup with average(values), I’m using it with the Mapbox API to plot POIs on maps. The average of the POIs is needed to center the map.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 12, 2021 09:32 AM
Is Lat a number field?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 12, 2021 09:49 AM
Good point, the original coordinates is a text field as otherwise it couldn’t store the 41.3310413,19.7828039 value. I believe because of this the consecutive formula split lat/lon fields can’t be recognized as numbers. Perhaps it’s also the dot, I tried to get around this with the substitute function but failed.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 12, 2021 09:59 AM
Yeah, just make sure both your Lat and Long fields are number fields (or a formula field resulting in a number), and then you should be fine.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 13, 2021 03:12 AM
Solution and final setup:
- Coordinates / text field: 40.096975653171484, 19.753827595322253
- Lat / number field: IF(Coordinates,VALUE(TRIM(LEFT(Coordinates,FIND(",",Coordinates)-1))))
- Lon / number field: IF(Coordinates,VALUE(TRIM(RIGHT(Coordinates,LEN(Coordinates)-FIND(",",Coordinates)))))
This enable the rollup with average(values), I’m using it with the Mapbox API to plot POIs on maps. The average of the POIs is needed to center the map.
