Forum Discussion

PhraserB's avatar
PhraserB
Copper Contributor
May 14, 2023
Solved

Calculating annual (& YTD) account credits from a dated record of monthly credits

Hi...Been struggling to solve the following problem with Excel 365 and hoping someone can help. Here is a summary. Thanks in advance for any help and suggestions. Fraser.   There 20 different accou...
Formulas and Functions
PhraserB's avatar
PhraserB
Copper Contributor
May 14, 2023
Hi Lz.... Many thanks for taking time to reply and for recommending using a Pivot Table. I realise and apologise that I have not explained the problem clearly enough. Please see my reply to Quadruple Pawn which gives further information. Thanks again for your help. Fraser
  • PhraserB's avatar
    PhraserB
    Copper Contributor
    May 14, 2023
    Hi Lz... Thanks. This is very neat and exactly what I was looking for. Will have a close look tomorrow when I have more time. Thanks again. Brilliant! Regards Fraser
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 15, 2023

      Hi PhraserB 

      Please re-download (inc. Variant). I made a couple of adjustments (same output) - I'll explain later if necessary

       

      Did a couple of scalability checks with 50 account running until end of 2050 - works like a charm
      FYI: 1st time you Refresh a query within an Excel session takes a bit of more time - to load the .NET Framework. Ex.: with the above sample 1st time to Refresh +/- 5 secs, next Refresh +/- 1 sec. (here)

      • PhraserB's avatar
        PhraserB
        Copper Contributor
        May 15, 2023
        Good Morning Lz... thank you for providing more information about your solutions, including the update to your original and the variant. The scalability information was helpful because there are currently 20 accounts. The refresh time is good to know and of course isn't a problem. Whilst I've used Excel for many years (I was the Director of Medical Physics in a Scottish hospital for many years), my knowledge of pivot tables has been limited to discussions with the Finance department. In terms of the present problem I'm still exploring the differences between the files you've provided. Although I'm sure it will turn out to be obvious, I'm intrigued by how the current year to date total is constrained by the current date. Thanks again for your help and interest. Best wishes Fraser

Resources