HI All, I have a month wise data set with two columns (A &B) which is the input data.
The output needs to be a 1month, 3month, 6month, 12month and YTD summary using a formula { =(PRODUCT(range/100+1)-1)*100}. Range denotes last three values of col A for a 3month calculation, last six values of col A for 6month calculation and so on.
Both input and output in same tab of attached file. Please can someone guide on this?
Input
Date | Col A | Col B |
Jan-20 | 0.79 | 0.89 |
Feb-20 | 0.62 | 0.54 |
Mar-20 | 0.78 | 0.53 |
Apr-20 | 0.56 | 0.71 |
May-20 | 0.78 | 0.03 |
Jun-20 | 0.61 | 0.00 |
Jul-20 | 0.10 | 0.42 |
Aug-20 | 0.45 | 0.81 |
Sep-20 | 0.14 | 0.84 |
Oct-20 | 0.66 | 0.34 |
Nov-20 | 0.83 | 0.84 |
Dec-20 | 0.36 | 0.94 |
Jan-21 | 0.77 | 0.09 |
Feb-21 | 0.07 | 0.21 |
Mar-21 | 0.66 | 0.96 |
Apr-21 | 0.29 | 0.67 |
May-21 | 0.72 | 0.40 |
Jun-21 | 0.98 | 0.16 |
Jul-21 | 0.41 | 0.60 |
Aug-21 | 0.63 | 0.44 |
Solved! Go to Solution.
There might be a more elegant solution, but I've used a series of sample/filter tools to identify the rows of interest for each different time period, then unioned them back together for the calculations. My results don't quite match yours so I might have gone slightly wrong, hopefully you'll be able to spot where since I haven't found it yet...
Solution for product function taken from:
Thanks Christina! I will try this out, I think the output variation is due to the product function used in multi-row tool.