Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT & SUM

Поделиться
HTML-код
  • Опубликовано: 19 окт 2024
  • Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
    Discover the power of Excel formulas to dynamically calculate year-to-date figures for volume and price. Ideal for analysts, accountants, and anyone seeking to enhance their Excel skills!
    ⬇️ Grab the workbook here: pages.xelplus....
    🔍 What's Inside:
    ▪️ Dynamic Formula for Year-to-Date Volume: Learn to use OFFSET combined with SUM functions to create a formula that dynamically adjusts as you drag it across months and years, avoiding manual adjustments.
    ▪️ Avoiding Helper Cells with SUMPRODUCT: Discover how to use SUMPRODUCT to eliminate the need for helper cells while calculating year-to-date figures for prices.
    ▪️ Handling Complex Data Sets: Understand how to efficiently manage complex data with overlapping years, ensuring your calculations are accurate and dynamic.
    ▪️ Deep Dive into OFFSET Function: Master the OFFSET function to create dynamic ranges that automatically adjust based on the month and year.
    ▪️ Real-World Application: Apply these techniques using real data, like monthly volumes and prices, for practical insights and analysis.
    You'll learn how to use Excel formulas dynamically to analyze monthly data over several years.
    Here's what you'll learn:
    Combining Formulas for Dynamic Results: Discover how to use product and offset formulas together for more efficient data handling.
    Year-to-Date Calculations: Learn to calculate year-to-date figures for both volume and price data, a key skill in data analysis.
    Dealing with Complex Price Data: Understand the complexities of calculating average prices and how to tackle them effectively.
    Avoiding Common Mistakes: Get insights into avoiding errors when dragging formulas across different months and years.
    Dynamic Ranges with Offset Formula: Explore how the offset formula can dynamically adjust ranges, enhancing the flexibility of your Excel sheets.
    Simplifying with SumProduct: See how the sum product formula can replace helper cells, making your data cleaner and more manageable.
    Practical Examples and Demonstrations: The video provides clear, step-by-step examples to ensure you can apply these techniques to your own data.
    I'll show you a way of writing YTD formulas (in this specific case for volume and price) when your data set goes over a few years. i.e. each month occurs more than once. If you were doing this manually, you'll need to revise your formula in the middle of your data set to make your range start from January of the next year. OFFSET allows you to create dynamic ranges - when you use this with the Month() and SUM() functions, you've created a smart formula that recognizes when the data goes over to the next year and revises your YTD calculation.
    ★ My Online Excel Courses ► www.xelplus.co...
    ➡️ Join this channel to get access to perks: / @leilagharani
    👕☕ Get the Official XelPlus MERCH: xelplus.creato...
    🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
    🎥 RESOURCES I recommend: www.xelplus.co...
    🚩Let’s connect on social:
    Instagram: / lgharani
    LinkedIn: / xelplus
    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

