LAMBDA Examples: Distance between two cities

Microsoft

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 ExtractionProperty 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 cosineslaw 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!

 

ASTHECROWFLIESASTHECROWFLIES

 

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

34 Replies

This is awesome! I'm loving it! LAMBDA functions will be a game changer for the Excel heavy users!

@Chris_Gross  Measuring distances amongst various locations/places on the Earth with the help of MS Office Excel formulas is really fascinating and awesomely great. 

 

LAMBDA:thumbs_up:=):bottle_with_popping_cork::popcorn::party_popper::collision:
A nice thing, the world has become a little bit smaller for everyone
... with just three entries in Excel :)).

Nikolino
I know that I don't know (Socrates)

One thing with =LET function I dont undestand

On this example You use parameter as city datatype and col param.latitude

when I use folowing example

=LET(t1, SomeTable, XLOOKUP(7, t1[keycolumn], t1[valuecolumn]) - dont work

so .-operator works with variables but [] indexer don't work

I quess sometime might be usefull to have 

=LAMBDA(SomeTable, Key, XLOOKUP(Key, SomeTable[KeyColumn], SomeTable[ValueColumn])) as named funktion 

 

@HennSarv 

Reference on SomeTable returns an array, actually SomeTable[#Data], not the table. Thus t1 is array, not table.

@Chris_Gross 

 

CellColor

=LAMBDA(Rng, GET.CELL(63,Rng))

Crashes excel

Any way to get this to work

 

Cheers

Sam

@Chris_Gross 

Are there plans to support names defined using LAMBDA functions to be distributed via Add-ins or are we back to PERSONAL.xlsb Cheers Sam

 

@Sameer_Bhide 

IMHO, lambda management shall be the same for desktop, online and mobile versions.

This is awesome & I can't wait to get my hands on it 

@Sameer_Bhide 

Given a named formula MyFunc in Book1, we should be able to call it from another workbook (as with vba functions):

 

=Book1!MyFunc(123)

 

If MyFunc =LAMBDA(x,x) this returns an error on my build but I assume it will work soon. Instead we could test with MyFunc = MyVBAFunc where

 

Function MyVBAFunc(x): MyVBAFunc = x: End Function

 

To access MyFunc as an add-in function without the Book1! prefix we would need to press Ctrl+F11 to open a macro sheet and change the definition in the define name dialog to a function. Or equivalently from vba:

 

Names("MyFunc").MacroType=xlFunction

 

For a simple test define the name MyFunc:=123 as a function and set the workbook IsAddin property to True. This name is then accessible from any workbook.

All the customs formula made with LAMBDA to be a way to stored/export online & those can be globally accessible to any workbook under that M365 subscription account.

 

It will be always safe and no loss of function in case of system crash.

 

Regards, Faraz Shaikh

@Faraz Shaikh 

Agreed, ideally we could store a library of these functions accessible from any workbook. Function scope should be global like XLL / .js custom functions and not linked to a particular workbook as with VBA udfs.

Hello will you be able to assist me with navigating excel?

@lori_m 

Wow ! - How did you figure that out - I am going to give this a shot on my install and see how it goes.

 

Cheers

Sam

I can't use my Excel application. When I ask it to open, the warning appears that it cannot open XLS files. Or the changes I made to the file I saved don't appear the next time I open the same file. there is no problem with word, but I definitely can't work with XLS workshop. can you help?

@sadece_Bilal -are You tried to change file format

 

OPen file - ignore warning

Press F12 (save as command)

Select new file type - Excel Workbook (xlsx) rather than Excel 2003 wrokbook (xls)

HennSarv_0-1607605797304.png

then close and reopen file (the last step is for safety)

 

CHeck - can Yu now work with?

@Sameer_Bhide No. XLM functions cannot be used on the worksheet.

@Sameer_Bhide You can save all of your LAMBDA formulas in a workbook and then move/copy a blank worksheet from that workbook to another workbook where you want to use them.