Excel 365 function examples

[georgiboy]

Hi all,

I will upload a file here where I will (try) to demonstrate the use of the newer functions available in Excel 365. I will update the file as I add more to it and post a message to this thread that I have done so.

The thread will be open for discussion/ suggestions/ corrections. Hopefully together we can create a file that will give a good starting point for learning these new functions.

If we can help even one person to learn then the time will not be wasted.

I have done it as an Excel file as if you are like me, you may not learn very well from videos. If you would like to know a good website for looking further into some of these functions then I propose the below link. It gives great examples visually, without using videos:
https://exceljet.net/functions

Feel free to post here with your thoughts.

SO far the attached file includes examples of the functions:

• LET
• More to come...

[Paul_Hossler]

I'll throw in a workbook that I put together to try and understand the new 365 functions

[georgiboy]

Thanks Paul,

Have you looked into the use of BYROW, BYCOL and MAP in combination with LAMBDA?

These functions often mean there is no need for loops in VBA.

I plan to go through these functions when I have the time to update the file.

[Aussiebear]

Here's a quick version georgiboy. Apologies for not formatting the data on sheet 2 as Currency but the User will understand.

Key Takeaways:

• LAMBDA: The core of these functions. It allows you to define custom, reusable functions directly within your spreadsheet formulas without needing to write VBA or App Script.
• BYROW: Processes data row by row.
• BYCOL: Processes data column by column.
• MAP: Processes data element by element, often useful when you need to combine or transform elements from multiple arrays.

These combinations are incredibly powerful for creating dynamic and flexible spreadsheets that automatically adapt to changes in your data.

[georgiboy]

Thanks Aussie,

Something worth noting with BYCOL & BYROW is that when using functions such as:
SUM, AVERAGE, MEDIAN, COUNT, COUNTA, MAX, MIN, PRODUCT, ARRAYTOTEXT, CONCAT etc..

You don't need to use LAMBDA.

For example:
=BYROW(A2:C4,LAMBDA(row,SUM(row)))

Could be written as:
=BYROW(A2:C4,SUM)

Same with:
=BYCOL(A2:C4, LAMBDA(col, AVERAGE(col)))

Could be:
=BYCOL(A2:C4,AVERAGE)

[Paul_Hossler]

@AB - those examples were very helpful. I'd been trying to see how BYROW, BYCOL, and MAP worked

@georgibuy - good point about using BYROW and BYCOL with the 'built in' functions. That would make it easier sometimes.

I have a hard time with LAMBDA and LET since 1) it's new, 2) I'm old, and 3) I haven't used it much

Capture.JPG

I still prefer not to have lots of parens in a formula, p45cal is very good at that, but I find it hard to bebug if I chain a lot into a single cell

Also, I don't understand where the TEXTJOIN in the Named Rage window comes from when iI start writting a formula. It pops up with the"=' abd goes away later. Doesn't hurt anything; just wierd

[Aussiebear]

ARRAYTOTEXT example

[Paul_Hossler]

This has been interesting. I learned a lot

I updated my new functions examples workbook with what I learned here if anyone is interested

[Aussiebear]

Thank you for that Paul. Our learning styles are somewhat different, but I will make an effort to go through your examples.

[Paul_Hossler]

I really just did it for my own purposes, to see what all the fuss was about

Wehn I come across interesting snippets of code, I put them into my Library.xlsm workbook

This sort of stuff didn't fit so I stuck it into a separate workbook so I wouldn't forget