Комментарии • 156

  • @LeilaGharani
    @LeilaGharani  8 месяцев назад

    Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-ytd-file

  • @DrSougataBanerjee
    @DrSougataBanerjee 6 лет назад +2

    Quite an 'out-of-the-box' thinking - specially the part where you actually disguise (read, custom-format) a date to show as months. I personally think that was THE KEY to the whole solution. Kudos to your thought process!!

    • @LeilaGharani
      @LeilaGharani  6 лет назад

      Thank you for your feedback. Glad you like the tutorial.

  • @GosCee
    @GosCee 3 года назад +1

    Leila, I love using Excel formulas and you're inspiring me with everyone of your videos. Thanks again for sharing.

  • @zahirbabiker
    @zahirbabiker 4 года назад +2

    Thank you so much for your training series, I converted month name to number by using MONTH(DATEVALUE(Month_Name&" 1")))

  • @keishrich
    @keishrich 6 лет назад +1

    Thanks Leila!
    In my situation, I wanted to show YTD depreciation expense in one cell as the year progresses for a depreciation schedule with the monthly depreciation for all assets pre-calculated.
    Plus, my financial year is Feb - Jan. Your videos on the OFFSET function helped heaps, even though they didn't provide the exact solution I was looking for.
    My formula is: =SUM(OFFSET(AZ,0,1,1,IF(MONTH(D5)>=2,MONTH(D5)-1,MONTH(D5)+1))

    • @LeilaGharani
      @LeilaGharani  6 лет назад +1

      You're welcome Keisha! Great you adjusted it to fit your needs :)

    • @FocusedSpender
      @FocusedSpender 6 лет назад +1

      I'm going to use this formula at work tomorrow!

    • @keishrich
      @keishrich 6 лет назад

      For the last argument in the IF function change it to 12, or when January comes, the the width for the OFFSET function will be 2 instead of 12.
      (Note: Reference D5 is for current reporting period in date format, for e.g. 6/30/2018).
      Revised formula: =SUM(OFFSET(Z10,0,1,1,IF(MONTH(D5)>=2,MONTH(D5)-1,12)))
      This formula will automatically sum year to date depreciation in (one cell) from Feb to Jun and works for fiscal years. If your using a calendar year then use only the MONTH function for the width argument in OFFSET.

  • @raghuv7114
    @raghuv7114 4 года назад +2

    No more words for you.my inspiration of learning level becoming infinite.

  • @jmasui
    @jmasui 4 года назад

    Thank you - one of better method of calculating YTD coupled with easy to follow explanation.

  • @Tattysnuc
    @Tattysnuc 4 года назад +1

    I came across a similar issue and this has given me some new ideas on how to solve the problem. Many thanks. Great to be learning once again about Excel :)

  • @robsonnvula9285
    @robsonnvula9285 10 месяцев назад

    Great tutorial from Leila as always!! Looks so simple the way you explain it

  • @Reyesnes
    @Reyesnes Год назад

    Wow, this video is great. Thank you very much Leila for this great solution.
    Is it too much to ask if you can make another video about YTD calculation, but adding more complexity with conditionals. Something like this:
    Maybe you have that same table with the months horizontally but with a large list of KPIs or products vertically (a matrix table).
    But then you have another "summary sheet" where the YTD calculations for each of the KPIs must be performed as follows:
    - YTD LY vs YTD CY summary comparison.
    - Then a YoY based on both YTD.
    Additionally, in this same "summary sheet" there is a drop-down list in a cell with the "month-year" which indicates up to where you want to perform the YTD calculation for each KPI.
    The calculations must then be conditioned by:
    - a search based on the KPI name (you must search for a match of the KPI name from the summary sheet vs. the KPI name in the main table)
    - And conditioned according to the drop-down list where you select the range of the year to evaluate.
    Example:
    In the dropdown you have set the value "Aug-2023". This means that:
    - for YTD CY you have to calculate the equivalent sum from Jan-2023 to Aug-2023.
    - for YTD LY you have to calculate the equivalent sum from Jan-2022 to Aug-2022.
    - and the YoY % would be based on the difference of both YTD calculations.

  • @Allmytravelz7072
    @Allmytravelz7072 7 лет назад +1

    Awesome, I always avoid Offset, but used helper cells. This is so easy and simple the way you lay it out. Thanks again

  • @lawrenceg7341
    @lawrenceg7341 2 года назад +1

    Hello Leila,
    You are my go-to excel learning resource, it seems you have a magic in explaining the concept really really well. I know this is an old video but would you have the practice workbook available for this one?

  • @bkkhatri2
    @bkkhatri2 4 года назад

    Hi Leila
    Thank you for your useful and powerful video. I learnt alot from them and still learning.

  • @hylarion
    @hylarion 5 лет назад +4

    If you can't play with the -month() formula, you can instead use something based on -(mod(column(XY),12)+1) to generate a continuous cycle of values from 1 to 12 depending on the column... A bit more cumbersome, but it is more "universal" because based on positions and not on cell values.

  • @zakeermohamedkhan8750
    @zakeermohamedkhan8750 5 лет назад

    Leila, Slowly I am discovering the depth of intelligence in you. Amazing. Keep it up.

  • @Yuuuuuuuwei
    @Yuuuuuuuwei 6 лет назад

    This is helpful and clear! and I really like the case you make. It makes a lot of sense.

    • @LeilaGharani
      @LeilaGharani  6 лет назад

      Glad you like it. Thank you for your comment.

  • @xodrinker
    @xodrinker 6 лет назад +1

    Extraordinary elegant Leila, Thanks!

  • @mrzorrombo
    @mrzorrombo 4 года назад

    Very helpful video! Pls share how to handle YTD if fiscal year starts from any other month

  • @ZA-ln8eg
    @ZA-ln8eg Месяц назад

    Great solution - however once you have selected the width option - it will return the whole range so you don't need to include the ":G2" .. e.g. =SUM(OFFSET(G2,0,0,1,-MONTH(G1))) gives exact same solution as =SUM(OFFSET(G2,0,0,1,-MONTH(G1)):G2)

  • @marcbousquet9742
    @marcbousquet9742 5 лет назад +1

    Hello Leila,
    Nice videos and i have to tell you that, even I'm very "fluent" in excel, i discovered interesting things with your videos.
    I would like to share a quick enhancement of your "offset" formula.
    You wrote in B15 : =SUM(OFFSET(B7;0;0;1;-MONTH(B6)):B7) but the ":B7", within the SUM function, is useless as you are using the WIDTH property of the offset.
    you could have wrote =SUM(OFFSET(B7;0;0;1;-MONTH(B6))) it would ave worked the same.
    if you still want to reference the END of the range, which can be quite handy while reading at the formula, you could just offset the column part of the range without touching the WIDTH
    =SUM(OFFSET(B7;0;1-MONTH(B6)):B7)
    Note that there is a 1 before "-MONTH" because in January we do not want to slide left, in feb we want to slide just one column and so on.
    Thanks,

  • @agape13
    @agape13 3 года назад

    Excel Queen 👑. 🏆🥇

  • @drpivotdrtovip5352
    @drpivotdrtovip5352 6 лет назад

    Nice idea using opffset. I would have used sumifs for year and month. Kudos!
    However, offset with an altered height or width gives a range already. Therefor you don't need to specify the end of the range. See $N$7:$O$7:O7 at 8:06.

    • @LeilaGharani
      @LeilaGharani  6 лет назад

      Thanks for the tip :) I'll check it out.

  • @shaikhazher1627
    @shaikhazher1627 4 года назад

    FANTASTIC

  • @maneprashant642
    @maneprashant642 6 лет назад

    Thanks Leila for the useful tools info.

  • @KgasS
    @KgasS 3 года назад

    Thanks for this insight. This works only in excel (a bug?)as the offset function in other sheets (Google/Zoho/Libreoffice) will not work as they did not accept negative width/column in the offset function. To work in all, a positive width version is needed. The formula sum(offset(B4,0,-month(B3)+1,1,Month(B3))) for volume sum and SUMPRODUCT(offset(B4,0,-month(B3)+1,1,Month(B3))*offset(B5,0,-month(B3)+1,1,Month(B3)))/B13 for weighted price works and hope it is easy to figure out where the cells are referring.

  • @louiselane806
    @louiselane806 4 года назад

    Hi Leila, you’re my go to when I need a formula, thank you for your great videos, people think I’m an excel wiz thanks to you 🙂. This one will help me with a ytd budget calculation, quick question can I use this with a vlookup, and how would that work, vlookup first?

  • @ericpeters3917
    @ericpeters3917 6 лет назад +2

    I am doing a spreadsheet where I am deriving data from multiple worksheets based on multiple criteria for a Fiscal Year using drop down lists. I have been able to use SUMIFS in conjunction with INDEX & MATCH to get a total of a monthly columns for obligations based on the specific criteria (currently 3 different criteria). For the sum range in the SUMIFS I used my INDEX & MATCH which allows me to get the proper totals of the data by month (month is the column header) but I also need to get a cumulative sum (i.e. if I select Oct (1st month of FY) from the drop down I get October's total, when I select Nov from the drop down I get October + November. After watching this video I have been trying to figure out how to apply it to my problem but have been unsuccessful so far. The second worksheet that I am using the SUMIFS to calculate is an external connection to an Access database as well so the number of rows in it may periodically increase. Formula I used for my SUMIFS is =SUMIFS(INDEX(Table2[[Oct]:[Sep],0,MATCH($C$3,Table2[[#Headers],[Oct]:[Sep]],0)),Table2[FY],Table1[FY],Table2[Organization],Table1[Organization],Table2[Account],Table1[Account]). Can OFFSET be used to allow me to complete the YTD calculation in my case? One problem I am running into is that since the second worksheet is an import of Access the Oct-Sep headers are text not dates so the MONTH function doesn't work.

    • @PONCEJE
      @PONCEJE 6 лет назад

      Send it to me and I'll fix it.

  • @halimjsc7260
    @halimjsc7260 4 года назад

    Thank alot 👍👍👍

  • @44.7b.kaharaditya6
    @44.7b.kaharaditya6 6 лет назад

    Thanks , very useful formula

  • @weili3794
    @weili3794 5 лет назад

    Dear Leila, I have the answer by watching your offset video. Brilliant! But what if the month criteria is somewhere else. For example, if I type the month June in cell A18. How do I sum YTD (Jan-Jun) in cell A19?

  • @kaaa3485
    @kaaa3485 6 лет назад

    Thank you so much.

  • @mchllwoods
    @mchllwoods 7 лет назад +4

    U can use the columns function also instead of minus month.

    • @LeilaGharani
      @LeilaGharani  7 лет назад +1

      Very true. Thanks for the contribution :)

    • @mchllwoods
      @mchllwoods 7 лет назад +1

      Leila Gharani no prob. Keep up the awesome work.

    • @swastik9872
      @swastik9872 4 года назад +1

      cud pls explain how column function will reset to 1 at january

    • @profbfc
      @profbfc 3 года назад +1

      same question as Nilesh

    • @allison447
      @allison447 2 месяца назад

      @@swastik9872 if function for Jan

  • @stuark0
    @stuark0 2 года назад

    Hi Leila,
    A very helpful video. One thing that I don't understand though is why its necessary to include the second parameter in the SUM function (ie. :N7 in this case)? As I understand it, as the OFFSET returns a range of cells (including the cell from the same column as the formula) the second parameter in the SUM function is redundant. To illustrate, won't these formula return the same result?
    =SUM( OFFSET (N7, 0, 0, 1, -MONTH(N6)): N7)
    =SUM( OFFSET (N7, 0, 0, 1, -MONTH(N6))
    In my tests, they return identical results. Am I missing something?
    Thanks

  • @hosseinhosseinpoor4845
    @hosseinhosseinpoor4845 3 года назад

    سپاس

  • @kaaa3485
    @kaaa3485 6 лет назад

    Hi Leila, I have to do a training for my job. I just love your training, your video; they are very clear and very helpful. Please, let me know what software do you use to record yours? thank you so much.

    • @LeilaGharani
      @LeilaGharani  6 лет назад +3

      Hi - I use Camtasia to record the screen. A canon camera to record the head-shots (although I did use my iphone at the beginning...)
      All the best for your training :)

  • @arkachatterjee6981
    @arkachatterjee6981 4 года назад

    We can use running count and if conditions......it will be more simple

  • @tumeenyamdorj378
    @tumeenyamdorj378 4 года назад

    Hi Leila, this is very nice and helpful. Much appreciated. I am interresting if there is an option that excel can make dynamic grant chart schedule based on 12 hour shift fo a week?

  • @hemant5757
    @hemant5757 3 года назад

    Can you now do it with dynamic array function. I mean only put a formula in one cell and you need not to drag it till the last cell in the range?

  • @ابراهيمالرجب-ه9ط
    @ابراهيمالرجب-ه9ط 3 года назад

    Great 👍

  • @glassigast100
    @glassigast100 5 лет назад

    When you have a pretty huge dataset, huge workbook, isn't it better to use hard coded helper cells rather than using additional formulas? From a performance wise perspective.

  • @andrewcopley994
    @andrewcopley994 3 года назад

    Hi Leila, I am gradually getting through your videos but just wonder on this one why you keep the colon and second cell reference in the formula if you have already set the range using OFFSET? Surely this is unnecessary. I get the same result using (in your cell B16) =SUMPRODUCT(OFFSET(B7,0,0,1,-(MONTH(B6)))*OFFSET(B8,0,0,1,-(MONTH(B6))))/B15

  • @miroslavjordanov4459
    @miroslavjordanov4459 4 года назад

    Hi, Leila,
    is there a way offset formula sums every second column? for example: b7+d7+f7+...+December column
    Thank you very much for the answer!
    Best Regards
    Miroslav

  • @juanjajan7243
    @juanjajan7243 5 лет назад

    Thanks a lot

  • @israfilgazi8071
    @israfilgazi8071 7 лет назад

    Hi, I am very big fan of you. I am completing all of your tutorials one by one. Thanks a lot for these videos. Could you give me all templates? PLEASE PLEASE PLEASE

    • @LeilaGharani
      @LeilaGharani  7 лет назад +1

      Hi Israfil - Thanks for the support. For most of the videos, I have the Excel workbooks included. I'll start to include them in for the missing videos....

    • @israfilgazi8071
      @israfilgazi8071 7 лет назад

      Thanks. Happy to know that.

    • @chandur5251
      @chandur5251 7 лет назад

      Madam can you help with macros which includes pivot table creation with v lookup from external data in it.

  • @chipgiii
    @chipgiii 6 лет назад

    This is great stuff. I was curious as to whether anyone has a clean way of having cost that are amortized during a fiscal year, spread out monthly. So if the start date is January 2018, and the FY19 begins in March 2019 I see the last 10 payments in FY19 ending two months before FY19 is finished.

  • @ChiltonTurbo
    @ChiltonTurbo 5 лет назад

    The month number is very useful, but how would you do it if you wanted to do this for a financial year? Without helper cells!

  • @googlesheetautomation3573
    @googlesheetautomation3573 5 лет назад

    Mam if this data in column then which formula we used because I use this formula but not give answer. if you can make video on this then it is great help from you side

  • @weili3794
    @weili3794 5 лет назад

    Dear Leila, cell B15 = SUM(OFFSET(B7,0,0,1,-MONTH(B6)):B7) for YTD. What if I have 2 rows of volumes to add, for example volume 1 & 2. Basically every month has 2 rows to add.

  • @Saddam-Abdulameer
    @Saddam-Abdulameer 4 года назад

    شكرا

  • @tha2irtalib343
    @tha2irtalib343 5 лет назад

    awesome , Thanks for the nice job.

    • @LeilaGharani
      @LeilaGharani  5 лет назад

      My pleasure. I'm happy you find the tutorial useful.

  • @Ali_khan512
    @Ali_khan512 5 лет назад

    Dear Leila I'm facing bit more confusing problem regarding my database wherein i want to calculate the payment of months which ia due but in a dynamic way. Pardon my mistake if i fail to elaborate my problem but i want it like if current month's payment is paid then the formula should avoid this month and calculte the rest month's of year due amount and if the the current month payment is not made then the formula should dynamically calculate the amount... I am positive that you'll reply soon

  • @tamerhegab1513
    @tamerhegab1513 Год назад

    Love you

  • @ryanrust9142
    @ryanrust9142 6 месяцев назад

    How do you calculate the YTD in the monthly payslips

  • @planxlsm
    @planxlsm 6 лет назад

    very good !

  • @JF-dy2dr
    @JF-dy2dr 2 года назад

    =if(year(c6)=year(b6), c7+b7,c7) should do ytd volume, no? similar for calculating ytd avg price.

  • @muhammadumar-bi7jf
    @muhammadumar-bi7jf 5 лет назад

    Dear good job
    But how to calculate 2 or 3 month average in one cell supposed January and February combined average. If we want 6 month average and one value is zero then answer #divd#?

  • @eliasmiahsohel1845
    @eliasmiahsohel1845 2 года назад

    Madam, plz give dynamic 3y or 5y cagr calculation from 10y in excel video

  • @paulwillems4281
    @paulwillems4281 3 года назад

    Hi @Leila, I summarise my large data set in a pivot table. Can I use a dynamic range to always show the last twelve months data in the pivot table?

  • @mifans9440
    @mifans9440 3 года назад

    متشکرم.

  • @alexrosen8762
    @alexrosen8762 7 лет назад

    Awesome! Thanks Leila :-)

  • @poilou2607
    @poilou2607 6 лет назад +1

    Actualy you do 'nt need the second term of the sum (:Ox) because the offset is already a vector

  • @briankibias8686
    @briankibias8686 7 лет назад

    good one

  • @subhasisbhattacharjee9187
    @subhasisbhattacharjee9187 4 года назад

    Hi Leila, I have some set of data month wise quantity and selling price, I want to sumproduct the selling price at a particular column based on a month selected from drop-down list. Please help.

  • @stephanietao546
    @stephanietao546 3 года назад

    I have 10 years of WMT dividend data from 2010-2019. Walmart paid 4 times per year except for Y2017 (paid one additional at the end of the year), which means I have four rows for each year but 5 rows for 2017. I need to sum dividends paid for the first four rows of each year. I tried sumif() with offset() but that didn't exclude the 5th rows of 2017. Please advise, thank you very much.

  • @Sam5597
    @Sam5597 5 лет назад +1

    Month formula is only applicable when you're doing YTD as per Calender year.
    What if I have a strict Fiscal year say April to March?
    Seems Match needs to be introduced.

  • @sudheercherukuri14
    @sudheercherukuri14 2 года назад

    Hi Ms.Leila, I am new to Excel functions. I have a question in this example. Could you please calrify it? What if the table starts from March but not from January? In this case, width range as '-3' will cause an error. How can we hanlde this?

    • @AtanasNenov
      @AtanasNenov Год назад

      Think you can use COLUMNS() function instead, then you wouldn't face this issue.

  • @hattemghanoom9557
    @hattemghanoom9557 6 лет назад

    Very smart

  • @joebonasia1140
    @joebonasia1140 4 года назад

    Can you use the Offset function with a particular referencing function ‘like counta for example’ order to calculate quarterly sums or averages?
    As I am doing this now it only moves cell by cell I would like to group 3 cells together and then skip 3 cells to the following quarterly data. Is it possible to use offset for generating quarterly summaries from monthly data?
    Any help would be greatly appreciated :)

  • @zeeshanrafi6511
    @zeeshanrafi6511 7 лет назад

    Hi greetings from India.

    • @zeeshanrafi6511
      @zeeshanrafi6511 7 лет назад

      I would need help from u out of ur busy schedule as ur great and fabulous with ur excel skills, if u can respond It would make my work easy.

  • @yanchaobjerkvig6313
    @yanchaobjerkvig6313 7 лет назад

    I like your tutorial. Do you have one for how to use Subtotal on Sumifs?

    • @LeilaGharani
      @LeilaGharani  7 лет назад +1

      Thanks. Glad you like it. I have one on SUMIFS, I'll upload it next week. I'll look to add SUBTOTAL as well, although I prefer the (newer) AGGREGATE function - I'll make a note of these for future videos.

    • @LeilaGharani
      @LeilaGharani  7 лет назад

      Hi Yanchao - you can find the SUMIFS video here: ruclips.net/video/AZuBNWMh7VM/видео.html

  • @mattgruber9573
    @mattgruber9573 6 лет назад +2

    Trying to figure out how I can continue to use the YTD Volume - Dynamic Formula with the MONTH function, but have a fiscal year from Oct-Sept. The MONTH function only seems to operate 1-12 and can't be modified to reorder it to say...Month 1 = October (or whatever fiscal start you plug in). I have gone through the comments and seen similar questions, but only half answered solutions. I have tried the COLUMN function with success, but it does not recognize the new fiscal year and continues to sum into the next fiscal year. I have also tried the COUNTA and MOD functions, but can't seem to get it to work. If I use the COLUMN function, what does the formula look like for it to only count 12 and begin adding up the next fiscal year? Thanks for helping.

    • @LeilaGharani
      @LeilaGharani  6 лет назад +1

      Hi Matt, Here are the easiest ways I can think of right now: 1st method - use helper columns. So right above the months put index numbers, 1 to 12. 1 would be above Oct and 12 above Sep - january would be 4 etc... then use this formula =SUM(OFFSET(B7,0,0,1,-B5):B7) - assuming you have Oct in the B column and B5 is where the index number is (I've based the setup based on the example in the video). 2nd method: this doesn't require helper columns but it does require your months to be input as actual dates (like I have in the video) - the formula here would be =SUM(OFFSET(B7,0,0,1,-IF(MONTH(B6)>=10,MONTH(B6)-9,MONTH(B6)+3)):B7) - here I am making an adjustment. If month number is greater than or equal to 10 then we deduct 9 from it, otherwise we add 3 (because it means we're before October) - Hope this helps....

    • @mattgruber9573
      @mattgruber9573 6 лет назад +2

      Thanks Leila! Both solutions worked brilliantly for my situation. The "helper cell" method is easier, but after watching a lot of your videos; I am reluctant to rely on helper cells. The second method is definitely more complicated and beyond my current excel knowledge to figure out on my own. So I appreciate your guidance. Your videos and instructions are well thought out and very professional. Keep up the great work!

    • @LeilaGharani
      @LeilaGharani  6 лет назад +1

      Thank you Matt for the feedback. Glad you got it to work :)

  • @belaalhayajneh4796
    @belaalhayajneh4796 5 лет назад

    Hi mam,
    I made repor data in excel for invoices, and I saved the invoices as pdf by number of invoice, so how can I make a hyberlink between the new pdf invoices and the invoices number in report sheet, thanks

  • @nader6450
    @nader6450 4 года назад

    Hi
    I'm building a dashboard for our sales over the year. I could do a dynamic chart to show a chart to show YTD compare with the same period last year, I need now to do the same dynamic chart to show current week with same last year + current month with the same last year. is there any way to make Excel calculate them automatically based on the date that we are in. Hope my question is clear. Thank you.

  • @ntheq3982
    @ntheq3982 5 лет назад

    Leila... I have a task that doesn't seem to be addressed in any RUclips example I've searched (50+ searches) and your Dynamic YTD is close but still not what I need. Maybe you can do a video on:
    "How to Find Cumulative Principal Paid on Amortized Loan at Every End-Of-Year"
    I have created a Mortgage Calculator with Amort Table that has columns with Period, Period Date based off start date, Payment, Principal, Interest and also column for Cumulative Principal & Cum Interest. Is there a FORMULA method to INDEX MATCH or VLOOKUP or DGET to find the DECEMBER Cumulative Principal data from the Mortgage Amortization Table. What makes this impossible for me is if the Loan Start Date is March 7, 2019 or October 23, 2019, it can't figure how to get the Cum Princ because 1) I need the Cum Princ for Dec which is only a few months in 1st year & 2) the row for the Cum Princ changes depending on the start date. I don't want to use helper cells. Wish you could see my workbook page. This is above my head. Thanks

  • @bkkhatri2
    @bkkhatri2 4 года назад

    I this video.. One issue is that what if we are starting the report mid of the year say aug 2018 to oct 2020

  • @empathic_mimicry
    @empathic_mimicry 7 лет назад +1

    It's here

  • @jyotiloomba464
    @jyotiloomba464 4 года назад

    Yeah it's really helpful, but what if we have million of fixed assets with different put to use dates, residual value 5 %, depreciation rate 10%, if the life of an asset is less than 180 days from put to use date we will apply half depreciation for that financial year, if greater than 180 days then full dep for that FY, how to calculate book value for each year using straight line method

  • @PHOK400
    @PHOK400 7 лет назад

    what is the best way to do WTD, MTD and YTD sumproduct for an array of daily data

    • @LeilaGharani
      @LeilaGharani  7 лет назад

      That's an interesting one. I've added it to my list. Thanks for the suggestion.

  • @pipo441
    @pipo441 3 года назад

    How if you have want the data in column format, running totals YTD??

  • @egbesamuel
    @egbesamuel 2 года назад

    When data range is vertical, pls what happens?

  • @josephhorling6013
    @josephhorling6013 5 лет назад

    Hi Leila, Can I download the whole workbook from you?

    • @LeilaGharani
      @LeilaGharani  5 лет назад

      Joseph, the complete workbook is included as working material in my Advanced Excel course.

  • @kaseox5436
    @kaseox5436 2 года назад

    what if i have stock prices for stock prices for everyone day ?

  • @AbuTalha-eo7pr
    @AbuTalha-eo7pr 5 лет назад

    Really Congratulations to a great muslim educator named Leila GHANAMI ...

  • @Base2aus
    @Base2aus 6 лет назад

    This works fine in the US but here in Australia our financial year runs jul - jun :( this method will not work, any suggestions

    • @LeilaGharani
      @LeilaGharani  6 лет назад

      I remember some options were mentioned in the comments. If you get a chance please scroll through them for ideas on how to handle different financial years.

  • @hassanalelaiw2240
    @hassanalelaiw2240 2 года назад

    Hi Leila,
    How can we claculate (sum) rows and columns using dynamic YTD where the criteria for top row is months but for column would be more than one (years, regions & products)
    i.e what is the YTD sum of 2020 / AM where the current period is Mar (in other words, total sum for Jan 2021 to Mar 2021 for AM)?
    Year Region Product Jan Feb Mar Ap r ........
    2020 AM X1 50 723 125 .....
    2020 AM X2 132 70 235
    2021 EU X3 565 1175
    2020 AM X3
    2021 EU
    2021 EU
    2022 AS
    Thanks for the excellent helpfull sessions

  • @MarkHakeScottsdaleAZ
    @MarkHakeScottsdaleAZ 5 лет назад

    What screencast software do you use?

  • @Thekastro911
    @Thekastro911 5 лет назад

    Leila Hi, is there a workbook for this video to practice?

    • @LeilaGharani
      @LeilaGharani  5 лет назад

      For this one, the workbook is included in the full course....

  • @firasmusmar2097
    @firasmusmar2097 5 лет назад

    what is the shortcut to make the range from specific sell to itself?

    • @123rockstar2010
      @123rockstar2010 5 лет назад

      Press the cell, and ":" (colon key)

    • @sandhyasandy1965
      @sandhyasandy1965 3 года назад

      23/06 🏆🇮🇳 No Charges / FORM FILL UP, COPY PASTE work. Only WhatsApp this number 9917089859
      OUR MISSION:- EMPLOYMENT INDIA🌞🌞

  • @Allmytravelz7072
    @Allmytravelz7072 5 лет назад

    Hi Leila, would this work on fiscal year, april is month one. I use the column function instead of month

    • @Allmytravelz7072
      @Allmytravelz7072 5 лет назад

      Thanks by the way

    • @ajit555
      @ajit555 4 года назад

      The month formula can be adjusted for Fiscal year.

  • @manishjindal9622
    @manishjindal9622 6 лет назад

    Hi leila.. i have data (apr~mar) instead of (jan ~mar) how can i use month function

    • @LeilaGharani
      @LeilaGharani  6 лет назад

      Hi Mainsh - I've answered this somewhere. Not sure where...Might be in the comments below.....

    • @manishjindal9622
      @manishjindal9622 6 лет назад

      ya i got it

  • @abdanomer
    @abdanomer 7 лет назад

    Great example of using offset.
    Could this example use the column() function instead of month (incase of the month written in text format ), then there should be a condition of counting 12 cells ! 😅
    This could be more complicated, but it could be there if import the sales data from a software!

    • @LeilaGharani
      @LeilaGharani  7 лет назад

      Agree Abdelrahman - you'd have to use a workaround - either column(), counta() or even MOD() functions could be used to figure out the month....

  • @shahchoudhury5301
    @shahchoudhury5301 3 года назад

    Hi, need abit of help been searching everywhere but can't seem to find a solution. I am only trying to calculate negative values for each month can someone help.

    • @jessicaykeith
      @jessicaykeith 3 года назад +1

      I believe the best way is to add an If statement >0, then Leylas' method. I hope this helps.

    • @shahchoudhury5301
      @shahchoudhury5301 3 года назад

      @@jessicaykeith thank you

  • @gantulgaerdenechimeg3520
    @gantulgaerdenechimeg3520 4 года назад

    Leila, have you ever heard rose diagram (used in geology)? Please show us how to do it please. @t
    Thanks in advance
    Your big fan

  • @ahmedelgazzar9530
    @ahmedelgazzar9530 7 лет назад

    Thanks a lot for that amazing Video .
    I have some issues in excel and I need your support , Can You ?

    • @LeilaGharani
      @LeilaGharani  7 лет назад +1

      You're welcome Ahmed. Regarding the issues, it really depends on what they are an how much free time I get on my hands in the evenings. Feel free to send me your file (you can find my contact info on www.xelplus.com) with an explanation of the problem and the outcome...

    • @ahmedelgazzar9530
      @ahmedelgazzar9530 7 лет назад

      Sure I will do.
      Appreciate your understanding : )

  • @agapheputrasusilo741
    @agapheputrasusilo741 Год назад +2

    my brain hurts

  • @alvarolopez2353
    @alvarolopez2353 3 года назад

    Does anyone have an idea of how to do this with weeks?

    • @JF-dy2dr
      @JF-dy2dr 2 года назад

      @Alvaro Lopez I imagine one could do something similar to what I did above for ytd or qtd with the weeknum formula to id when the week change

  • @gedenidzegiorgi7255
    @gedenidzegiorgi7255 2 года назад

  • @rksudera
    @rksudera 7 лет назад

    You look so very beautiful....

  • @farooqtahir538
    @farooqtahir538 Год назад

    I found this video highly useless

  • @ChiltonTurbo
    @ChiltonTurbo 5 лет назад

    Stop saying Wolume