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.
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.
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!
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?
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
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))
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)))
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!
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 !
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.
Hallo Leila, tanks for this video, i am italiano but i follow you from two years. I have change formulas in this way: =+SCAN(0;TABSCAN1[SALES];LAMBDA(A;B;SE(E(MESE(SCARTO(B;;-1))1;MESE(SCARTO(B;;-1))7);A+B;0*A+B))) for to sum each six months, ciao dall'italia
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 ❤
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.
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.
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!
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.
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.
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.
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.
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.
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.
Wonderful function.... ...more then that beautiful voice u have..... lucky boyfriend or husband. Hey this is Anthony Joe Indian live from Fiji Island 🤟
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?
As Lars says, this can be achieved without *any* functions - SCAN, LAMBDA, OFFSET, LET etc Putting it another way this is surprisingly over-engineered :) When I was young we proceeded as follows: 1) Start a table in B2 with column names MonthYear, Amount, RunTot 2) Say B3 is Jan-2020, B4 is Feb-2020 ... , B25 is Nov-2021 3) Say C3 is 1, C4 is 2 ... , C25 is 23 4) Set D3 to IF(MONTH([@MonthYear])=1,0,N(D2))+[@Amount] Job done. You have output 1 3 6 10 15 21 28 36 45 55 66 78 13 27 42 58 75 93 112 132 153 175 198
Very true! She’s demonstrating SCAN function using YTD as the example. The real power of SCAN is in combination with other dynamic array functions, which is a much more advanced than most people care about.
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
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?
I am not sure it is simplier but without lambda fonction and (unfortunately?) without Table you can use: SUM(B$2:B2*--(YEAR(A$2:A2)=YEAR(A2))) with A the column of dates and B the values of sales (or whatever). Without Table you have to drag it till your last line or the entire column ...
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
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.
I Used this Month Name starts from A2 Amount Starts from B2 Result in C2 IF(IF(ISNUMBER(SEARCH("Jan", TEXT(A2,"MMMM"))), "Yes", "No")="Yes",B2,C1+B2) Cons : sum limited to 12 months only
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.
@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?
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
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)
Ideally you'd be able to reference the entire table in the Scan function and then just reference particular columns, or draw the Month column into the Scan function via a variable in the LAMBDA, but that's not possible either. You're left with what seems to be a very inefficient approach where you're referring back to the initial dataset at each stage of the recursion via INDEX or OFFSET. It just doesn't seem as nice as it could be. To avoid OFFSET, you can use: =SCAN(0,Sales[Sales],LAMBDA(a,b,(MONTH(INDEX(Sales[Month],ROW(b)-ROW(Sales[#Headers]),))1)*a+b)) This also avoids the IF() statement, which might speed it up marginally, but it still doesn't seem right. I'm unconvinced by it, even if it works!
This is a very elegant solution Rico! I just tried on a fake data set and works beautifully. Reference the entire headers on the table #Headers is great. I dont like to use offset because it keeps calculating over and over.
@@jazzista1967 Yes, it's okay. However, what would be better would be some sort of FILTERSCAN or SCANIFS function, that allowed us to apply a logical statement to the SCAN function. Something a bit more positive than referencing the original dataset with each recursion in the function. Anyway, I can't complain too much!
@@ricos1497 Haha! I don't think you are complaining . Instead you are making a god point. Where is the recursion happening? On the B argument of the Lambda?
@@jazzista1967 strictly speaking, I don't believe it's recursion, it just seems that way. It looks like you're passing a value to be stored in "a", which is then acted upon by "b". However, I think what will actually occur is that the scan function will use the BYROW function (or mmult) to create the cumulative array. It seems to be too quick and not volatile (doesn't return NUM! error) to be using recursion.
I like and I can use it but not sure if it's easier than sumifs with expanding range. Big advantage though is that formula is much more compact and readable than with table nomenclature. Also,I find that many users have trouble setting expanding range with a table.
Darn this would help me resolve a lot of reporting issue I have. Funny also as this is reciprocating a simple programming loop function we used to learn first back in the days. I've just never took the time to learn VBA that is counterintuitive for me.
Grab the file I used in the video from here 👉 pages.xelplus.com/scan-function-file
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.
YOU ARE HEAVEN SENT!!! I wish you have an Excel book for ordinary Joes like me.
I love these videos about the new functions even if I'm unable to use them (or even to understant how they work).
I'd simply put a pivot table next to the table and use sum and running total options
Honestly, the lamda function is my favourite 😊thanks Leila you're the best
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!
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!
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!
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?
Thank you. I like when you are taking excel formulas to the next level.
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
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))
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! 🙌
I like when you are taking excel formulas to the next level.
Leila Gharani
Leila you are an Excel formula magician! 😄
Leila~, the Excel Goddess!
Nice, clear demonstration. Thanks !
I am so sad I only found you lately ; you are my excel superhero 🎉
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
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 😊
your knowledge is from another world! hahaha thank you lei :)
These tips are awesome. In a "simple" way we have this information without more. Congratulations on clarity.
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.
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!
U always have something that i am seeking for.
wonderful helpful function and best quality course . Thank you lovely and talented Leila
I can see that I need to replace some of my formulas for existing files. Thanks Leila!🥰
You're so welcome!
Thanks Leila, great explanation.
Just come across this scan functio when I press =S in a cell. Nice video.
👍
Hallo Leila, tanks for this video, i am italiano but i follow you from two years. I have change formulas in this way: =+SCAN(0;TABSCAN1[SALES];LAMBDA(A;B;SE(E(MESE(SCARTO(B;;-1))1;MESE(SCARTO(B;;-1))7);A+B;0*A+B))) for to sum each six months, ciao dall'italia
Thanks for sharing!
Excellent thanks Leila!
Very nice. Opens up immense possibilities.
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 :)
Thanks so much Leila for this video. I better understand how the new SCAN function works with this use case
Glad it was helpful!
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.
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've seen some tutorials on this and think it's a pretty amazing function!
Fabulous video!🤗🤗🤗🤗
Thank you 🤗
Nice information 👍
Thank You 😊
Tried, working , great solution.
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!
"Aren't really for everyone" - so honest.
Nice function
Leila you make these complex problems so easy. You are just awesome
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!
Excellent video. Looks like DOSUBS function of my hp48G!
Ok, that is super slick! As always, thanks for the awesome tutorial!
Glad you like it!
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.
Simply amazing.
Stunning.
Thank you
I have just ❤️ to pass you 👍👍👍
Yeah! Another great function to use for model building. ,😃
Wonderful tutorial 👍🌹❤
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.
Thanks for this amazing tutorial.
You're very welcome!
Thanks for this help me so much, good video 👍👍👍👍👍
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.
Good one❤
Very impressive explanation Leila. I have the beta on my home PC, and looking forward to an update at work.
I have good number of sheets I will be updating to include this function. Thanks for the insight!
Glad it was helpful!
this is awesome and very interesting. I have only one query can this scan function worked horizontal also?
Very good
You are wonderful mam ❤️❤️❤️❤️
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.
Brilliant 🤟🏼
Quite interesting
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.
Thanks, really useful 👍
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 🙂
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?
As Lars says, this can be achieved without *any* functions - SCAN, LAMBDA, OFFSET, LET etc
Putting it another way this is surprisingly over-engineered :) When I was young we proceeded as follows:
1) Start a table in B2 with column names MonthYear, Amount, RunTot
2) Say B3 is Jan-2020, B4 is Feb-2020 ... , B25 is Nov-2021
3) Say C3 is 1, C4 is 2 ... , C25 is 23
4) Set D3 to IF(MONTH([@MonthYear])=1,0,N(D2))+[@Amount]
Job done. You have output
1
3
6
10
15
21
28
36
45
55
66
78
13
27
42
58
75
93
112
132
153
175
198
Very true! She’s demonstrating SCAN function using YTD as the example. The real power of SCAN is in combination with other dynamic array functions, which is a much more advanced than most people care about.
@@xlrobot Care to share a usecase?
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
Since it's getting close to the end of the year how about some tips on how to layout yearly reports
We can use Index+Sequence function instead offset
Does this work across i.e. can I get it to spill across?
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
Please enable the translator. Regards from Cali Colombia
Excellent 👌
Thank you! Cheers!
Before watching this, I just use OFFSET function with a month cell reference for getting the YTD of my Sales Metrics. 😍
Interesting!
Thanks for yet again a usefull video i! do you have a video about basic DAX Power BI pls? thanks Giovanni
I am not sure it is simplier but without lambda fonction and (unfortunately?) without Table you can use: SUM(B$2:B2*--(YEAR(A$2:A2)=YEAR(A2)))
with A the column of dates and B the values of sales (or whatever). Without Table you have to drag it till your last line or the entire column ...
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
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.
Wow 😯, do u give excel classes ?? 🙈🙈🙈🔥🔥🔥🔥
Sure do: www.xelplus.com
Thanks Leila for your great explaination, it is will be an alternative for fixed asset depreciation calculation. right?
❤nice
I like you so much 😊
I Used this
Month Name starts from A2
Amount Starts from B2
Result in C2
IF(IF(ISNUMBER(SEARCH("Jan", TEXT(A2,"MMMM"))), "Yes", "No")="Yes",B2,C1+B2)
Cons : sum limited to 12 months only
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.
@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?
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
amazing
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?
This is really impressive!
Ideally you'd be able to reference the entire table in the Scan function and then just reference particular columns, or draw the Month column into the Scan function via a variable in the LAMBDA, but that's not possible either. You're left with what seems to be a very inefficient approach where you're referring back to the initial dataset at each stage of the recursion via INDEX or OFFSET. It just doesn't seem as nice as it could be. To avoid OFFSET, you can use:
=SCAN(0,Sales[Sales],LAMBDA(a,b,(MONTH(INDEX(Sales[Month],ROW(b)-ROW(Sales[#Headers]),))1)*a+b))
This also avoids the IF() statement, which might speed it up marginally, but it still doesn't seem right. I'm unconvinced by it, even if it works!
This is a very elegant solution Rico! I just tried on a fake data set and works beautifully. Reference the entire headers on the table #Headers is great. I dont like to use offset because it keeps calculating over and over.
@@jazzista1967 Yes, it's okay. However, what would be better would be some sort of FILTERSCAN or SCANIFS function, that allowed us to apply a logical statement to the SCAN function. Something a bit more positive than referencing the original dataset with each recursion in the function. Anyway, I can't complain too much!
@@ricos1497 Haha! I don't think you are complaining . Instead you are making a god point. Where is the recursion happening? On the B argument of the Lambda?
@@jazzista1967 strictly speaking, I don't believe it's recursion, it just seems that way. It looks like you're passing a value to be stored in "a", which is then acted upon by "b". However, I think what will actually occur is that the scan function will use the BYROW function (or mmult) to create the cumulative array. It seems to be too quick and not volatile (doesn't return NUM! error) to be using recursion.
That's great! Many thanks Rico! I agree, it would be great if SCAN could handle table references.
I like and I can use it but not sure if it's easier than sumifs with expanding range. Big advantage though is that formula is much more compact and readable than with table nomenclature. Also,I find that many users have trouble setting expanding range with a table.
Thanks for your feedback, Richard.
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?
You did not say if the excel file you are working on will attach itself to the email prior to send.?
You forgot to put the automatic subtitles. Could you please so kind put them?
Darn this would help me resolve a lot of reporting issue I have. Funny also as this is reciprocating a simple programming loop function we used to learn first back in the days. I've just never took the time to learn VBA that is counterintuitive for me.