It looks like a big leap from Excel to Trees, and in some ways it is.

Excel the leading Spreadsheet Office App, loved and hated by everyone possibly equally, but here to stay, a cornerstone of the business community.

Digital Twin – the idea sweeping the world, we have the technology now to create a true ‘metaverse’ inside a computer. A world that looks just like our world, and Trees must be one of the most important parts of that world. So the release of a library such as the one Quixel has just launched must be a landmark in that journey.

Trees – Personally, I don’t want to build a virtual world without trees, if we are going to build a truly immersive metaverse or digital twin we need trees and Quixel has just done a massive thing for the display of trees in photorealistic 3/4/5D.

Looking Forward

Looking forwards to 2022 as they roll out the trees one species at a time.

It turns out from their release video, that they had doubts about whether it was possible to do trees the necessary justice. It was a brave thing to do and I think they’ve done a great job.

When I started researching how we could best link Excel to a top end Graphics package I had no real idea where we would end up, or how fascinating a journey it would be! First you have to choose your graphics engine, and really there are 2 to choose from namely Unreal Engine and Unity. There are plenty of articles comparing the 2 so I won’t go into detail here suffice to say we chose Unreal Engine and have never looked back.

I think Unreal Engine or UE for short is simply the most remarkable piece of software I have ever seen. Many years ago I collected and worked with georeferenced 3d imagery for the Environment Agency and I worked with computer vision and AI during my Masters thesis but CAD was Solidworks I think and later I saw 3DstudioMax which was amazing for the time.

20 years later and we have an outstanding opensource CAD system given to us for free by Blender.org and Unreal Engine and amazing computer vision libraries such as opencv.org I mean really things have moved at an astonishing rate.

4 days ago we saw Quixel release their new “Megascans Trees – European Black Alder (early Access)” and this is why it is such a big deal.

When they released the asset, I must have been near the front of the queue, certainly I downloaded it immediately. This was a long awaited event and many in the 3d scene must have been holding their breath like me. Download, add to my Landscape River project and swap one of my trees for one of the new Megascan trees and WOW! Five minutes and the scene has been totally taken up a level in reality.

Trees are so important, and I’m so glad that Quixel have gone the whole way and delivered a truly inspiring asset library for Unreal Engine. Personally, I don’t want to build scenes without trees. Watching the release video where the team discussed the project, I was inspired by their use of the word ‘hormone.’

The role of hormones in life is huge, no less so with their effects on trees. Trees are effected by their hormones, changing colour of leaves and the way they grow. So that is the exact level we need to take things, if we are going to build a truly immersive metaverse or digital twin.

The models themselves are lovely, I think just as importantly though, they’ve also given us the master materials and all the Blueprint code behind them. So there is now a standard library for rendering foliage in UE. Certainly I shall be merging my code into theirs and not necessarily the other way around.

Spreadsheets are a critical tool for businesses to store, organise and analyze data. In the past, it has been difficult even for advanced Excel users to create dynamic spreadsheets that can do things like calculating conditional formatting based on changing values in other cells.

The new Excel LAMBDA function marks a significant change because you can now use formulas as if they were functions! This blog post covers everything you need to know about the new LAMBDA function and how it works so you can become a spreadsheet ninja!

What Is The LAMBDA Excel Function?

The LAMBDA excel function is a way to do simple programming in Excel. Microsoft has made this new Excel function available with the Office 365 subscription plan from April 2016.

In traditional programming, multiple inputs can affect your output values so it’s difficult to get unexpected results from your computer programs simply because you can test every possible combination of inputs and the conditions that those inputs affect.

The LAMBDA excel function is a way to do simple programming in Excel so you can use if-then statements as formulas instead of just values!

So why does this matter? Well, now your spreadsheet calculations can be dynamic and change automatically based on changing data or other events without making manual adjustments.

What Does The LAMBDA Excel Function Do?

Lambda is a function that’s built into Excel to help shorten your formula syntax. Here are some examples of what lambda can do:

– Count cells based on specified conditions with COUNTIFS, SUMPRODUCT & COUNTA functions in excel

