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).

These tips are awesome. In a "simple" way we have this information without more. Congratulations on clarity.

Leila you make these complex problems so easy. You are just awesome

Honestly, the lamda function is my favourite 😊thanks Leila you're the best

Thank you. I like when you are taking excel formulas to the next level.

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.

I'd simply put a pivot table next to the table and use sum and running total options

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.

For Finally Scan function is used for Cumulative calculation, I love this formula, Thanks for your videos 😊

Thanks Leila, great explanation.

This is really impressive!

Simply amazing. Stunning. Thank you

wonderful helpful function and best quality course . Thank you lovely and talented Leila

your knowledge is from another world! hahaha thank you lei :)

Best channel ever!!!! I actually got a promotion using all your videos and improving my office skills. I can't thank you enough.

@LeilaGharani

2 жыл бұрын

Wow, that's great to hear! Congrats on the promotion, Paul!

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.

Excellent thanks Leila!

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!!

@LeilaGharani

2 жыл бұрын

Thanks, Wayne!

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!

Nice, clear demonstration. Thanks !

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.

@LeilaGharani

Жыл бұрын

Glad it was helpful!

Ok, that is super slick! As always, thanks for the awesome tutorial!

@LeilaGharani

2 жыл бұрын

Glad you like it!

Very impressive explanation Leila. I have the beta on my home PC, and looking forward to an update at work.

I like when you are taking excel formulas to the next level. Leila Gharani

Tried, working , great solution.

Thank you for sharing with us the news!

I can see that I need to replace some of my formulas for existing files. Thanks Leila!🥰

@LeilaGharani

2 жыл бұрын

You're so welcome!

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))

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 ❤

@LeilaGharani

2 жыл бұрын

My pleasure :)

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!

@LeilaGharani

2 жыл бұрын

My pleasure. Happy Diwali 😊

@y2nsamy2nsam3

2 жыл бұрын

Happy Diwali

Leila you are an Excel formula magician! 😄

@Yachid

2 жыл бұрын

Leila~, the Excel Goddess!

Wonderful tutorial 👍🌹❤

Brilliant 🤟🏼

Thanks, really useful 👍

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 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 !

@LeilaGharani

2 жыл бұрын

Happy Diwali 😊

Thanks for this help me so much, good video 👍👍👍👍👍

Thanks for this amazing tutorial.

@LeilaGharani

2 жыл бұрын

You're very welcome!

Fabulous video!🤗🤗🤗🤗

@LeilaGharani

2 жыл бұрын

Thank you 🤗

Thanks so much Leila for this video. I better understand how the new SCAN function works with this use case

@LeilaGharani

2 жыл бұрын

Glad it was helpful!

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!

Good one❤

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

Yeah! Another great function to use for model building. ,😃

U always have something that i am seeking for.

Nice function

Excellent video. Looks like DOSUBS function of my hp48G!

Very good

Nice information 👍

@metimeMaths

2 жыл бұрын

Thank You 😊

You are wonderful mam ❤️❤️❤️❤️

Excellent 👌

@LeilaGharani

2 жыл бұрын

Thank you! Cheers!

excellent

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.

@davidferrick

2 жыл бұрын

Agree completely, very frustrating all the insider features that I want to get my hands on and still not available.

@LeilaGharani

2 жыл бұрын

Agree - It's frustrating when it take so long for them to roll out.

@robertbenz8260

2 жыл бұрын

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?

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.

I've seen some tutorials on this and think it's a pretty amazing 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)))

@LeilaGharani

2 жыл бұрын

Smart! Thanks for sharing Geert! 🙌

Interesting!

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.

@LeilaGharani

2 жыл бұрын

Thanks for your feedback, Christos!

I have good number of sheets I will be updating to include this function. Thanks for the insight!

@LeilaGharani

2 жыл бұрын

Glad it was helpful!

amazing

I have just ❤️ to pass you 👍👍👍

Happy Diwali Leila!🎉🥰

@LeilaGharani

2 жыл бұрын

🎉

❤nice

Thanks for yet again a usefull video i! do you have a video about basic DAX Power BI pls? thanks Giovanni

Thanks for this clear explanation but a little complex. maybe easier by VBA.

Just come across this scan functio when I press =S in a cell. Nice video.

@LeilaGharani

Жыл бұрын

👍

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.

Thanks Leila for your great explaination, it is will be an alternative for fixed asset depreciation calculation. right?

I like 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 awesome and very interesting. I have only one query can this scan function worked horizontal also?

Obrigado 🥰🥰🥰🥰🥰🥰🥰🥰

Thanks Leila. Is this released intending to make the recursive lambda easy?

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)))

@jazzista1967

2 жыл бұрын

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

@LeilaGharani

2 жыл бұрын

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.

Wonderful function.... ...more then that beautiful voice u have..... lucky boyfriend or husband. Hey this is Anthony Joe Indian live from Fiji Island 🤟

@LeilaGharani

2 жыл бұрын

Thank you so much 🙂

"Aren't really for everyone" - so honest.

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.

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.

@LeilaGharani

2 жыл бұрын

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.

@philwongnz

2 жыл бұрын

@@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.

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)

@paulfelce8757

2 жыл бұрын

Use the sort function on the data before the scan?

Since it's getting close to the end of the year how about some tips on how to layout yearly reports

@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?

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 🙏

We can use Index+Sequence function instead offset

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.

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.

Wow 😯, do u give excel classes ?? 🙈🙈🙈🔥🔥🔥🔥

@LeilaGharani

2 жыл бұрын

Sure do: www.xelplus.com

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 ... 🤔).

@LeilaGharani

2 жыл бұрын

These functions are definitely not for everyone - me included 😁

@BHhaaf

2 жыл бұрын

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?

@pmlover1810

2 жыл бұрын

@@BHhaaf good question, yeah I think she mentioned the function will spill the formula for you. But I like your alternate solution lol

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.

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.

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

Does this work across i.e. can I get it to spill across?

Before watching this, I just use OFFSET function with a month cell reference for getting the YTD of my Sales Metrics. 😍

Lock SUM fórmula (locking the fístula value), would give the sabe result to Date. As for YTD just use a 12 cells array