Forum Discussion
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...
Hi PhraserB
EDIT: After clarifications below proposal does not apply. Approach could apply to other scenario though
Alternatively Unpivot your accounts with Get & Transform aka Power Query (sample attached). After that almost all analysis are easy, i.e. with a PivotTable:
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
- 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
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)