– Use IF and AND statements as formulas instead of just values so you don’t have to write out the long syntax

– Use OR statements as formulas instead of just values so you don’t have to write out the long syntax

How To Use The LAMBDA Excel Function?

To use the LAMBDA function in Excel you need to have a Microsoft Office 365 subscription and the latest version of Microsoft Office installed. You also need the latest Microsoft updates installed (including Beta updates). Currently, you also need to join Microsoft Office Insiders in order to receive Beta updates.

The most basic use of the new LAMBDA excel function is to add if-then statements in your spreadsheets that only show up when a condition you’ve defined occurs, like below:

If [A] = [value], then show this value or else show this other value.

The above is written in Excel exactly how you would write it out in plain English. The “if-then” statement tells excel when to show the first value and when to show the second one so if cell A has a certain value, then that label will be shown instead of another. You can use any formula in the “if-then” statement, not just values.

The next step after writing your if-then statement is to define what values will trigger it! You can do this by using either of these methods:

Use an =IF() formula in a different cell and connect that cell reference to another cell with the LAMBDA excel function.

Use a Data Validation formula in another cell and connect that to your LAMBDA excel function so the input will come from what you select with the drop-down menu next time you open up your spreadsheet.

This is much easier than it sounds!

LAMBDA Excel Function Example

The next steps will show you an example of how to use the LAMBDA excel function with a real-life spreadsheet.

Let’s start with the Syntax:

=LAMBDA([parameter1, parameter2, …,] calculation)

The parameter argument defines the values that you want to pass to the calculation. This could be a cell reference, a text string, or a numeric value.

You can define up to 253 parameters, but this limit is unlikely to be reached!

The parameter argument is optional. Parameter names are defined as a text string e.g. x, number, myPar, etc.

The calculation argument defines the calculation that will be performed using the defined parameters. It must return a result, and it is not optional

=LAMBDA(x,x*10)(1)

This will return the result of 10 in the cell. A single parameter (x) is defined and the calculation argument multiplies that parameter by 10. The value of (1) after the first part of the function defines the value for the x parameter. This is only required if you are using the LAMBDA function directly into a cell instead of using it to create a re-usable function.

You can use standard Excel functions within the calculation argument.

=LAMBDA(x, y, SQRT((x^2+y^2)))

This LAMBDA function will use Pythagoras to calculate the hypotenuse of a right-angled triangle. The parameters x and y represent the horizontal and vertical sides of the triangle. The calculation argument squares both the parameters and adds them together and then uses the Excel SQRT function to get the value of the hypotenuse.

Creating Re-usable Function with LAMBDA Function In Excel

Re-usable LAMBDA functions are incredibly useful and can be used just like the Excel functions that exist in your everyday workbooks.

Get started by defining a new name then click on Formulas in the top Excel ribbon. This will give you the option to Define Name in the Defined Names group of the ribbon.

Give your new name a descriptive name, then click on the Scope button to define where you want this function to be available.

A workbook can contain up to 253 defined names (or functions). Each of these is either global or local in scope so by clicking one of these two buttons it will tell excel which setting you have chosen.

The final step is to define the parameters which you will need for your new function. These are simply text strings that can be used within custom functions just like standard excel formulas, so these could look something like x or number.

You now have a brand-new, re-usable Excel function! Be sure to save it with an appropriate name so that you can find it again later if needed!

You could then use this function in your spreadsheet by clicking on the cell where you want to see the results of your custom function, and entering =myFunction(parameter) into the formula bar. This will be displayed with a drop-down menu when using Data Validation so users can choose exactly which parameter they want to use as input for your custom function.

The LAMBDA Excel Function – A New Dawn In Excel

The LAMBDA function is a brand-new Excel tool that allows you to create flexible and reusable functions for your workbooks. From basic arithmetic, through Pythagoras’ Theorem, to complex conditional equations; the LAMBDA excel function opens up a whole new world of possibilities in Microsoft Office!

If you need help using the LAMBDA Excel function please get in touch with a member of our team. Our Excel Experts have worked with a range of businesses to transform their operations using automation, LAMBDA and more complex Excel features and functions.