LOL.

My office still even use 2007 version lol I can't stand on it so i decided to upgrade mine into 365 version 😂

Using 2013 here also, but finally in 2 weeks we are going to 365

😂

The same situation...😕

Same!

Same here my friend 😃

I've been googling SQL, python and databases. I'm guessing that's why I got suggested this video

@@smd7 Yeah, I can see the connection there. I wasn't looking at anything related to computing before this recommendation, other than games.

Because the author paid for it

Glad it helped!

Excel is a few programming languages, referred to as "micro languages". The one in the video is called Excel Formula Language. If you are into databases and you have PowerPivot add-in, you can also use DAX which is a mix of SQL and Excel Formula Language. Excel also has M Language, Excel Macro Language (for backward compatibility) and obviously VBA.

Just emulate macOS

@@ultrio325 what? Why would I do that?

Excel, and many other Microsoft office applications, work with Visual Basic for Applications (VBA) which for all intents and purposes is a programming language that can be used in excel to make a large variety of programs

Also MS Office SDKs allow programming Excel add-ins and XLL libraries. Tbh this new feature is cool but not as groundbreaking as the many existing formula and programming features in Excel

VBA is an atrocity as well, so any alternative is nice by default.

@@skipfred I am from a non programming background myself, but I enjoy VBA. Why do people hate it?

@@sohailahmed1351 My original comment was a bit of hyperbole, it's not *that* bad. But personally, I find it clunky, unintuitive, and frustrating to work with.

@@skipfred its also like 100 years old lmfao.... u frustrated with the scalability of an abacus aswell?

@@grimendancehall I don't still have to use an abacus. If I was required to use an abacus to get Excel to be reasonably useful then yeah, I would complain about that too.

Yeah no kidding, after the first minute I was like "this is nothing" to the next minute "this is the best thing ever"

...and then on the last formula my brain had a "recursion" and turned inside out - oh the pain (Dr.Smith)

Check out my video with more examples. kzfaq.info/get/bejne/hc1ph7yAvLG8pGg.html

You could as a simple example define a function that calculates the floating average of a range of values and reuse it over and over again. instead of typing the whole formula you could just say something like "=FLOATAVRG(A1:A100)". That is what programmers do all time: define function to do something and reuse it but instead of having to learn VBA you can do it in excel syntax. Or you could do date formatting, validating values, convert values. The possiblities are nearly endless.

This is because you’re not a programmer.

hi! im a chemical engineering student and we frequently use excel in all sorts of calculations using complex formulas, and especially in cases where we use equations derived from laws this function will come in very handy!

optimizations, dynamic programming, search algorithms, etc...

What’s VBA

@@ananasbanana Visual Basic, a programming language supported by excel. You can use it to solve problems which would be difficult or impossible to solve with normal functions.

@@ananasbanana I think it stands for Visual Basic Access. I remember using it a long time ago for L4G programming on a Access database but other Office products started using it also for macro and function programming.

@@lajya01 Is it separate to Excel? Have to pay extra for it?

@@ananasbanana It comes built in to most Office applications though in the newest versions (I think from 2016 onwards, someone correct me on this) it has to be enabled as it can be a security risk. It was designed to be used under license with other applications but as far as I am aware no other applications use it outside of the Office range.

Documentation and notation.

@@Capt_AwesomePants nah. it's been working in production for two years now... Good luck!

Actually, it will make their life easier compared to them trying to make sense of your arcane, uncommented VBA spaghetti

I got really excited that excel might support arrow syntax

A very cool new addition to Excel.

I'm trying to figure out in what report I'll use this and how.

@@AdilParray It does make it easy to shrink complex formulas.

@@SeleTraining Good point!

@@SeleTraining just the ability to abstract logic into a named function without vba is so powerful

In order to understand recursion, you first need to understand recursion.

@@ellisz5972 haha exactly

well you could name it that way with naming your own funktion and then callig it :D

Now that song is stuck in my head... For eternity!

The Lambada function is cool, but the Macarena function blows it away

🤣

You would have loved Public Function in VBA

Omg.. it's much more complicated. Just lose the fear of creating a function in VBA, where you have more space to write and you can make comments to organize the code. I do this for litteraly 20 years. And if the input parameters depends on another cell you can use "Application.Volatile" to force update.

Loving udfs without vba

thats called not knowing how to program. not trying to overly insult but thats the key. a million different ways to do something. obviously some are much better than others. thats why this field is so lucrative... not cuz its hard to write a for loop and add subdirectory to name and make a new file.

Just a query, how did the median value automatically pop up? Is it referring from some location?

underrated comment

well it was done by VBA (you could write your own functions including data type operations), however, VBA has its issues (many being security) so by default, it is blocked unless the user activates it.

