A Use Case of Excel's LAMBDA Function in SpreadsheetWeb: Calculating Distance by Latitude and Longitude

SpreadsheetWeb Hub supports Excel's LAMBDA function. Recently released LAMBDA function allows Excel users to build custom, reusable functions without VBA. There are so many use cases of LAMBDA function. In this article, we will build a function to calculate distance between two points represented by their latitudes and longitudes. Then we will build a SpreadsheetWeb application using this function for distance calculation.

Latitude/longitude calculations are always tricky thanks to the ellipsoidal shape of the earth. Although, you accept the earth as perfect sphere like most calculation approaches, the curvy structure works with degrees, minutes, and seconds while distance is measured in decimals. This conversion increases the length of your formula immensely and makes it harder to reuse and manage.

Formula of calculating distance by longitude and latitude

In our example, we are using the great-circle distance method which uses the shortest path, also known as, as the crow flies, between two points corresponding to an arc linking two points on a sphere. The method uses Spherical law of cosines a theorem relating the sides and angles of spherical triangles.

where

• φ: Latitude (radian)
• λ: Longitude (radian)
• R: Radius of the earth

The coordinates are represented in two forms:

• As a string including degree, minute and second
• As a numerical value of decimal degrees

Converting degrees, minutes and seconds to decimal degrees:

Decimal Degrees = Degrees + (Minutes + Seconds / 60) / 60

Converting decimal degrees to radians:

Radians = Decimal Degrees * PI / 180

Because deg-min-sec notation will make things more confusing, we will use decimal degrees going forward. This is the Excel formula for decimal degrees:

Obviously, no one wants to deal with this kind of formula more than one time. Instead, you can use the LAMBDA function to define this formula once in a named range and use the named range like a UDF (user-defined function). Let's see what the LAMBDA function is and how you can use it.

LAMBDA Function

The LAMBDA function is game changer feature that allows you create your own functions without any VBA, macro, or JavaScript knowledge. Since it's unique nature, the function has a unique use case.

• A LAMBDA formula should be written in a named range. That named range's name will be your custom function's name.
• You need to type parameter names before the function itself. Such as, a and b are the parameters and the latter operation is the function itself: =LAMBDA(a, b, a-b)
• LAMBDA functions can be called recursively.

For detailed information, please visit the following page: Excel LAMBDA Function

Calculating distance by longitude and latitude with LAMBDA Function

Let's create our custom function as described above. The distance formula needs 4 arguments, if we use a static value for the radius of earth, e.g., 6,371 km.

Once created, you can re-use your custom function anywhere in this workbook. Excel even displays IntelliSense box for this function.

Publishing your model as a web application

With SpreadsheetWeb supports for LAMBDA function, you can turn your Excel file into a professional looking, scalable online calculator without any coding. You can also enable automation by collecting contact information from your visitors to send them detailed results.

For example, why not display the coordinates on a map along with results? You can embed a map into your SpreadsheetWeb application. In this article, we will not go over how this Excel file can be converted into a web application using SpreadsheetWeb. Those who are interested can visit our help pages and video tutorials to learn more about this.

You can sign up for a free SpreadsheetWEB Hub account and start building your app. Here is a live preview of this distance calculator application.