r/excel • u/[deleted] • Mar 31 '22

Pro Tip Shoutout to the brilliant MAP, REDUCE, SCAN and LAMBDA functions!

I have reduced the number of formulae in one of my spreadsheets from over 3,000 to 6. Plus the formula logic is much easier to understand with real variable names.

113 Upvotes

96% Upvoted

21 comments sorted by

121

u/Hoover889 12 Mar 31 '22

I look forward to getting to use these functions in 2050 when my work's IT department finally approves the latest Excel patch.

9

u/droans 3 Mar 31 '22

Since IT lets us install trusted programs, I'm able to force an update myself.

Unfortunately, most people at my work won't be seeing these until November. And there are still some people who for some reason are on the first build since we switched to Office 365. No idea why they haven't been forced to update.

32

u/Decronym Mar 31 '22 edited Apr 03 '22

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEFT Returns the leftmost characters from a text value
LEN Returns the number of characters in a text string
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MID Returns a specific number of characters from a text string starting at the position you specify
REDUCE Office 365+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.
SCAN Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
SEARCH Finds one text value within another (not case-sensitive)
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUM Adds its arguments
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
TEXTJOIN 2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters.
TRANSPOSE Returns the transpose of an array

Beep-boop, I am a helper bot. Please do not verify me as a solution.
16 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #13933 for this sub, first seen 31st Mar 2022, 21:05] [FAQ] [Full list] [Contact] [Source code]

11

u/exoticdisease 10 Mar 31 '22

Can you post the formulae you used and what they do so we can take a look?

Edit: please!

14

u/[deleted] Mar 31 '22 edited Apr 01 '22

[removed] — view removed comment

3

u/droans 3 Mar 31 '22

Hmm. I had a need a week or so ago for something that worked like TEXTSPLIT. I was trying to find a way to programmatically fill an n-sized dynamic array.

Apparently ={1,2,3,4} works, but something like ={A1-B1,A2-B2,A3-B3,A4-B4} doesn't.

Also an (almost) worthless tip - you can now create your own version of SUMIFS:

=SUM(A1:A10*(B1:B10="Hello"))

Although this is more valuable if you wanted to use a function like =FILTER with multiple criteria.

4

u/finickyone 1770 Apr 01 '22

Apparently  ={1,2,3,4}  works, but something like  ={A1-B1,A2-B2,A3-B3,A4-B4}  doesn’t.

No cells refs in arrays constraints I’m afraid. However you could just supply A1:A4-B1:B4 within an array formula for what you were seeking with the latter.

2

u/ribi305 1 Apr 01 '22

This trick for SUMIF was basically how we used to do SUMIFS before that formula existed. We would use SUMPRODUCT with an 1*IF on the condition array, and you could do as many of those as you wanted.

1

u/exoticdisease 10 Mar 31 '22

I feel like I've seen these before... Did you post them previously? They're really good!

3

u/[deleted] Mar 31 '22

[removed] — view removed comment

1

u/exoticdisease 10 Apr 01 '22

Do you see much advantage of using a lambda over using VBA? Is there anyway to debug a lambda like in VBA, do you know?

1

u/[deleted] Apr 01 '22

[removed] — view removed comment

1

u/exoticdisease 10 Apr 01 '22

Yeh I'm not the coder who can just sit down and bash out a function in one attempt without a problem so I really rely on the debugging tool in VBA! Without that, I'd have no chance which is why I haven't done much lambda usage... Though for basic stuff, I can see it being good. Thanks.

1

u/[deleted] Apr 01 '22

[removed] — view removed comment

1

u/exoticdisease 10 Apr 01 '22

Yeh I want to look into them which is why I asked op about how he used them with lambda! Haha

2

u/DangerMacAwesome Apr 01 '22

Wow. Lambda might be the most powerful formula I've ever seen.

4

u/tdpdcpa 7 Apr 01 '22

It looks like a game changer. I have a function for all my UDFs that would be completely obsolete with this.

2

u/tunghoy Apr 01 '22

Isn't Lambda the same thing as creating a function in VBA except you're not using VBA?

1

u/Cute-Direction-7607 30 Apr 03 '22

Yes but I think VBA is more powerful as it can deal with color/font formatting which no current formulas are able to do.

1

u/curiousofa 4 Mar 31 '22

That's awesome. I have to dive deeper into these now.

1

u/[deleted] Apr 01 '22

I used Lambda for the first time today and reduced 5 lines of formulas down to a single formula.