HI Chandoo, here is a Trick, We don't need offset function just Sum function is enough. Trick is that just hold the Ctrl key while selecting YTD(E4) and D5 Cell.....you will be glad it change the Table reference to Cell reference and then the formula works fine...... !!!Thanks a lot...Keep Teaching us.
Hi lalit , this is Niha. I have a dowbt. I am trying to do YTD. But on % values. Example : I have sales data in % jan = 0.0% Feb=11.1% mar=0.0% Apr=100.0% Result/answer = 10.5% , this is in excel. I tried the CYTD formulas and getting the wrong result when validating it. Please let me know if you know anything about it. Thanks 🙏🏻
All dreamers out there don't lose hope this is temporary situations it will pass keep going you are on the way to success don't give up your success will, ✊ ✊ ✊
Thank you. Quarters start on months 1,4,7,10. All of these give remainder of 1 when divided by 3. So we can use =MOD(MONTH(date),3)=1 to check for the quarter start condition. The rest is as per what we did for YTD.
Thanks for the quick reply Chandoo. This formula works but it doesn’t reset when Q2 starts. Thats the issue i was having earlier, please let me know if theres another function i can use. Thanks in advance
Dear Chandoo, Using the SCAN function, the data spill is obtained: =SCAN(0,D5:D27,LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))1,x+y,0*x+y))) -> YTD (January Start) =SCAN(0,D5:D27,LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))4,x+y,0*x+y))) -> YTD (April Start) In the table, your calculation is better, because, if you want to use the SCAN function, the calculation would have to be done in an external column: =SCAN(0,data[Registrations],LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))I4,x+y,0*x+y))) -> YTD TABLE (Starts according to the month established in cell I4) 🤗
That is a good use of SCAN & LAMDBA functions. I saw the video Leila did on this few days ago. As I don't yet have access to LAMBDA & SCAN, I prefer the easier options.
Hi @Chandoo. Can you make a video please, about this same topic "YTD"? But making it dynamic. Having a dropdown list with the months-year? For example, we have this table: | Month | Amount | |--------|------------| | Jan-22 | $123.43 | | Feb-22 | $154.54 | | Mar-22 | $21.92 | | Apr-22 | $143.11 | | May-22 | $77.00 | | Jun-22 | $186.98 | | Jul-22 | $142.52 | | Aug-22 | $112.57 | | Sep-22 | $55.57 | | Oct-22 | $149.70 | | Nov-22 | $95.30 | | Dec-22 | $124.52 | | Jan-23 | $2.36 | | Feb-23 | $158.92 | | Mar-23 | $170.61 | | Apr-23 | $15.61 | | May-23 | $70.12 | | Jun-23 | $17.03 | | Jul-23 | $102.85 | | Aug-23 | $33.70 | | Sep-23 | $25.05 | | Oct-23 | $132.00 | | Nov-23 | $68.54 | | Dec-23 | $181.37 | Now, I want to see the comparative about YTD Aug-23 (from jan-23 to aug-23) vs YTD Aug-22 (from jan-22 to aug-22). But when I say Dynamic, But when I say "Dynamic" I mean that there is a cell containing the dropdown list with the end months. For example if the cell says "Aug-23", it means that it has to calculate the YTD from Jan-23 to Aug-23. If the cell says "Sept-23", then it should calculate the YTD from Jan-23 to Aug-23. That is, it also has to respect the year.
A possible problem in YTD calculations is when the data contains gaps. The methods presented works if periods with no registrations are included with a zero value. That is not always the case.
never used those first 2 elements in an offset before. i used offset often usually to defina a range that might not be in a table. we a plug-in to pull data into excel and it overrides the sheet. so i define range as offset(a1,0,0,counta(a:a),counta(1:1)).
![АЛАУДИНОВ у Скабеевой: эти их ВСУ нас НЕ ДОГОНЯТ 😁 [Пародия]](http://i.ytimg.com/vi/an0anqU4WGQ/mqdefault.jpg)
Amazing I was using two tables to get YTD numbers... By your help it was so easy now... Thanks a lot ... Chandoo... 👌👍
Most welcome 😊
One of my my professor in the last semester of my MBA program recommended me to subscribe to your channel. I think that was a great recommendation.
HI Chandoo, here is a Trick, We don't need offset function just Sum function is enough. Trick is that just hold the Ctrl key while selecting YTD(E4) and
D5 Cell.....you will be glad it change the Table reference to Cell reference and then the formula works fine...... !!!Thanks a lot...Keep Teaching us.
That is CRAZY!!!. I never knew it. Thank you so much for sharing it. Donut for you Lalit 🍩
@@chandoo_ Thanks Chandoo!
Hi lalit , this is Niha. I have a dowbt. I am trying to do YTD. But on % values.
Example : I have sales data in %
jan = 0.0%
Feb=11.1%
mar=0.0%
Apr=100.0%
Result/answer = 10.5% , this is in excel. I tried the CYTD formulas and getting the wrong result when validating it.
Please let me know if you know anything about it. Thanks 🙏🏻
Result = CYTD ( I am trying here for calendar year. Jan = 1st month).
All dreamers out there don't lose hope this is temporary situations it will pass keep going you are on the way to success don't give up your success will, ✊ ✊ ✊
Thanks
Thanks for this video....very helpful
Hi! Thanks in advance. I use (Sumar.Si.Conjunto) SUMIFS to do it, with some criteria
You are welcome Fernando :)
When you believe in yourself you become unstoppable 💪
That is awesome 😎
you are truly AMAZING...sir. i have no words to admire you..thank you so so much for this kind of information.
You are most welcome
thanks for knowledge
and Sample File
thanks very much
I can follow to step by step
Chandoo...I'm amazed 😮 to think how many of such cool tricks are in your Excel arsenal!
Really Chandoo himself is a Google of Excel!!!
Thanks Vinod. I enjoy learning new things and sharing them with you all 😀
Hi Chandoo. Great tips! Thanks for sharing both techniques :)) Thumbs up!!
My pleasure 😊
This is absolutely gold!! Does anyone know how I can use this formula to calculate QTD?
Thank you. Quarters start on months 1,4,7,10. All of these give remainder of 1 when divided by 3. So we can use =MOD(MONTH(date),3)=1 to check for the quarter start condition. The rest is as per what we did for YTD.
Thanks for the quick reply Chandoo. This formula works but it doesn’t reset when Q2 starts. Thats the issue i was having earlier, please let me know if theres another function i can use. Thanks in advance
Thanks for sharing! 👍
My pleasure Frankwhite...
Hi, I want to know how we can calculate ytd if the table is in horizontal format and we want ytd sum in one cell only.
Thanks Chandoo :)
You are welcome John.
Great video, thank you Chandoo!
Thank you!! this really helped!
How can I use in power bi pls help
Dear Chandoo,
Using the SCAN function, the data spill is obtained:
=SCAN(0,D5:D27,LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))1,x+y,0*x+y))) -> YTD (January Start)
=SCAN(0,D5:D27,LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))4,x+y,0*x+y))) -> YTD (April Start)
In the table, your calculation is better, because, if you want to use the SCAN function, the calculation would have to be done in an external column:
=SCAN(0,data[Registrations],LAMBDA(x,y,IF(MONTH(OFFSET(y,,-1))I4,x+y,0*x+y))) -> YTD TABLE
(Starts according to the month established in cell I4) 🤗
That is a good use of SCAN & LAMDBA functions. I saw the video Leila did on this few days ago. As I don't yet have access to LAMBDA & SCAN, I prefer the easier options.
Amazing chandoo thanks 😊
Welcome 😊
How do you calculate the YTD for payslips?
Sir this is like running total
Hi @Chandoo.
Can you make a video please, about this same topic "YTD"? But making it dynamic. Having a dropdown list with the months-year?
For example, we have this table:
| Month | Amount |
|--------|------------|
| Jan-22 | $123.43 |
| Feb-22 | $154.54 |
| Mar-22 | $21.92 |
| Apr-22 | $143.11 |
| May-22 | $77.00 |
| Jun-22 | $186.98 |
| Jul-22 | $142.52 |
| Aug-22 | $112.57 |
| Sep-22 | $55.57 |
| Oct-22 | $149.70 |
| Nov-22 | $95.30 |
| Dec-22 | $124.52 |
| Jan-23 | $2.36 |
| Feb-23 | $158.92 |
| Mar-23 | $170.61 |
| Apr-23 | $15.61 |
| May-23 | $70.12 |
| Jun-23 | $17.03 |
| Jul-23 | $102.85 |
| Aug-23 | $33.70 |
| Sep-23 | $25.05 |
| Oct-23 | $132.00 |
| Nov-23 | $68.54 |
| Dec-23 | $181.37 |
Now, I want to see the comparative about YTD Aug-23 (from jan-23 to aug-23) vs YTD Aug-22 (from jan-22 to aug-22).
But when I say Dynamic,
But when I say "Dynamic" I mean that there is a cell containing the dropdown list with the end months.
For example if the cell says "Aug-23", it means that it has to calculate the YTD from Jan-23 to Aug-23. If the cell says "Sept-23", then it should calculate the YTD from Jan-23 to Aug-23. That is, it also has to respect the year.
Can you explain how to calculate WTD & MTD in the same way
Thanks in advance
Really very helpful formula!!
Glad it was helpful!
That was genius !
Thanks Raed...
Thanks!!!!!
Welcome Chris :)
I am pleasantly surprised well done!
You are welcome Tina...
A possible problem in YTD calculations is when the data contains gaps. The methods presented works if periods with no registrations are included with a zero value. That is not always the case.
Good catch. The method should be fine as long as you have data for the starting month. Otherwise you need more complex conditions
Thank you, very usefull - but please turn down the background music :-)
Noted! I will adjust the background volume in future vids.
Hi chandoo PLZZ help replicate the same in Power BI
Hi Chandoo, how would you do the year to date (YTD Cals) if the monthly figures are in columns rather than rows?
I also need to know if you got your answer, pls share
never used those first 2 elements in an offset before. i used offset often usually to defina a range that might not be in a table. we a plug-in to pull data into excel and it overrides the sheet. so i define range as offset(a1,0,0,counta(a:a),counta(1:1)).
That is a cool approach.
Really i don't know is that what i need. I have stock prices for each day from 1st day of January 2020 to current day 2022
Need this video in Telugu anna...
*good*
When I select C5 I get @month instead of C5 and I am unable to do calculation. How to fix this problem?
Thanks
Welcome
Awesome trick , please turn off or slow down background music.
Thanks Shoaib. I will adjust the background volume in future videos :)
Amazing Sir, can this be applied for Daily Revenue date as Daily and MTD
You can. Instead of MONTH(date)=1, you can use DAY(date)=1 to check for first of the month and then calculate MTD values.
Hi, loved this. how would you do this to get an average of the YTD?
Instead of SUM, use AVERAGE.
@@chandoo_ I do have a problem with that, cuz average you would need to use numbers from the begining of the year- how to do that?
Bro.. కొన్ని videos on macros కూడా చెయ్యగలరా?
Sure. I already have many VBA / Macro videos. Please see the playlist here - ua-cam.com/video/nerIcLV7a2s/v-deo.html
@@chandoo_ bro మీరు ఏ application ise చేస్తారు for creating youtube videos
Please see this. I recently explained my backend process - ua-cam.com/video/P0Wb615JVZw/v-deo.html
@@chandoo_ Thanks Chandoo గారు.. I have a question, can we make thumbnail using ppt? If so ఎలాగో చెప్పగలరా..
Can we use this in a pivot? via calculated field
In pivots, you can use year to date as a calculation option (but only for calendar dates) or DAX for financial years.
Could I sum all line break value in Excel
Use subtotal instead of sum
Scan formula
👍
вообще не работающий совет,диз
Thanks for this video....very helpful