@Winston McGee Eeh no... Haskell came out in 1990. Python integrated lambda-functions 25 years ago. C++, C#, Javascript and many more have been using them for years.

@Winston McGee If we talk about existence, Lisp used them 60 years ago. I was only talking in a modern context.

So nice of you

Excellent!

Nice! I thought recursion was sought only by javascript Google sheets programers

agreed, but it allows you to share the file with the office without macros. But yeah... VBA all the way.

LOL

Actually it's more like the double parentheses are: first to build the Lambda expression, then the next set is to execute the expression. Lisp/Scheme might look like this: (funcall (lambda (x y) (* x y)) 6 8 ) Compared to the XL expression: =Lambda(x, y)(6, 8)

Thanks

Agreed. It took way too long

Do you hate microwaves ?

@@BHARGAV_GAJJAR what you think?

True!

I did realise this while learning the concept, but it's nice to get confirmation as it's a good feeling to know I derived it's implications without a use case Learning this stuff is fun

I guess this whole thing feels like a trailer hitch on a Prius, asking Excel to do a job meant for VBA. If the formula is simple, hiding it behind a custom function name only makes it more difficult to read without necessarily being more useful. If the formula is complex, I'm not sure why VBA wouldn't still be the better platform, being easier to work with and granting much more control to the developer, especially when we factor in VBA add-ins that can carry custom functions between all of your workbooks. And especially considering the atrocity that is editing formulas in the "Refers to" boxes. I'm not debugging a lambda with 12 nested functions in that thing, accidentally hitting the arrow key and having it insert gibberish in the middle.

@flo xbr perhaps you could create an ‘advanced’ series of presentations covering more in-depth examples. Please reply when you’ve posted your first session so we can check it out.

@@CLove511 For basically that reason. You can do a lot more with VBA, including things which you wouldn't necessarily trust coming from some random. I am assuming the lambda functions are meant to be "safe", so wont need saving as an excel macro enabled workbook.

I took 3 terms of Calculus. Can't remember anything now...

haahah lol😅 but Haskell and Lambda Calculus still are so beautiful i think. i love em

Glad it was helpful!

Install uBlock origin already

Thank you! 😃

Only 30? Lisp is older than that …

Same here. I’ve been using Excel since ‘97 and MS Works and Lotus 1-2-3 before that. Videos like this put me to shame.

Take a look at my LAMBDA more examples video. You can see some more complex uses.

Google what's lambda in any programming language.

Thanks Wayne

Intentionally so I believe

lol no

Thanks Tyler.

Yes. I generally try to get these new function training videos out early so people know what is coming in the future. My tips and tricks videos reference the released functions.

@@SeleTraining Please do not take this as criticism of you. I'm more irritated at Microsoft. They made LET sound like it was available, and I was all excited. Only to find it was only available for insiders, and I had to wait 3 months or so. I've the same irritation about LAMBDA. Have a great day!

@@jtmh31 If you have a Microsoft 365 Family or Personal subscription, it's easy to become an insider. See insider.office.com/en-us/join/windows

do you know the timetable for us normies to get access to lambda?

@@maxheithmar334 Microsoft never says when they will release it. It is in beta but it could be days or months.

excel (and, somewhat surprisingly, powerpoint) are turing-complete, so in principle, they can compute anything that is computable

Yes you can, through index with multiple match

Yes you can with Xlookup.

If you have a complex formula used throughout your spreadsheet it could save a bunch of time. Also, consider if you had to update the function you could then do it in one place rather than in every cell it is used.

It allows you to perform a transformation and then use that value as a variable without needing to recalculate that value for each time you want to use it.

@@SeleTraining This looks like the key detail. You put "=Property_Tax_Formula(A2,B2,C2)" in the D2 cell, and in the Name Manager screen you assign "Property_Tax_Formula" to a significant equation that you don't want to type out in each cell. This avoids the potential problem where the equation is changed and you might copy the new equation to almost all the locations where it is used but miss a few. By using the Name Manager to change the equation, you ensure that everywhere using 'Property_Tax_Formula' will be using the new equation. For example, you might have a property tax calculation that takes in total land area, waterfront property, and zoning code to determine the base tax value for a piece of land. One year the variables in the formula to determine the tax are changed. If you just copy and pasted the formula, you might make a mistake. But by changing the formula in the Name Manager screen you ensure that all cells using that formula will use the updated formula. (If I am understanding it correctly)

@@toddkes5890 Yes. You can also save a set of LAMBDA functions with a spreadsheet and then copy the spreadsheet whenever you need to use them.

Thanks!

Bummer

That's really old 😕

u mean 2007. 2008 dnt exist

Thank you! 😁