Excel - Exploring Excel's Three New Functions: MAP, REDUCE, and SCAN - Episode 2416
Microsoft Excel Tutorial: Create a PRODUCTIF function in Excel using the new array helper functions MAP, REDUCE, and SCAN.
Welcome to the MrExcel Podcast, where we dive deep into all things Excel. In today's episode, we will be exploring three new functions that have been recently added to Excel: MAP, REDUCE, and SCAN. These functions are currently only available to 50% of users on Insiders Fast, so consider yourself lucky if you have access to them.
First up, we have the MAP function, which is used for value transformation. With MAP, we can pass one or more arrays and have it go through each item in the array, perform a calculation, and return an array of equal size. This is particularly useful for functions like ISEVEN, which don't play well with arrays. With MAP, we can easily overcome this limitation and get the desired result.
Next, we have the REDUCE function, which operates on each item in the array and returns a single value. Unlike MAP, which returns an array, REDUCE returns a single value. This function is particularly useful when we want to avoid using the OFFSET function, which can be a bit tricky to work with. However, one limitation of REDUCE is that it can only work with one array at a time, unlike MAP which can handle multiple arrays.
Last but not least, we have the SCAN function, which is similar to REDUCE but with one key difference. As SCAN is doing its thing, it returns each intermediate value, giving us a better understanding of how the calculation is being performed. This can be particularly useful when we want to see how the values are accumulating over time. Just like REDUCE, SCAN can only work with one array at a time.
I am currently working on the MrExcel 2022 version of my book, which will include these new helper functions. If you enjoyed this video, please consider giving it a thumbs up and subscribing to our channel. Don't forget to hit the bell icon to be notified of our latest videos. And as always, feel free to leave any questions or comments down below. Thank you for tuning in, and I'll see you in the next episode of the MrExcel Podcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents
(0:00) Walkthrough three new lambda helper functions
(0:29) MAP function in Excel
(0:50) Some old Excel functions don't work with arrays
(1:24) Using MAP to run a function on each cell
(2:10) Sum all values on an odd row using MAP function
(3:05) Passing multiple arrays to MAP function
(3:20) PRODUCTIF function using MAP function
(4:23) REDUCE function can only handle one incoming array
(5:45) REDUCE needs an initial value
(6:00) SCAN returns each intermediate result
(7:12) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Three more of the new Lambda Helper functions in Excel: MAP, REDUCE, and SCAN. In today's episode, see three different ways to create a PRODUCTIF function in Excel using MAP, REDUCE, or SCAN.
Important points:
MAP can take one or more arrays.
MAP is looking at each individual cell, not just the values in the cells.
REDUCE does the same thing, but produces a single result.
SCAN is like REDUCE, but makes the intermediate results available.
Read more at: https://techcommunity.microsoft.com/t...
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
This video answers these common search terms:
how to use scan function in excel
how to use the scan function in excel
how to use map function in excel
how to use the reduce function in excel
difference between map and scan function in excel
contrast the map and scan function in excel
when to use map function in excel
Watch video Excel - Exploring Excel's Three New Functions: MAP, REDUCE, and SCAN - Episode 2416 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 29 July 2021, don't forget to share it with your friends and acquaintances, it has been viewed on our site 8,111 once and liked it 199 people.