This is more complex than anything I am currently using excel for but I love these videos. When I do need to get back into Excel I know I can come right back to these videos and refresh my mind.
For some calculations, the LAMBDA approach offers some real benefits In this instance, I would prefer the YTD to be calculated in the original sales table: Add a new calculated column (YTD) in the Sales table =IF(MONTH([@Month])=1,[@Sales],[@Sales]+INDEX([YTD],ROW([@Sales])-ROW(Sales[[#Headers],[Sales]])-1,1)) Essentially it says, if month is Jan, use sales, otherwise add sales to prior YTD total Alternatively =SUMPRODUCT((YEAR(INDEX([Month],1,1):[@Month])=YEAR([@Month]))*INDEX([Sales],1,1):[@Sales]) Note, INDEX([Month],1,1):[@Month] is the range from the first row to the current row
This was extremely helpful. Thanks to scan plus lambda I was able to calculate values adding up per month and finally finding the month with the highest value without pivot and fully dynamic.
Excellent explanation of SCAN. I am an old-school LISP programmer. I am so glad that finally functional programming has arrived in Excel. Thus I prefer single-cell formulas. Thanks to your video I can add SCAN to my kit of powerful tools.
Leila, thanks for all the great videos. Just to answer your question on alternative ways to calculate YTD balance, I would have used the following formula =IF(MONTH([@Month])=1,0,OFFSET([@YTD],-1,0))+[@Sales] Or, the rows are not sorted, then will use Sumifs =SUMIFS([Sales],[Month],"=" & DATE(YEAR([@Month]),1,1))
I needed a way to reference another cell not via a fixed reference but a way of calculating whichever other cell I needed. Google did not find the OFFSET function for me, and it's exactly what i was looking for, so thank you!
Thank you. This will be useful, once released, for a 26 year monthly Excel I maintain to have running FYTD. Actually even more used for FYTD since it is everywhere.
Leila, this is a monumentally powerful function! I'm trying to get it to determine streaks in a list, like winning streaks for sports teams or stock markets.
Wow! Another new function. As MS only update the Whats New on Fridays I got to find out a day early. I've been using Excel since 1993 (ver.5) Always something to learn. Thanks for this ❤
My Diwali just became a bit more knowledge-packed! The explanation you gave at 2:46 was just perfect. It reminded me of the Fibonacci Sequence but the 2nd number is hidden :o. I really like your thumbnail (gotta learn from yours). The offset function was new to me... Thanks!
Another wonderful function that no one except a small subset of “insiders” can try out. This one depends on lambda, which over an year after announced, still haven’t reached all insiders, forget anyone else. So far, vaporware at’s worst.
Its worse than vaporware. I got v2110 64bit and lambda let alone this scan function are no where to be found. Yet Microsoft has done something with the calculation engine - what I don't know. I do know that the calculation engine as measured by the time to calculate a sheet of Excel past version v1908 is over a 100 times slower, so darn slow that in my VBA application I must force the sheet manual calculate, otherwise after a minute or two of a sheet being updated with data the Excel hangs then suddenly crashes.? I had to revert many a workstation back to an older version until I finally figured out that MS has jacked up the calculation engine (apparently for the new yet to be released functions), disabling the autocalculate. As for Lambda, seems like a capability that would be better suited in VBA?
Thanks Leila for this brilliant video. Viewers should first watch your OFFSET and LAMBDA video to get the context; then they can move to this video. Love form India, Happy Diwali and wish you and your loved ones healthy and prosperous year ahead !
Leila, there's no need for the "0*a+b"-trick, just putting "b" in the last argument will work just fine for the reset: =SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))1,a+b,b)))
Never knew about this SCAN function. Maybe in the near future I can use this. I need to create a list of possible functions with a link to her vids. Thank you!
Thank you! I see daily use for this, but I need to drill down deeper. Example: 20 different sales people within each month. YTD for the salesperson. I’m certain I can get there eventually.
Actually is working with functions like lambda not so complicated in case, you don't lose yourself by defining the variables or under functions, which should cooperate with each other. It is so satisfying to see how the values change when their source gets updated.
Great intro to SCAN thanks Leila! For those without / nervous about SCAN, one could also try something like: =SUMIFS(sales,dates=DATE(YEAR(@dates),1,1)) Need to check my syntax but I think that general idea should work for YTD figures.
I really try to understand the reason for using functions very few will understand. Unless is for personal and only use when you build something for your manager, a colleague etc they need to understand what they are looking. The lambda λ function or the scan function etc despite being a powerful tool to the hands of a developer they can easily put off the ultimate user of a file. All these functions are powerful and versatile but must be addressed and presented (by Microsoft not you of course) in a more understandable and appealing way to the general public. Nice video.
@Leila Gharani, I'm having trouble finding more complex uses of SCAN. Have you covered SCAN further? Or know of where I can find more complex examples?
Nice demo of scan function. But what happens when someone sorts the data such that months are not in ascending order anymore? To fully exploit the potential of DAs we need functions that are not affected by order of data. I would prefer to do this with a recursive Lambda or a N*N matrix transformation (although it would result in lot of redundancies)
Ho to deal the situation where our arrays are horizontal spreading to multiple monthly ans yearly columns. Are we able to achieve the yearly sums through dynamic formulas. I find difficulties using dynamic arrays when we have months and years data.
Very nice solution Abdelkrim MESAI AHMED if you don't have the SCAN function. I do have it and tried in a fake data set and used an alternative solution from Rico S a few posts above Thanks for sharing
If you don't have a table then yes. In this case we have data organized in a table - so the advantage of a using a function that spills is the result is automatically updated once we get new data without pulling down the formula. But of course this is just one way among many.
Great video, well explained. Unfortunately, I cannot get it to work in a Table. You use it adjacent to the Table, but I don't see it being used like that in practice.
Wonderful function.... ...more then that beautiful voice u have..... lucky boyfriend or husband. Hey this is Anthony Joe Indian live from Fiji Island 🤟
XLOOKUP could replace offset if each monetary value only occurs once in the 'Sales' column. Or better yet provide the 'Month' column instead of the 'Sales'. and everywhere you use 'b' in the LAMBDA formula, replace it with an XLOOKUP in the other direction
this is off topic regarding the video. i would like to know if it's at all possible to to drag and drop a file path (like a link) without having to navigate to the file and going through multiple directories to get to the file i want.
I wonder why MS doesn't include status of these new functions in MS roadmap. It would be more easy to follow when something is rolling out for everyone. Now I just need to keep list of what I have heard and check if that has reached me.
Hi Leila, I would like to know that how to send emails from "get a row from excel" in power automate but from multiple rows.. pls guide.. thanks in advance
hi could you tell me how to scan ducoment in execl and add link of ducoment to cell I wnat to when I push scan command bottom start scanner to work and scan my document thanks
At the 7:56 mark " ... Honestly ... I'm not sure these Lambda Helper functions are for everyone ...". WHEW!!! 😵🥴. I knew I was in BIG trouble when 'Lambda' appeared in equation. My initial reaction was " ... oh gawd ... 😬 ... as my palms got sweaty and I could feel heart palpitations. A mug of hot coffee (on a cold Maryland morning) only made it worse. Once again, I loved the ease with which Leila gracefully glides through the presentation. But I will stick with @Sum while anchoring the first cell, and (hope to) figure out an @IF function to address the new year (which I think Leila taught us once before ... I think ... 🤔).
You can always use IF(MONTH(A2)1,E2+B2,B2) and then drag the function down. I believe the only thing this function improves is the need to drag the function down, right?
Really great but lamda is difficult can you please prepare one more video on Lamda. Have watched your Video on Lamda but was little difficult....... please if you can 🙏
Pardon my ignorance. I could have done this using a simple excel formula which is much easier to understand than use Scan, offset, Lambda etc. What's the usecase for SCAN function?
Long time fan of your channel, as a financial modeller and I had been working with Excel for nearly 20 years your vids are great! I am still trying to figure out SCAN formula, but personally for the sake of spill, can't we put the YTD column in a table and let it spill by simply summing up what the top value is and have an IF statement to check if it's Jan (or whatever the year starts) before summing? Is great to learn new formulas but I think we are over engineering something which IMHO relatively simple. Also a lot of people don't know how to use LAMDA (or even have it yet) so a lot of the time I have to get around problems with old school methods as my clients' Excel varies.
Thanks Phillip - agree. One advantage of using a function that spills is that you write it once and it automatically updates once we have new data added to our table.
@@LeilaGharani thanks for replying, but that's what I mean. If we put both the input data and the formulas in the same table (I will put the formula on the right just like you), if the input data has more rows, the formulas on the right will spill to reach to the same ending row as the input data. As an extra measure (I was working at a spreadsheet risk project) I would colour code the cells which has formulas to indicate they are not inputs just so the user knows not to touch or over write them. I use this setup up all the time and then further manipulatie my data by referencing the table as a range so my ranges in the referencing formulas will update, e.g. You don't need to do 1:10000 rows for an xlookup when you can reference the table column, so it should speed up your model as we only looking up rows we need.
Grab the file I used in the video from here 👉 pages.xelplus.com/scan-function-file
YOU ARE HEAVEN SENT!!! I wish you have an Excel book for ordinary Joes like me.
This is more complex than anything I am currently using excel for but I love these videos. When I do need to get back into Excel I know I can come right back to these videos and refresh my mind.
I love these videos about the new functions even if I'm unable to use them (or even to understant how they work).
This is really impressive!
Leila you make these complex problems so easy. You are just awesome
Nice, clear demonstration. Thanks !
Thank you. I like when you are taking excel formulas to the next level.
These tips are awesome. In a "simple" way we have this information without more. Congratulations on clarity.
Honestly, the lamda function is my favourite 😊thanks Leila you're the best
Best channel ever!!!! I actually got a promotion using all your videos and improving my office skills. I can't thank you enough.
Wow, that's great to hear! Congrats on the promotion, Paul!
Thanks Leila, great explanation.
I'd simply put a pivot table next to the table and use sum and running total options
Simply amazing.
Stunning.
Thank you
Excellent thanks Leila!
For some calculations, the LAMBDA approach offers some real benefits
In this instance, I would prefer the YTD to be calculated in the original sales table:
Add a new calculated column (YTD) in the Sales table
=IF(MONTH([@Month])=1,[@Sales],[@Sales]+INDEX([YTD],ROW([@Sales])-ROW(Sales[[#Headers],[Sales]])-1,1))
Essentially it says, if month is Jan, use sales, otherwise add sales to prior YTD total
Alternatively
=SUMPRODUCT((YEAR(INDEX([Month],1,1):[@Month])=YEAR([@Month]))*INDEX([Sales],1,1):[@Sales])
Note, INDEX([Month],1,1):[@Month] is the range from the first row to the current row
your knowledge is from another world! hahaha thank you lei :)
This was extremely helpful. Thanks to scan plus lambda I was able to calculate values adding up per month and finally finding the month with the highest value without pivot and fully dynamic.
Very nice. Opens up immense possibilities.
Excellent. Thank you Leila !!
Excellent explanation of SCAN. I am an old-school LISP programmer. I am so glad that finally functional programming has arrived in Excel. Thus I prefer single-cell formulas. Thanks to your video I can add SCAN to my kit of powerful tools.
Glad it was helpful!
Brilliant 🤟🏼
Thank you for sharing with us the news!
For Finally Scan function is used for Cumulative calculation, I love this formula, Thanks for your videos
😊
Leila, thanks for all the great videos.
Just to answer your question on alternative ways to calculate YTD balance, I would have used the following formula
=IF(MONTH([@Month])=1,0,OFFSET([@YTD],-1,0))+[@Sales]
Or, the rows are not sorted, then will use Sumifs
=SUMIFS([Sales],[Month],"=" & DATE(YEAR([@Month]),1,1))
wonderful helpful function and best quality course . Thank you lovely and talented Leila
I like when you are taking excel formulas to the next level.
Leila Gharani
I am so sad I only found you lately ; you are my excel superhero 🎉
I needed a way to reference another cell not via a fixed reference but a way of calculating whichever other cell I needed. Google did not find the OFFSET function for me, and it's exactly what i was looking for, so thank you!
Tried, working , great solution.
Ok, that is super slick! As always, thanks for the awesome tutorial!
Glad you like it!
Hi Leila. Great example of how to use SCAN and LAMBDA to create a useful result for a real world challenge! Thanks for sharing :)) Thumbs up!!
Thanks, Wayne!
Thank you. This will be useful, once released, for a 26 year monthly Excel I maintain to have running FYTD. Actually even more used for FYTD since it is everywhere.
I can see that I need to replace some of my formulas for existing files. Thanks Leila!🥰
You're so welcome!
Wonderful tutorial 👍🌹❤
Thanks, really useful 👍
Leila you are an Excel formula magician! 😄
Leila~, the Excel Goddess!
Leila, this is a monumentally powerful function! I'm trying to get it to determine streaks in a list, like winning streaks for sports teams or stock markets.
Very impressive explanation Leila. I have the beta on my home PC, and looking forward to an update at work.
Thanks for this amazing tutorial.
You're very welcome!
Wow! Another new function. As MS only update the Whats New on Fridays I got to find out a day early. I've been using Excel since 1993 (ver.5) Always something to learn. Thanks for this ❤
My pleasure :)
Yeah! Another great function to use for model building. ,😃
U always have something that i am seeking for.
My Diwali just became a bit more knowledge-packed! The explanation you gave at 2:46 was just perfect. It reminded me of the Fibonacci Sequence but the 2nd number is hidden :o. I really like your thumbnail (gotta learn from yours). The offset function was new to me... Thanks!
My pleasure. Happy Diwali 😊
Happy Diwali
Another wonderful function that no one except a small subset of “insiders” can try out. This one depends on lambda, which over an year after announced, still haven’t reached all insiders, forget anyone else.
So far, vaporware at’s worst.
Agree completely, very frustrating all the insider features that I want to get my hands on and still not available.
Agree - It's frustrating when it take so long for them to roll out.
Its worse than vaporware. I got v2110 64bit and lambda let alone this scan function are no where to be found. Yet Microsoft has done something with the calculation engine - what I don't know. I do know that the calculation engine as measured by the time to calculate a sheet of Excel past version v1908 is over a 100 times slower, so darn slow that in my VBA application I must force the sheet manual calculate, otherwise after a minute or two of a sheet being updated with data the Excel hangs then suddenly crashes.? I had to revert many a workstation back to an older version until I finally figured out that MS has jacked up the calculation engine (apparently for the new yet to be released functions), disabling the autocalculate. As for Lambda, seems like a capability that would be better suited in VBA?
Thanks so much Leila for this video. I better understand how the new SCAN function works with this use case
Glad it was helpful!
Fabulous video!🤗🤗🤗🤗
Thank you 🤗
Thanks for this help me so much, good video 👍👍👍👍👍
Thanks Leila for this brilliant video. Viewers should first watch your OFFSET and LAMBDA video to get the context; then they can move to this video. Love form India, Happy Diwali and wish you and your loved ones healthy and prosperous year ahead !
Happy Diwali 😊
Good one❤
Nice function
Leila, there's no need for the "0*a+b"-trick, just putting "b" in the last argument will work just fine for the reset:
=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))1,a+b,b)))
Smart! Thanks for sharing Geert! 🙌
Interesting!
You are wonderful mam ❤️❤️❤️❤️
Nice information 👍
Thank You 😊
Very good
Excellent 👌
Thank you! Cheers!
excellent
Never knew about this SCAN function. Maybe in the near future I can use this. I need to create a list of possible functions with a link to her vids. Thank you!
Excellent video. Looks like DOSUBS function of my hp48G!
Thanks Leila for your great explaination, it is will be an alternative for fixed asset depreciation calculation. right?
I've seen some tutorials on this and think it's a pretty amazing function!
Thank you! I see daily use for this, but I need to drill down deeper. Example: 20 different sales people within each month. YTD for the salesperson. I’m certain I can get there eventually.
Quite interesting
amazing
I have just ❤️ to pass you 👍👍👍
❤nice
Actually is working with functions like lambda not so complicated in case, you don't lose yourself by defining the variables or under functions, which should cooperate with each other. It is so satisfying to see how the values change when their source gets updated.
Great intro to SCAN thanks Leila!
For those without / nervous about SCAN, one could also try something like:
=SUMIFS(sales,dates=DATE(YEAR(@dates),1,1))
Need to check my syntax but I think that general idea should work for YTD figures.
I have good number of sheets I will be updating to include this function. Thanks for the insight!
Glad it was helpful!
Thanks Leila. Is this released intending to make the recursive lambda easy?
I really try to understand the reason for using functions very few will understand. Unless is for personal and only use when you build something for your manager, a colleague etc they need to understand what they are looking. The lambda λ function or the scan function etc despite being a powerful tool to the hands of a developer they can easily put off the ultimate user of a file. All these functions are powerful and versatile but must be addressed and presented (by Microsoft not you of course) in a more understandable and appealing way to the general public. Nice video.
Thanks for your feedback, Christos!
@Leila Gharani, I'm having trouble finding more complex uses of SCAN. Have you covered SCAN further? Or know of where I can find more complex examples?
I like you so much 😊
Just come across this scan functio when I press =S in a cell. Nice video.
👍
Thanks for this clear explanation but a little complex. maybe easier by VBA.
this is awesome and very interesting. I have only one query can this scan function worked horizontal also?
Thanks for yet again a usefull video i! do you have a video about basic DAX Power BI pls? thanks Giovanni
Happy Diwali Leila!🎉🥰
🎉
Obrigado 🥰🥰🥰🥰🥰🥰🥰🥰
Nice demo of scan function. But what happens when someone sorts the data such that months are not in ascending order anymore? To fully exploit the potential of DAs we need functions that are not affected by order of data. I would prefer to do this with a recursive Lambda or a N*N matrix transformation (although it would result in lot of redundancies)
Use the sort function on the data before the scan?
"Aren't really for everyone" - so honest.
Ho to deal the situation where our arrays are horizontal spreading to multiple monthly ans yearly columns. Are we able to achieve the yearly sums through dynamic formulas. I find difficulties using dynamic arrays when we have months and years data.
If the dates are in column A and Sales are in column B this formula will work perfectly
SUM(IF(YEAR($A$2:A2)=YEAR(A2),($B$2:B2)))
Very nice solution Abdelkrim MESAI AHMED if you don't have the SCAN function. I do have it and tried in a fake data set and used an alternative solution from Rico S a few posts above Thanks for sharing
If you don't have a table then yes. In this case we have data organized in a table - so the advantage of a using a function that spills is the result is automatically updated once we get new data without pulling down the formula. But of course this is just one way among many.
Great video, well explained. Unfortunately, I cannot get it to work in a Table. You use it adjacent to the Table, but I don't see it being used like that in practice.
Wonderful function.... ...more then that beautiful voice u have..... lucky boyfriend or husband.
Hey this is Anthony Joe Indian live from Fiji Island 🤟
Thank you so much 🙂
XLOOKUP could replace offset if each monetary value only occurs once in the 'Sales' column. Or better yet provide the 'Month' column instead of the 'Sales'. and everywhere you use 'b' in the LAMBDA formula, replace it with an XLOOKUP in the other direction
this is off topic regarding the video. i would like to know if it's at all possible to to drag and drop a file path (like a link) without having to navigate to the file and going through multiple directories to get to the file i want.
I wonder why MS doesn't include status of these new functions in MS roadmap. It would be more easy to follow when something is rolling out for everyone. Now I just need to keep list of what I have heard and check if that has reached me.
Hi Leila, I would like to know that how to send emails from "get a row from excel" in power automate but from multiple rows.. pls guide.. thanks in advance
We can use Index+Sequence function instead offset
hi could you tell me how to scan ducoment in execl and add link of ducoment to cell
I wnat to when I push scan command bottom start scanner to work and scan my document
thanks
Since it's getting close to the end of the year how about some tips on how to layout yearly reports
At the 7:56 mark " ... Honestly ... I'm not sure these Lambda Helper functions are for everyone ...". WHEW!!! 😵🥴.
I knew I was in BIG trouble when 'Lambda' appeared in equation. My initial reaction was " ... oh gawd ... 😬 ... as my palms got sweaty and I could feel heart palpitations. A mug of hot coffee (on a cold Maryland morning) only made it worse.
Once again, I loved the ease with which Leila gracefully glides through the presentation. But I will stick with @Sum while anchoring the first cell, and (hope to) figure out an @IF function to address the new year (which I think Leila taught us once before ... I think ... 🤔).
These functions are definitely not for everyone - me included 😁
You can always use IF(MONTH(A2)1,E2+B2,B2) and then drag the function down. I believe the only thing this function improves is the need to drag the function down, right?
@@BHhaaf good question, yeah I think she mentioned the function will spill the formula for you. But I like your alternate solution lol
what lambda formula if i have 1 other parameter such as name product? so i want to calculate every sales of product year to date?
Really great but lamda is difficult can you please prepare one more video on Lamda. Have watched your Video on Lamda but was little difficult....... please if you can 🙏
Does this work across i.e. can I get it to spill across?
How we will apply in debit, creadit and balance situation?
Pardon my ignorance. I could have done this using a simple excel formula which is much easier to understand than use Scan, offset, Lambda etc. What's the usecase for SCAN function?
Long time fan of your channel, as a financial modeller and I had been working with Excel for nearly 20 years your vids are great!
I am still trying to figure out SCAN formula, but personally for the sake of spill, can't we put the YTD column in a table and let it spill by simply summing up what the top value is and have an IF statement to check if it's Jan (or whatever the year starts) before summing? Is great to learn new formulas but I think we are over engineering something which IMHO relatively simple. Also a lot of people don't know how to use LAMDA (or even have it yet) so a lot of the time I have to get around problems with old school methods as my clients' Excel varies.
Thanks Phillip - agree. One advantage of using a function that spills is that you write it once and it automatically updates once we have new data added to our table.
@@LeilaGharani thanks for replying, but that's what I mean. If we put both the input data and the formulas in the same table (I will put the formula on the right just like you), if the input data has more rows, the formulas on the right will spill to reach to the same ending row as the input data. As an extra measure (I was working at a spreadsheet risk project) I would colour code the cells which has formulas to indicate they are not inputs just so the user knows not to touch or over write them. I use this setup up all the time and then further manipulatie my data by referencing the table as a range so my ranges in the referencing formulas will update, e.g. You don't need to do 1:10000 rows for an xlookup when you can reference the table column, so it should speed up your model as we only looking up rows we need.