How to Calculate YTD (Year to Date) Values in Excel using the SCAN function
Вставка
- Опубліковано 16 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
A common task for finance professionals is to create YTD (Year-To-Date) calculations in Microsoft Excel. Usually these formulas can get quite complex. You might use a combination of SUMPRODUCT and OFFSET. In this video I'll show you a solution using the new Excel SCAN function. We create a flexible YTD function so that you can easily adjust for fiscal months. If you get more data, the Year to date calculations automatically update.
⬇️ Download the workbook here: pages.xelplus.com/scan-functi...
Discover how to:
⩥ Use the SCAN function for simple to-date calculations, allowing formulas to automatically spill down without manual dragging.
⩥ Integrate LAMBDA functions with SCAN for custom calculations, enabling you to process data with precision.
⩥ Dynamically calculate YTD values, adjusting for new calendar years within your data set to reset accumulations accurately.
⩥ Apply practical examples with sales data to see the SCAN function in action, demonstrating both to-date and YTD calculations.
⩥ Adjust for fiscal calendars, showing how to modify the formula for different fiscal year starts, ensuring versatility across various accounting periods.
We'll combine the SCAN, LAMBDA and Excel OFFSET functions to create a (simple) formula that dynamically updates your results and resets whenever it crosses a year-end.
Note: The SCAN function is available in Microsoft 365.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
00:00 Calculate YTD Balances Dynamically in Excel
00:41 How to Calculate To-Date With the SCAN Function
02:42 How the Excel SCAN Function Works
03:59 How to Calculate Year-To-Date With the SCAN Function
07:53 Wrap Up
🎬 LINKS to related videos:
Excel LAMBDA Function: • Excel LAMBDA - HOW & W...
Excel OFFSET Function: • Excel OFFSET Function ...
🎒 Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
📚 Excel RESOURCES I Recommend: www.xelplus.com/resources/
🎥 GEAR I use: www.xelplus.com/resources/#gear
More resources on my Amazon page: www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: / lgharani
Twitter: / leilagharani
LinkedIn: / leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
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.
Nice, clear demonstration. Thanks !
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
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))
Honestly, the lamda function is my favourite 😊thanks Leila you're the best
Leila you make these complex problems so easy. You are just awesome
These tips are awesome. In a "simple" way we have this information without more. Congratulations on clarity.
Thank you. I like when you are taking excel formulas to the next level.
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!
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 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!
For Finally Scan function is used for Cumulative calculation, I love this formula, Thanks for your videos
😊
This is really impressive!
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.
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!
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 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
Thanks Leila, great explanation.
Simply amazing.
Stunning.
Thank you
wonderful helpful function and best quality course . Thank you lovely and talented 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!!
Thanks, Wayne!
your knowledge is from another world! hahaha thank you lei :)
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 :)
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
Excellent thanks Leila!
Very nice. Opens up immense possibilities.
Excellent. Thank you Leila !!
Very impressive explanation Leila. I have the beta on my home PC, and looking forward to an update at work.
I can see that I need to replace some of my formulas for existing files. Thanks Leila!🥰
You're so welcome!
Thank you for sharing with us the news!
Ok, that is super slick! As always, thanks for the awesome tutorial!
Glad you like it!
I like when you are taking excel formulas to the next level.
Leila Gharani
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! 🙌
Tried, working , great solution.
I am so sad I only found you lately ; you are my excel superhero 🎉
Leila you are an Excel formula magician! 😄
Leila~, the Excel Goddess!
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 !
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?
Wonderful tutorial 👍🌹❤
Thanks for this amazing tutorial.
You're very welcome!
Thanks so much Leila for this video. I better understand how the new SCAN function works with this use case
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!
Thanks, really useful 👍
Brilliant 🤟🏼
Thanks for this help me so much, good video 👍👍👍👍👍
U always have something that i am seeking for.
Yeah! Another great function to use for model building. ,😃
Nice information 👍
Thank You 😊
Fabulous video!🤗🤗🤗🤗
Thank you 🤗
Excellent video. Looks like DOSUBS function of my hp48G!
Nice function
Good one❤
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.
Very good
excellent
Interesting!
Excellent 👌
Thank you! Cheers!
You are wonderful mam ❤️❤️❤️❤️
I've seen some tutorials on this and think it's a pretty amazing function!
Quite interesting
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.
👍
I have good number of sheets I will be updating to include this function. Thanks for the insight!
Glad it was helpful!
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!
amazing
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.
Thanks Leila for your great explaination, it is will be an alternative for fixed asset depreciation calculation. right?
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?
Thanks for yet again a usefull video i! do you have a video about basic DAX Power BI pls? thanks Giovanni
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.
❤nice
Happy Diwali Leila!🎉🥰
🎉
I have just ❤️ to pass you 👍👍👍
"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.
I like you so much 😊
Thanks Leila. Is this released intending to make the recursive lambda easy?
@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?
Obrigado 🥰🥰🥰🥰🥰🥰🥰🥰
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 🙂
We can use Index+Sequence function instead offset
Since it's getting close to the end of the year how about some tips on how to layout yearly reports
Not feeling super comfy with LAMBDA yet. Although not perfect I managed to find a solution to replace the OFFSET function.
New updated formula would look like this: =SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(INDEX(Sales[Month],ROW(b)-1))1,a+b,b)))
Please let me know if you found a better solution! 😉
That's great, just the sort of solution I'm looking for (without OFFSET). Not perfect? I don't know how to improve it.😀
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 🙏
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 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
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.
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 ...
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.
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
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?
Does this work across i.e. can I get it to spill across?
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
Wow 😯, do u give excel classes ?? 🙈🙈🙈🔥🔥🔥🔥
Sure do: www.xelplus.com