LAMBDA Examples: Distance between two cities

[Chris_Gross]

This post is the first of a series where we will be sharing out examples of lambdas. This is intended to highlight lambdas we have cooked up that show the power of this new super-charged function. Additionally, you have the opportunity to engage with us on the lambdas you have built yourself and of course let us know how our own formulas could be improved. 

If you didn’t catch the announcement, be sure to check out the blog post highlighting the release of this new function, Announcing LAMBDA: Turn Excel formulas into custom functions

In today’s example we will be picking up where we left off in the announcement blog and making good on the promise of:

“a custom function that takes two cities as input and calculates the distance between them...”

So let’s get to it!

Distance between two cities

The first thing to note is that this function will be making use of Excel Data Types. Data Types are useful here because of the ease at which we can retrieve latitude and longitude for a given entity through “dot notation”.

Property Extraction

The next thing to cover is the mathematical formula which we will be encoding as an excel formula. We will be making use of the law of cosines which can give you an as the crow flies distance calculation.

This formula is a great one to encode as a lambda given its complexity which will make it more prone to formula-authoring errors. It also happens to re-use multiple inputs which makes it a great candidate for LET. In short, this is the type of function I would want to author once and store for re-use.

The equation looks like this:

law of cosines

While this might seem a bit daunting, the inputs we really care about are:

1. Latitude and longitude of the start location
2. Latitude and longitude of the end location

In the equation

• Δλ is the delta of the longitudes (_lon2 - _lon1)
• φ1 and φ2 represent _lon1 and _lon2 respectively
• r is equal to the radius of the sphere, in this case 6378 which is the radius of the earth in kilometers.

The last trick we employ in this example is to convert everything into radians which is where we make use of the LET function to do these transformations. This is important as the equation expects lat/long in radians and the Data Types return them in degrees.

This is illustrated in the first series of name definitions where we:

1. Calculate the values in terms of radians for re-use
2. Assign new names to differentiate between the input values (_lat1, _lon1, _lat2, _lon2) and transformed values (lat_1, lat_2, lon_1, lon_2)

Putting all those concepts together gives us the following solution for LATLONGDISTANCE which will be doing the heavy-lifting for the final formula that takes in cities and passes in their respective latitudes and longitudes to LATLONGDISTANCE.

=LATLONGDISTANCE

=LAMBDA(_lat1, _lon1, _lat2, _lon2, LET(
     lat_1, RADIANS(_lat1),
     lon_1, RADIANS(_lon1),
     lat_2, RADIANS(_lat2),
     lon_2, RADIANS(_lon2),
     r, 6378,
     ACOS(
      (SIN(lat_1) * SIN(lat_2)) + (COS(lat_1) * COS(lat_2) * COS(lon_2-lon_1))
     ) * r
))

 

=ASTHECROWFLIES

The last piece of the puzzle is to create a lambda which will take two cities as inputs. You could encode all of this into the previous formula, but we like the composability of lambdas and figured it would be a great way to show lambdas calling one another.

Revisiting the previous comment, about extracting properties from data types, we will need to define something which takes two cities as inputs and then extracts the latitudes and longitudes.

For this we will make use of dot notation and LET.

=LAMBDA(city1, city2, LET(
     lat_1, city1.Latitude,
     lon_1, city1.Longitude,
     lat_2, city2.Latitude,
     lon_2, city2.Longitude,
))

With the values extracted and names defined, the last thing to do is define a calculation which we call distance that returns the value. You’ll notice we wrap this in an IFERROR to catch any errors which might result from passing in bad values.

=LAMBDA(city1, city2, LET(
     lat_1, city1.Latitude,
     lon_1, city1.Longitude,
     lat_2, city2.Latitude,
     lon_2, city2.Longitude,
     distance, LATLONGDISTANCE(lat_1, lon_1, lat_2, lon_2),
     IFERROR(distance, "an error occurred")
))

And that's it!

ASTHECROWFLIES

We hope you found this example useful and look forward to seeing what lambdas you have cooked up on your own.

Until next time!

Chris Gross,

Program Manager Excel

[#2]

@David Hager 

Copying a sheet is a neat trick. XLM functions can't be input in the worksheet but should still be ok within names, this is still in beta however and it looks like a number of things are not quite how they should be yet.

@Sameer_Bhide 
That method comes from having spent too much time writing Excel4 macros even before VBA was mainstream! XLM language is long gone, as you say, but the same environment is now playing a key role as the old macro functions were defined via names in much the same way the new LAMBDA function is. Another little known feature of Excel is that every session has a hidden macro workbook which is where XLL function names are stored. In fact it's possible to copy workbook names to this hidden area as follows...

Enter the formula =MyFunc in A1, defined as before, and from the VBA immediate window execute the following command (in R1C1 notation)

ExecuteExcel4Macro "COPY(!C1,TEXTREF(""C1""))"

MyFunc is now defined like an XLL function that can be accessed from any open workbook and is available even when the original workbook is closed.

[#3]

Pmz

[#4]

I need help with getting a 2021 Calendar Excel sheet so I can Edit

[#5]

@kfriedelwmcom 

What's the link between Lambda and Calendar template? You may File->New and "calendar" in search, there are lot of templates.

[#6]

@Chris_Gross 

I have created a Lambda function to generate a Fibonacci series. It is detailed in this link.

Lambda Example: Generate Fibonacci series - Microsoft Tech Community

Would love to know if there is any way we could improve the algorithm.

[#7]

@Viz  I tried Fibonacci series too and came up with two options:

1. Recursive

FIB:
=LAMBDA(n,
      IF(n<2,
      SEQUENCE(n+1,,0),
      LET(b,FIB(n-1),
           IF(SEQUENCE(n)<n,b,INDEX(b,n-1)+INDEX(b,n-2))
      )))

2. Non-recursive (Binet)

=LAMBDA(n,ROUND(((1+5^0.5)/2)^SEQUENCE(n,,0)/5^0.5,))

[#8]

@Chris_Gross I am very interested in the interaction between the new Data Types and LAMBDAs. In your example, the ASTHECROWFLIES() function works with the City Data Type, which provides the Latitude and Longitude fields. But (I presume) it would work just as well with any Data Type that exposed these two numerical fields with the same names.

Thus I might define a Data Type of Mountain, which does not perhaps have much in common with City, but if it exposed the "Coordinates" interface of Latitude and Longitude methods could still be used with AsTheCrowFlies. Maybe the Name could be qualified as "Coordinates.AsTheCrowFlies"?

As I come from a C++ background, this is starting to suggest generic, template-based programming, but perhaps I am getting a bit carried away.

As an aside, is there any way of checking the type in LAMBDAs (or other formulas)?

[#9]

@DS_London 

I see no difference between built-in Geography data type or custom one like Mountain.

By checking the type you mean to check if the value in cell is Data Type or not? In general that's =TYPE(A1) returns 128 for the compound data.

[#10]

@Sergei Baklan I don't quite get your point? Certainly you can class a mountain as Geography (though the built-in type doesn't seem to recognize "Mount Everest" on my Excel at least), but a mountain is certainly distinct from a city in many respects.

For me, at least, the power of Data Types is the ability to create my own, and use them to encapsulate data and pass them around (including into LAMBDA functions which understand their fields). In that sense they have some OOP capabilities when put together with LAMBDA.

I don't want to sidetrack this LAMBDA thread into a Data Type discussion, but I was wondering how you could tell from a cell what Data Type it contained (beyond it being a compound type)? 

[#11]

@DS_London 

I mean you may create your own data type with Power Query

Another option is using featured table in Power BI adding it to Organization data type.

[#12]

@Sergei Baklan Yes, this is what I have been doing. It was just that my sheet is a little more involved and I was trying to bring out a more general comment, using the original example. I wanted to show my appreciation of how LAMBDA and Data Types fit so well together. The creation of Data Types paired with specific LAMBDAs to make use of them is very interesting.

[#13]

@Chris_Gross 

Detail :

• φ1 and φ2 represent _lon1 and _lon2 respectively
• hum... 
• φ1 and φ2 represent _lat1 and _lat2 respectively

Thanks

[#14]

@Chris_Gross @Sameer_Bhide 

I had forgotten about this post but was led back here from @Viz 's Fibonacci link above. Revisiting the methods described in previous comments I was pleased to find that it's now possible to define lambdas as addin functions that can be called from any open workbook.

Steps:
1. define lambdas in a new workbook
2. insert a macro sheet (ctrl+F11)
3. with the macro sheet selected, open name manager
4. edit name definitions clicking the Function option.
5. remove the macro sheet and save as addin (xlam).
6. open the addin and enter a test formula in a new workbook

Lambda definitions:
TIMEIT
=LET(t, timer(), LAMBDA(formula, timer() - t));
timer
=LAMBDA(ROUND(NOW()*24000*3600,0))

Test Formula:
=TIMEIT(SUM(SEQUENCE(1000,1000))) -> ¬60 milliseconds.

[#15]

This is great !!! Thanks a lot for this @lori_m - There is no autocomplete/parameter help - but we can live with that.
Cheers
Sam

[#16]

@Chris_Gross 

How can I make this formula recursive?

All this information is great!